The perils of UUID primary keys in SQLite • Hacker News Board

Re: The perils of UUID primary keys in SQLite

#101

Post by throw-the-towel » Sun, Jun 07, 2026, 2:07 AM UTC

zmj wrote: post #73

silvestrov wrote: post #42

Earlier quoted context omitted.

Good trick is to prefix all such keys with magic, i.e. a couple of letters that identify type type of key. Then it will always be a string and you will be free to change the format/type of the key in the future to UUID or whatever you like.

Rule of thumb: if you’re not doing math with a value, it’s not a number.

Speaking of which, one of my favourite UX brainfarts is treating text fields where you enter a sum as numbers.

Why, you ask? Let's you have a number like 10,000 and you want to replace it with 20,000. You delete the leading 1, and boom! The number is now zero, and three of the digits are gone, and you'll have to retype them like you got no other things to do with your life.

Re: The perils of UUID primary keys in SQLite

#102

Post by [deleted] » Sun, Jun 07, 2026, 2:36 AM UTC

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.

[deleted]

Re: The perils of UUID primary keys in SQLite

#103

Post by pavo-etc » Sun, Jun 07, 2026, 8:00 AM UTC

usrnm wrote: post #50

sudoshred wrote: post #24

Earlier quoted context omitted.

Statistically impossible to inadvertently generate a collision using UUID keys. UUID is designed to be unique when generated across any computer system. Practically speaking if you have an exactly matching pair of UUIDs from disparate system you have found the exact record match. The name gives a hint "Universally unique identifier". -Not a cryptographer.

It definitely is possible, just very improbable

"very" is underselling it

Re: The perils of UUID primary keys in SQLite

#104

Post by Terr_ » Sun, Jun 07, 2026, 9:37 AM UTC

Fabricio20 wrote: post #17

blopker wrote: post #5

UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints. For a single database, bigints are smaller and faster, with less footguns. UUIDs can be nice for an opaque public ID, however I'd still prefer…

> bigints are smaller and faster, with less footguns But be careful!! Javascript WILL interpret your bigints as Number() and round them down because they are too big without telling you!!! Famously seen by every snowflake user that has interacted with Javascript, quite an annoying problem.

> Javascript WILL interpret your bigints as Number()

A similar horror story from PHP, which I discovered by diagnosing a test failure. (Or maybe it was in production? Long ago, can't remember.)

I think the code in question was for some kind of web auth, comparing random 32-character hexadecimal strings. PHP has a "feature" where its == operator falls back to trying certain strings as numbers... and that includes a version with scientific notation. (12000 == "12000" == "12e3")

Such a collision through bad comparison may seem unlikely, but there are two islands of higher odds: 0*10^X is zero for any X, and X*10^0 is one for any X. Finally, leading zeros can be included. ("0e1234" == "00000e1" and "1234e0" == "9e0000")

The fix was simply going to stricter ===, but it definitely reinforced my dislike of "loose" languages.

Re: The perils of UUID primary keys in SQLite

#105

Post by scotty79 » Sun, Jun 07, 2026, 11:08 AM UTC

chromatin wrote: post #78

scotty79 wrote: post #65

Earlier quoted context omitted.

It's " WITHOUT ROWID" problem. Why would you force database to order rows on the drive according to random id?

If you had read the article, you'd have seen that UUIDv4 with Rowid was slightly slower than UUIDv7

That's unbelievable! As in "I did not believe it". I mean it's nominally true, but only because this table has almost nothing in it, so a second index on random uuid is a visible cost. If this was actual table in actual software it would hold few other indexes and many fields and uuid+rowid vs int primary key would be a rounding error.

I never wished, gee, why didn't I use integer key. But so many times I wished I used uuid because eventually your data rows are going to need to have identity that is not local to this specific database instance.

Re: The perils of UUID primary keys in SQLite

#106

Post by gvkhna » Sun, Jun 07, 2026, 3:14 PM UTC

ItsBob wrote: post #49

My rule for primary keys and id's is simple: Sequential integer (or bigint) as the PK and if I need to make it public, I have a GUID (or UUID) in the row too, e.g. tbl_person would have Id (int|bigint) and person_guid as (UUID). The Integer id is used for joins and looks ups and such but that's it. If I need to send anything to the frontend or outside of the app/DB then that's the UUID.

I agree technically but in most use cases the timestamp from uuidv7 is not a security leak. Especially where you’re already sharing that data in some way or another. A default guid is unnecessary if you use uuidv7 I think (in most situations).

Re: The perils of UUID primary keys in SQLite

#107

Post by gvkhna » Sun, Jun 07, 2026, 3:17 PM UTC

UUIDv7 as pk is inherently the best option besides just a bigint where you really don’t need a public id.

But a Url62 as a url safe public id from the pk is simple and straightforward to use and comes with few risks of leak issues. Wish postgres had native base62 encoding for url62 now that it has uuidv7 native.

Re: The perils of UUID primary keys in SQLite

#108

Post by beagle3 » Sun, Jun 07, 2026, 4:42 PM UTC

ErroneousBosh wrote: post #96

usrnm wrote: post #50

Earlier quoted context omitted.

It definitely is possible, just very improbable

It definitely is possible, just very much a "woah, shit, guys come and look at this!" moment.

More like a moment that the guys can’t come because each one was independently struck by a lightning.

Re: The perils of UUID primary keys in SQLite

#109

Post by sgarland » Sun, Jun 07, 2026, 5:26 PM UTC

JamesSwift wrote: post #14

blopker wrote: post #5

UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints. For a single database, bigints are smaller and faster, with less footguns. UUIDs can be nice for an opaque public ID, however I'd still prefer…

UUIDs also have a nice benefit of it being impossible to query the wrong table with one if you mixup what an FK goes to

Or just write tests, instead of relying on statistical improbability to prevent disaster.

Re: The perils of UUID primary keys in SQLite

#110

Post by sgarland » Sun, Jun 07, 2026, 5:29 PM UTC

willtemperley wrote: post #88

blopker wrote: post #5

UUIDs are way over used. There is almost always a better key to use, usually a bigint for databases. If you're making some kind of leaderless distributed data store, then maybe, but even then there are other ID sharding strategies I'd go for first depending on the constraints. For a single database, bigints are smaller and faster, with less footguns. UUIDs can be nice for an opaque public ID, however I'd still prefer…

UUIDs make client code so much simpler. Just create a UUID, use it client side to create your object graph and commit or not as appropriate. No need to retrieve an incremented integer.

Every DB, even MySQL can return the autoincrementing integer for you as part of the insert. Postgres, SQLite, and MariaDB (likely others, I’m just not familiar) can even return the rest of the data, should you need that.

IME, most of the arguments for why UUIDs make things better are due to developer ignorance of RDBMS features (or B+tree performance).