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

11–20 of 117 posts

Re: The perils of UUID primary keys in SQLite

#11

Post by bob1029 »

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…

I am finding UUIDs help a lot if your primary schema consumer is an LLM.

Inappropriate aliasing of integer keys allows for silent errors in queries because it will actually return some result a lot of the time. A UUID is immune to this problem. The model recognizes its mistake a lot more reliably when previously non-empty tables start showing up empty after attempting a join.

Re: The perils of UUID primary keys in SQLite

#14

Post by JamesSwift »

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

Re: The perils of UUID primary keys in SQLite

#15

Post by dexterdog »

yepyoukno wrote: post #9
JSR_FDED wrote: post #8

Earlier quoted context omitted.

Small nit: uuid7 is 128 bits (16 bytes) by definition. So there’s no need to convert it to binary. It already is. Unless you’re working with a stringified version of the uuid7.

Oh yes, I meant don’t store as an ID in its string format!

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

Re: The perils of UUID primary keys in SQLite

#16

Post by crubier »

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…

No one ever got fired for using UUIDs

Re: The perils of UUID primary keys in SQLite

#17

Post by Fabricio20 »

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.

Re: The perils of UUID primary keys in SQLite

#19

Post by pyuser583 »

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

Yeah this is nice - also helps with grepping dump files.

Re: The perils of UUID primary keys in SQLite

#20

Post by spiffytech »

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.

Fortunately we're seeing more JS DB libraries offering to read large numbers as the BigInt type.
Post reply on HN