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

Re: The perils of UUID primary keys in SQLite

#41

Post by mort96 » Sat, Jun 06, 2026, 7:27 AM UTC

anematode wrote: post #31

shakna wrote: post #30

Earlier quoted context omitted.

But frustratingly, a JS BigInt is nothing like a BigInt in any other language. In JS - BigInt is 64bit integer. In anything else - BigInt is a arbitrarily large integer.

Hm? JavaScript BigInts are arbitrary precision, and you need to use methods like BigInt.asIntN(64, a) to convert them to 64 bits

I hate this so much because you can’t nicely serialise a BigInt as JSON. Using a string is nicer but it only makes sense where int64 is used as an ID, not where it’s used as a number; and you don’t wanna have to configure this per field per query.

Re: The perils of UUID primary keys in SQLite

#42

Post by silvestrov » Sat, Jun 06, 2026, 7:32 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.

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.

Re: The perils of UUID primary keys in SQLite

#43

Post by ac50hz » Sat, Jun 06, 2026, 7:42 AM UTC

I enjoy these carefully worded posts from Anders Murphy, illustrative and informative, not opinionated and preachy. Very useful, it’s great to see the process, and ofc bookmarkeable material for sharing with others.

Re: The perils of UUID primary keys in SQLite

#44

Post by bambax » Sat, Jun 06, 2026, 7:49 AM UTC

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.

Re: The perils of UUID primary keys in SQLite

#45

Post by voakbasda » Sat, Jun 06, 2026, 7:52 AM UTC

littlecranky67 wrote: post #39

dexterdog wrote: post #15

Earlier quoted context omitted.

It's just s dumb as storing dates as strings, but people still do it.

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

You can use an integer

Re: The perils of UUID primary keys in SQLite

#46

Post by andersmurphy » Sat, Jun 06, 2026, 8:00 AM UTC

cropcirclbureau wrote: post #40

Is this relevant for other databases? For postgres for example, which supports concurrent writers, wouldn't sequential keys lead to contention on the page at the frontier?

That's a good question. I don't know the answer. I will say, generally you can get higher write throughput with a single writer. Even more so if you're prepared to shard along boundaries where you don't need atomic transactions.

Contention and coordination are real killers, concurrent writes (that require coordination like postgres) often underdeliver.

Re: The perils of UUID primary keys in SQLite

#47

Post by nh2 » Sat, Jun 06, 2026, 8:11 AM UTC

mort96 wrote: post #41

anematode wrote: post #31

Earlier quoted context omitted.

Hm? JavaScript BigInts are arbitrary precision, and you need to use methods like BigInt.asIntN(64, a) to convert them to 64 bits

I hate this so much because you can’t nicely serialise a BigInt as JSON. Using a string is nicer but it only makes sense where int64 is used as an ID, not where it’s used as a number; and you don’t wanna have to configure this per field per query.

JSON has arbitrary length numbers in the spec only.

Re: The perils of UUID primary keys in SQLite

#48

Post by jdthedisciple » Sat, Jun 06, 2026, 8:27 AM UTC

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.

Re: The perils of UUID primary keys in SQLite

#49

Post by ItsBob » Sat, Jun 06, 2026, 8:37 AM UTC

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.

Re: The perils of UUID primary keys in SQLite

#50

Post by usrnm » Sat, Jun 06, 2026, 9:10 AM UTC

sudoshred wrote: post #24

mamcx wrote: post #23

Earlier quoted context omitted.

How is this done?

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