Re:Unexpected downsides of UUID keys in PostgreSQL
by jgraettinger1 on Thursday June 22, 2023 @01:57PM
(#36436137)
We use a macaddr8 that embeds a wall-clock timestamp (so they're ascending order, achieving data locality) with some additional shard and sequence-number bits. It's worked really well for us:
https://github.com/estuary/flow/blob/master/supabase/migrati...
we use macaddr8 instead of bigint, because it has a postgres serialization / JSON encoding which lossless-ly round-trips with browsers and it works well with PostgREST. The same CANNOT be said for bigint, which is a huge footgun.
But sometimes you have to make these keys public, e.g. as user or other resource IDs. You want to make them UUIDs so they won't be predictable. Not having to join everywhere with the UUID-to-artificial-PK table may be a bigger performance win than the losses from larger size of UUIDs.
Sometimes you have a distributed / sharded system, and don't want the keys to clash, and also avoid assigning ranges. Sometimes you have to accept someone else's ID, not originating in your system. In cases like that, large random numbers, e.g. UUID v4, work reasonably well.
Of course when you just have one DB, and a relative slow stream of new rows, it's easy to fully control PK creation. And this covers the majority of practical cases.