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

21–30 of 117 posts

Re: The perils of UUID primary keys in SQLite

#21

Post by paulddraper »

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.

!!

Node.js drivers will correctly read int64 as string or bigint, not number.

E.g. pg for PostgreSQL

Maybe there’s a buggy driver but I don’t know it.

Re: The perils of UUID primary keys in SQLite

#22

Post by themafia »

yepyoukno wrote: post #2

Perils of “UUIDv4”. Everyone knows that’s what UUIDv7 was really for, and you should always convert that to binary to optimize everything.

> and you should always convert that to binary to optimize everything

I disagree. I tried this once. Now you need a client access layer to touch the DB in any context. All your console tools no longer work well or at all. If they show up in URLs you need to deoptimize them for transport.

You give up a lot of convenience for this optimization. You should be absolutely sure your design requires it before using it.

Re: The perils of UUID primary keys in SQLite

#23

Post by mamcx »

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

How is this done?

Re: The perils of UUID primary keys in SQLite

#24

Post by sudoshred »

mamcx wrote: post #23
JamesSwift wrote: post #14

Earlier quoted context omitted.

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

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.

Re: The perils of UUID primary keys in SQLite

#25

Post by nickpeterson »

mamcx wrote: post #23
JamesSwift wrote: post #14

Earlier quoted context omitted.

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

How is this done?

They just mean you catch incorrect joins more easily because there is usually no overlap in keys between unrelated tables. Using int, you’re usually going to have some shared values between two unrelated tables.

Re: The perils of UUID primary keys in SQLite

#26

Post by KPGv2 »

smitty1e wrote: post #6
dumbledorf wrote: post #3

Wait how is sqlite doing a million inserts a second?

':memory:' https://sqlite.org/inmemorydb.html

Except this source code is not using :memory: The linked source code has

    (defonce db
      (d/init-db! "db/db.db"
        {:pool-size 4 :pragma {:synchronous "FULL"}}))
That's writing to disk.

Re: The perils of UUID primary keys in SQLite

#28

Post by masklinn »

mamcx wrote: post #23
JamesSwift wrote: post #14

Earlier quoted context omitted.

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

How is this done?

The U means if you join the wrong table your join will always come up empty.

It does not actually make it impossible to query the wrong table it just tells you quickly when you’ve done so.

Re: The perils of UUID primary keys in SQLite

#30

Post by shakna »

spiffytech wrote: post #20
Fabricio20 wrote: post #17

Earlier quoted context omitted.

> 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.

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.

Post reply on HN