Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The perils of UUID primary keys in SQLite

andersmurphy.com

Post reply on HN
Print view View original

61–70 of 117 posts

Re: The perils of UUID primary keys in SQLite

#61

Post by adityaathalye »

Thanks for the benching, Anders! So grateful for the stuff you've shared over the years. Invariably, every single post has been useful and/or educational to me.

I read this post more as an illustration of the *value* of UUIDv7 as primary key, over integer primary keys, in lieu of minimal loss of read/write performance, and marginally more data on disk bloat.

SQLite's automatic integer rowID primary key is a no-brainer, when the SQLite application is local-only, such as application storage format (mobile and desktop). Or is never intended to grow beyond a single server instance. Basically, where each SQLite file is private to a singular instance of the application.

However, if there is even an outside chance of needing to cooperate across application instances, e.g. the minimal limit case of a personal knowledge base that should seamlessly sync across a person's devices, as well as a hosted service, then a high-quality sequential random ID starts to make a lot more sense. (No-brainer arbitrary table merges / splits / remerges, de-duplication, etc.)

Random ID primary key is a bad idea period, whether it be the UU kind or the SQ kind, or any other kind. As far as my DB knowledge goes, this class of ID destroys all tree-algorithms, and we are stuck with the fact that there is no practically better way, than an appropriate tree-structure, to group and organise a meaningful amount of data, efficiently and effectively.

Re: The perils of UUID primary keys in SQLite

#62

Post by adityaathalye »

adityaathalye wrote: post #61

Thanks for the benching, Anders! So grateful for the stuff you've shared over the years. Invariably, every single post has been useful and/or educational to me. I read this post more as an illustration of the *value* of UUIDv7 as primary key, over integer primary keys , in lieu of minimal loss of read/write performance, and marginally more data on disk bloat. SQLite's automatic integer rowID primary key is a no-brain…

Aside: Specific to SQLite...

Thanks to its oh so convenient automatic integer rowIDs, I believe one can amortise some of the other overheads of UUIDv7s for "in-between" queries, viz. indices, joins, ctes, virtual tables etc., with appropriate schema / query design.

Re: The perils of UUID primary keys in SQLite

#63

Post by ysleepy »

andersmurphy wrote: post #32

This is actually a draft. I Wanted to add more details about how this changes with row size etc. I might get time to update it later today.

Maybe you could explain why one would use "without rowid" in the first place.

I get saving 8 bytes per row seems attractive, but the tradeoff is not explained.

Re: The perils of UUID primary keys in SQLite

#65

Post by scotty79 »

jdthedisciple wrote: post #48

So UUID isn't the problem but UUID v4 is, just like any random ID-scheme, correct? UUID v7 so far seems like the best solution if you want UUID benefits and ordering.

It's " WITHOUT ROWID" problem.

Why would you force database to order rows on the drive according to random id?

Re: The perils of UUID primary keys in SQLite

#66

Post by mort96 »

sheept wrote: post #59
mort96 wrote: post #57

Earlier quoted context omitted.

And then you end up with strings on the other side, not numbers.

No you don't? The example I gave produces {"a":9007199254740993} not {"a":"9007199254740993"}

Oh, that's much worse! The JSON string `{"a":9007199254740993}` decodes to the object `{"a":9007199254740992}` with typical JSON parsers like JavaScript's `JSON.parse`.

Re: The perils of UUID primary keys in SQLite

#67

Post by Volundr »

voakbasda wrote: post #45
littlecranky67 wrote: post #39

Earlier quoted context omitted.

But SQLite does not have a native datetime type so you have to use strings

You can use an integer

How do I know the time zone of an integer? Sure there are plenty of cases where one doesn't care, but there are also many cases where the original time zone is important.

Re: The perils of UUID primary keys in SQLite

#68

Post by victorbjorklund »

bambax wrote: post #44

Why would you use UUIDs a primary keys? Let SQLite use rowids internally (which is automatic and invisible), and have a different (indexed) column with UUID if you need that for publishing the ID somewhere.

Because another app can then create the id and add it to the db later.

Re: The perils of UUID primary keys in SQLite

#69

Post by littlecranky67 »

voakbasda wrote: post #45
littlecranky67 wrote: post #39

Earlier quoted context omitted.

But SQLite does not have a native datetime type so you have to use strings

You can use an integer

other comment said it already, timezone information is not saved. Easiest is just to use a string.

Re: The perils of UUID primary keys in SQLite

#70

Post by andersmurphy »

kjgkjhfkjf wrote: post #60

The script to create the benchmark numbers appears to be inserting 100 batches, not 10. (The benchmark numbers in the table appear to be consistent with the text, so I guess the actual script used to create them was correct.)

Yeah that was just a holdover from when I was playing with smaller batch sizes. It's not in the actual linked source.
Post reply on HN