Article on AUTOINC vs. UUIDs

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Article on AUTOINC vs. UUIDs

Simon Slavin-3
Thought some of you might enjoy seeing this article.  I make no comment on what I think of the reasoning therein.  It’s set in the PostgreSQL world, but you could make an external function for SQLite which generates UUIDs.

<https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/>

"Today, I'll talk about why we stopped using serial integers for our primary keys, and why we're now extensively using Universally Unique IDs (or UUIDs) almost everywhere."

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Stephen Chrzanowski
I'm going to read it later, but, I'll be going in skeptical.  Collision is
real, even at 128-bit resolution, but if your code handles the potential of
such a thing, then its no different than an autoinc primary key.  I'm
rather interested in their reasoning.

On Thu, Nov 30, 2017 at 9:15 AM, Simon Slavin <[hidden email]> wrote:

> Thought some of you might enjoy seeing this article.  I make no comment on
> what I think of the reasoning therein.  It’s set in the PostgreSQL world,
> but you could make an external function for SQLite which generates UUIDs.
>
> <https://www.clever-cloud.com/blog/engineering/2015/05/20/
> why-auto-increment-is-a-terrible-idea/>
>
> "Today, I'll talk about why we stopped using serial integers for our
> primary keys, and why we're now extensively using Universally Unique IDs
> (or UUIDs) almost everywhere."
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by Simon Slavin-3

Well, in my opinion the guy is an idiot.  The way to avoid the issues he is describing as the problems with serial IDs (or using the RowID) are simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny would say "What a maroon!".

Nonetheless, I have created an SQLite extension for Windows (2000 or later) that will generate UUIDs using the builtin Windows RPC interface.  Apparently similar facilities are available on other OSes though all in different manners (different functions in different libraries).  Note that the silly proxies for the RPC functions are so that the compiler can maintain correct linkage to the RPC libraries when using function pointers -- the linkage through function pointers cast to (void*) works on 64-bit Windows but not on 32-bit Windows.  On 32-bit windows not using a proxy function to maintain the correct linkage results in the stack frame corruption.

Also, uuid generation function for V1/3/4/5 are available in the Python standard uuid library, not mentioned in the article.

File is sqlfwin.c located in
http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows versions; or
http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit Windows versions
Source is the same in both, .dll compiled with MinGW 7.1.0.2 (dependencies only to standard windows DLLs and to the subsystem runtime library).

SQLite3 UDF functions returning blobs (16-byte UUID) are:
uuidCreateV1()
uuidCreateV4()
uuidFromString('text-uuid-rendering')

And returning textual renderings are:
uuidStringCreateV1()
uuidStringCreateV4()
uuidToString(uuid-blob)

The create functions are volatile (like the randomblob function), and the To/From string functions are deterministic.

sqlfwin.c also contains some other Windows API functions for working with the builtin windows security such as looking up names and sids, checking whether the current process access token contains a given sid/name, getting the current process access token username, computername, FQDN, and a few others.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Thursday, 30 November, 2017 07:16
>To: SQLite mailing list
>Subject: [sqlite] Article on AUTOINC vs. UUIDs
>
>Thought some of you might enjoy seeing this article.  I make no
>comment on what I think of the reasoning therein.  It’s set in the
>PostgreSQL world, but you could make an external function for SQLite
>which generates UUIDs.
>
><https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-
>increment-is-a-terrible-idea/>
>
>"Today, I'll talk about why we stopped using serial integers for our
>primary keys, and why we're now extensively using Universally Unique
>IDs (or UUIDs) almost everywhere."
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

wmertens
The article is a bit muddled, the only real argument I could find is that
auto-inc makes next keys easy to guess, which is information leakage, which
means that is a potential security problem if that information were somehow
useful.

Seems to me that problem can be resolved by having an auto-inc type that
skips a random amount on every insert? E.g. the next id is the last ID +
randomFromRange(1, 10000).

UUIDs are nice though if you don't have a natural key available and you are
generating keys on multiple systems. Sadly, they are strings in sqlite, and
it would be more efficient to store and compare them as their 128-bit
representation. Is there an extension that can do that?

I don't know about the collision rate; if your systems are set up in a sane
way, the MAC address alone would prevent collisions, no? And on the same
system, are collisions even possible?

On Thu, Nov 30, 2017 at 4:01 PM Keith Medcalf <[hidden email]> wrote:

>
> Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> is describing as the problems with serial IDs (or using the RowID) are
> simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the
> RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> Buggs Bunny would say "What a maroon!".
>
> Nonetheless, I have created an SQLite extension for Windows (2000 or
> later) that will generate UUIDs using the builtin Windows RPC interface.
> Apparently similar facilities are available on other OSes though all in
> different manners (different functions in different libraries).  Note that
> the silly proxies for the RPC functions are so that the compiler can
> maintain correct linkage to the RPC libraries when using function pointers
> -- the linkage through function pointers cast to (void*) works on 64-bit
> Windows but not on 32-bit Windows.  On 32-bit windows not using a proxy
> function to maintain the correct linkage results in the stack frame
> corruption.
>
> Also, uuid generation function for V1/3/4/5 are available in the Python
> standard uuid library, not mentioned in the article.
>
> File is sqlfwin.c located in
> http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows
> versions; or
> http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit Windows
> versions
> Source is the same in both, .dll compiled with MinGW 7.1.0.2 (dependencies
> only to standard windows DLLs and to the subsystem runtime library).
>
> SQLite3 UDF functions returning blobs (16-byte UUID) are:
> uuidCreateV1()
> uuidCreateV4()
> uuidFromString('text-uuid-rendering')
>
> And returning textual renderings are:
> uuidStringCreateV1()
> uuidStringCreateV4()
> uuidToString(uuid-blob)
>
> The create functions are volatile (like the randomblob function), and the
> To/From string functions are deterministic.
>
> sqlfwin.c also contains some other Windows API functions for working with
> the builtin windows security such as looking up names and sids, checking
> whether the current process access token contains a given sid/name, getting
> the current process access token username, computername, FQDN, and a few
> others.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Simon Slavin
> >Sent: Thursday, 30 November, 2017 07:16
> >To: SQLite mailing list
> >Subject: [sqlite] Article on AUTOINC vs. UUIDs
> >
> >Thought some of you might enjoy seeing this article.  I make no
> >comment on what I think of the reasoning therein.  It’s set in the
> >PostgreSQL world, but you could make an external function for SQLite
> >which generates UUIDs.
> >
> ><https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-
> >increment-is-a-terrible-idea/>
> >
> >"Today, I'll talk about why we stopped using serial integers for our
> >primary keys, and why we're now extensively using Universally Unique
> >IDs (or UUIDs) almost everywhere."
> >
> >Simon.
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Jay Kreibich
In reply to this post by Keith Medcalf

There are some minor points, but I agree that it basically boils down to “serial IDs break security-by-obscurity.”

That’s true, but….

  -j




> On Nov 30, 2017, at 9:00 AM, Keith Medcalf <[hidden email]> wrote:
>
>
> Well, in my opinion the guy is an idiot.  The way to avoid the issues he is describing as the problems with serial IDs (or using the RowID) are simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny would say "What a maroon!".

>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-
>> [hidden email]] On Behalf Of Simon Slavin
>> Sent: Thursday, 30 November, 2017 07:16
>> To: SQLite mailing list
>> Subject: [sqlite] Article on AUTOINC vs. UUIDs
>>
>> Thought some of you might enjoy seeing this article.  I make no
>> comment on what I think of the reasoning therein.  It’s set in the
>> PostgreSQL world, but you could make an external function for SQLite
>> which generates UUIDs.
>>
>> <https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-
>> increment-is-a-terrible-idea/>
>>
>> "Today, I'll talk about why we stopped using serial integers for our
>> primary keys, and why we're now extensively using Universally Unique
>> IDs (or UUIDs) almost everywhere."
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Chris Locke-3
> if your systems are set up in a sane way, the MAC address alone would
prevent collisions, no?
> And on the same system, are collisions even possible?

Google says "In the case of standard version 1 and 2 UUIDsusing unique MAC
addresses from network cards, collisions can occur only when an
implementation varies from the standards, either inadvertently or
intentionally."

I used to use UUIDs, but when looking at a database using many foreign
keys, it was a debug nightmare looking for a specific key.  After switching
to auto increment fields, its nice when debugging to look for
'templateId=4310' and not
'templateId='8af78580-bb03-4674-92ab-33cef99afdb2'.

On Thu, Nov 30, 2017 at 3:23 PM, Jay Kreibich <[hidden email]> wrote:

>
> There are some minor points, but I agree that it basically boils down to
> “serial IDs break security-by-obscurity.”
>
> That’s true, but….
>
>   -j
>
>
>
>
> > On Nov 30, 2017, at 9:00 AM, Keith Medcalf <[hidden email]> wrote:
> >
> >
> > Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> is describing as the problems with serial IDs (or using the RowID) are
> simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the
> RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> Buggs Bunny would say "What a maroon!".
>
> >> -----Original Message-----
> >> From: sqlite-users [mailto:sqlite-users-
> >> [hidden email]] On Behalf Of Simon Slavin
> >> Sent: Thursday, 30 November, 2017 07:16
> >> To: SQLite mailing list
> >> Subject: [sqlite] Article on AUTOINC vs. UUIDs
> >>
> >> Thought some of you might enjoy seeing this article.  I make no
> >> comment on what I think of the reasoning therein.  It’s set in the
> >> PostgreSQL world, but you could make an external function for SQLite
> >> which generates UUIDs.
> >>
> >> <https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-
> >> increment-is-a-terrible-idea/>
> >>
> >> "Today, I'll talk about why we stopped using serial integers for our
> >> primary keys, and why we're now extensively using Universally Unique
> >> IDs (or UUIDs) almost everywhere."
> >>
> >> Simon.
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Stephen Chrzanowski
As one of the security guys here at work say, "Security does not help
convenience".  In the debug world, yeah, I agree, looking for 4310 is much
easier than 8af7* but, that should stick to a debug environment.

But to put a twist on this, and to lessen the paranoia of collisions, you
could implement a 1024 GIGABYTE  PK/FK key structure..... {smirk}

On Thu, Nov 30, 2017 at 10:48 AM, Chris Locke <[hidden email]>
wrote:

>
> I used to use UUIDs, but when looking at a database using many foreign
> keys, it was a debug nightmare looking for a specific key.  After switching
> to auto increment fields, its nice when debugging to look for
> 'templateId=4310' and not
> 'templateId='8af78580-bb03-4674-92ab-33cef99afdb2'.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Simon Slavin-3


On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski <[hidden email]> wrote:

> As one of the security guys here at work say, "Security does not help
> convenience".  In the debug world, yeah, I agree, looking for 4310 is much
> easier than 8af7* but, that should stick to a debug environment.

From the user/password system on in, almost all the code I write exists to stop people from doing things.  I’m serious.  I’d estimate about 70% authentication, cross-site scripting checks and log files, and 20% user-interface and 10% report/display.

By the way, using sequence numbers to deduce data was understood a hundred year ago (okay, 1920).  Adolf Hitler was the 55th member of the Nazi party but his membership number was 555 to make the party look bigger.  And pictures of British ship engine rooms and tank engines were not allowed to show engine serial numbers until after WW2 ended.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

wmertens
For userIds, I generate a unique id from their email address. If they
later change the email address, they keep the id anyway. I really like
natural keys.

Of course, if you want to use that id in URLs, it would be good to use
a second unique id that is not used as a foreign key, so that people
can change their "url-id" if needed.

On 11/30/17, Simon Slavin <[hidden email]> wrote:

>
>
> On 30 Nov 2017, at 3:52pm, Stephen Chrzanowski <[hidden email]> wrote:
>
>> As one of the security guys here at work say, "Security does not help
>> convenience".  In the debug world, yeah, I agree, looking for 4310 is much
>> easier than 8af7* but, that should stick to a debug environment.
>
> From the user/password system on in, almost all the code I write exists to
> stop people from doing things.  I’m serious.  I’d estimate about 70%
> authentication, cross-site scripting checks and log files, and 20%
> user-interface and 10% report/display.
>
> By the way, using sequence numbers to deduce data was understood a hundred
> year ago (okay, 1920).  Adolf Hitler was the 55th member of the Nazi party
> but his membership number was 555 to make the party look bigger.  And
> pictures of British ship engine rooms and tank engines were not allowed to
> show engine serial numbers until after WW2 ended.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

J Decker
In reply to this post by wmertens
On Thu, Nov 30, 2017 at 7:14 AM, Wout Mertens <[hidden email]>
wrote:

> The article is a bit muddled, the only real argument I could find is that
> auto-inc makes next keys easy to guess, which is information leakage, which
> means that is a potential security problem if that information were somehow
> useful.
>
> Seems to me that problem can be resolved by having an auto-inc type that
> skips a random amount on every insert? E.g. the next id is the last ID +
> randomFromRange(1, 10000).
>
> UUIDs are nice though if you don't have a natural key available and you are
> generating keys on multiple systems. Sadly, they are strings in sqlite, and
> it would be more efficient to store and compare them as their 128-bit
> representation. Is there an extension that can do that?
>
> I don't know about the collision rate; if your systems are set up in a sane
> way, the MAC address alone would prevent collisions, no? And on the same
> system, are collisions even possible?
>

virtual machines can easily duplicate mac addresses if copied and not
updated correctly.
It's also a leak of information, and if it's the server service that's
creating the UUID's using it's mac, doesn't matter that it's for lots of
clients, the odds of collision increase greatly... even if you have a small
cloud of 10's or 100's of systems.... the leak of information is why MS
moved away from mac addresses when genertaing GUIDs especially for things
like COM service IDs (that was more than a decade ago, so I'm finding it
hard to find articles on it).  There are also network device manufacturers
that relesaed hardware with duplicate mac addresses; but since they were
between multiple lots of product it wasn't noticed to a great extent.

For the project I used UUIDs extensively for, it was written in C# with
datasets with all foriegn keys modeled in it.   On insert, if there was a
collision, it would regenerate a UUID and update the offending row, which
would automaically propagate through all child rows; and since the parent
had to exist before inserting the children then continuing on from that
point was very little work for the database.  Though it only used a few
thousand IDs and after many years of usage would only be a couple hundred
thousand IDs I still coded it paranoid-like.

Some databases have preference for using Sequential UUIDs.

https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid
Laboratory Test – SQL Server

VS2008 test, 10 concurrent users, no think time, benchmark process with 600
inserts in batch for leaf table
Standard Guid
Avg. Process duration: 10.5 sec
Avg. Request for second: 54.6
Avg. Resp. Time: 0.26

Sequential Guid
Avg. Process duration: 4.6 sec
Avg. Request for second: 87.1
Avg. Resp. Time: 0.12

Results on Oracle (sorry, different tool used for test) 1.327.613 insert on
a table with a Guid PK

Standard Guid, 0.02 sec. elapsed time for each insert, 2.861 sec. of CPU
time, total of 31.049 sec. elapsed

Sequential Guid, 0.00 sec. elapsed time for each insert, 1.142 sec. of CPU
time, total of 3.667 sec. elapsed

Could really wish more languages had DataSet.

I would also like to make a note, that many criticisms are 'there's so many
bytes to have to compare', however, because of the highly random nature of
good UUIDs failure occurs quickly, usually within 4 bytes, which makes it
almost as good as an integer (especialy for things like SQLite that are
comparing numbers as strings anyway).... the only time the full thing is
compared is on the row that exactly matches.


'sides storage is cheap...
https://www.linkedin.com/pulse/20140414133905-9970539-peak-hard-drive/
would project 600TB drives by 2020, but another place noted that
commercial(home consumer) hard drives weren't increasing in size as fast as
datacenter/server drive storage.  but I can't find any recent articles that
also say that... looking at graphs of such information they all stop
2010-2013 ish... so no data for the last 4-7 years.... *shrug*



> On Thu, Nov 30, 2017 at 4:01 PM Keith Medcalf <[hidden email]> wrote:
>
> >
> > Well, in my opinion the guy is an idiot.  The way to avoid the issues he
> > is describing as the problems with serial IDs (or using the RowID) are
> > simple to avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing
> the
> > RowID in a URL is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy
> > Buggs Bunny would say "What a maroon!".
> >
> > Nonetheless, I have created an SQLite extension for Windows (2000 or
> > later) that will generate UUIDs using the builtin Windows RPC interface.
> > Apparently similar facilities are available on other OSes though all in
> > different manners (different functions in different libraries).  Note
> that
> > the silly proxies for the RPC functions are so that the compiler can
> > maintain correct linkage to the RPC libraries when using function
> pointers
> > -- the linkage through function pointers cast to (void*) works on 64-bit
> > Windows but not on 32-bit Windows.  On 32-bit windows not using a proxy
> > function to maintain the correct linkage results in the stack frame
> > corruption.
> >
> > Also, uuid generation function for V1/3/4/5 are available in the Python
> > standard uuid library, not mentioned in the article.
> >
> > File is sqlfwin.c located in
> > http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows
> > versions; or
> > http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit
> Windows
> > versions
> > Source is the same in both, .dll compiled with MinGW 7.1.0.2
> (dependencies
> > only to standard windows DLLs and to the subsystem runtime library).
> >
> > SQLite3 UDF functions returning blobs (16-byte UUID) are:
> > uuidCreateV1()
> > uuidCreateV4()
> > uuidFromString('text-uuid-rendering')
> >
> > And returning textual renderings are:
> > uuidStringCreateV1()
> > uuidStringCreateV4()
> > uuidToString(uuid-blob)
> >
> > The create functions are volatile (like the randomblob function), and the
> > To/From string functions are deterministic.
> >
> > sqlfwin.c also contains some other Windows API functions for working with
> > the builtin windows security such as looking up names and sids, checking
> > whether the current process access token contains a given sid/name,
> getting
> > the current process access token username, computername, FQDN, and a few
> > others.
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> > >-----Original Message-----
> > >From: sqlite-users [mailto:sqlite-users-
> > >[hidden email]] On Behalf Of Simon Slavin
> > >Sent: Thursday, 30 November, 2017 07:16
> > >To: SQLite mailing list
> > >Subject: [sqlite] Article on AUTOINC vs. UUIDs
> > >
> > >Thought some of you might enjoy seeing this article.  I make no
> > >comment on what I think of the reasoning therein.  It’s set in the
> > >PostgreSQL world, but you could make an external function for SQLite
> > >which generates UUIDs.
> > >
> > ><https://www.clever-cloud.com/blog/engineering/2015/05/20/why-auto-
> > >increment-is-a-terrible-idea/>
> > >
> > >"Today, I'll talk about why we stopped using serial integers for our
> > >primary keys, and why we're now extensively using Universally Unique
> > >IDs (or UUIDs) almost everywhere."
> > >
> > >Simon.
> > >_______________________________________________
> > >sqlite-users mailing list
> > >[hidden email]
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

wmertens
Thanks, very insightful!

On Thu, Nov 30, 2017 at 5:27 PM J Decker <[hidden email]> wrote:

> I would also like to make a note, that many criticisms are 'there's so many
> bytes to have to compare', however, because of the highly random nature of
> good UUIDs failure occurs quickly, usually within 4 bytes, which makes it
> almost as good as an integer (especialy for things like SQLite that are
> comparing numbers as strings anyway).... the only time the full thing is
> compared is on the row that exactly matches.
>

Aha so that's why the UUIDs are don't have the full time as the first part…
I was wondering why they did not use this easy "sort by creation date"
shortcut…
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Keith Medcalf
In reply to this post by J Decker

On Thursday, 30 November, 2017 09:27, J Decker <[hidden email]> wrote:

>> UUIDs are nice though if you don't have a natural key available and
>> you are generating keys on multiple systems. Sadly, they are strings
>> in sqlite, and  it would be more efficient to store and compare them
>> as their 128-bit representation. Is there an extension that can do
>> that?

Just store them as a 16-byte blob.  What's the problem?

>I would also like to make a note, that many criticisms are 'there's
>so many bytes to have to compare', however, because of the highly
>random nature of good UUIDs failure occurs quickly, usually within
>4 bytes, which makes it almost as good as an integer

Or just use randomblob(16) ...

>(especialy for things like SQLite that are comparing numbers as
>strings anyway).... the only time the full thing is compared is
>on the row that exactly matches.

I do not know what version of SQLite3 you are using, but according
to the source code, only strings are compared as strings.  Everything
else is compared using the appropriate Affinity -- Double to Double,
Integer to Integer, BLOB to BLOB, etc.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

J Decker
On Thu, Nov 30, 2017 at 11:52 AM, Keith Medcalf <[hidden email]> wrote:

>
> On Thursday, 30 November, 2017 09:27, J Decker <[hidden email]> wrote:
>
> >> UUIDs are nice though if you don't have a natural key available and
> >> you are generating keys on multiple systems. Sadly, they are strings
> >> in sqlite, and  it would be more efficient to store and compare them
> >> as their 128-bit representation. Is there an extension that can do
> >> that?
>
> Just store them as a 16-byte blob.  What's the problem?
>
> >I would also like to make a note, that many criticisms are 'there's
> >so many bytes to have to compare', however, because of the highly
> >random nature of good UUIDs failure occurs quickly, usually within
> >4 bytes, which makes it almost as good as an integer
>
> Or just use randomblob(16) ...
>
The problem with that is continual reformatting

>
> >(especialy for things like SQLite that are comparing numbers as
> >strings anyway).... the only time the full thing is compared is
> >on the row that exactly matches.
>
> I do not know what version of SQLite3 you are using, but according
> to the source code, only strings are compared as strings.  Everything
> else is compared using the appropriate Affinity -- Double to Double,
> Integer to Integer, BLOB to BLOB, etc.
>

Hmm; I thought it did store numbers as binary; but over the years of
watching various messages on this list, I got the idea that numbers were
being stored as strings also and haven't double checked.... my bad.  Maybe
it something about dates...

>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Article on AUTOINC vs. UUIDs

Jens Alfke-2
In reply to this post by Stephen Chrzanowski


> On Nov 30, 2017, at 6:52 AM, Stephen Chrzanowski <[hidden email]> wrote:
>
> I'm going to read it later, but, I'll be going in skeptical.  Collision is
> real, even at 128-bit resolution

IIRC, to have a realistic chance of a collision you'd have to generate about √(2^128) UUIDs, which is 2^64, which about 2 x 10^19, i.e. 20 quintillion. I don’t think there’s a real chance that this database will be in operation long enough to generate that many records!

UUIDs are probably overkill for typical databases, but they’re extremely useful for distributed ones, where a centralized counter becomes both a performance bottleneck, a single point of failure, and in some cases (P2P) a trust problem.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users