Hacker News Reader

Comment by nine_k | Hacker News Reader

If you want locality, speed, simplicity, etc above all, use an incremented integer and be done with it.

UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price.

In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lower-hanging fruit has been collected. There are relatively few places where their role is dramatic.

View on HN

> UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price.

You often don't realize you you have non-simple needs until your application is reasonably mature, and in production. If you already picked integer keys, you now either forever deal with the issues caused by not using UUIDs, or you deal with the unknown-but-non-zero pain of converting to UUIDs.

The age old startup bargain: do you want to pay a small cost up front, or a large one later? If only you'd seen it coming!

> Where you e.g. cannot coordinate the creation of your primary keys.

You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online?

> Or where you cannot allow them to be predictable.

You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a different column will still have some of the problems with index maintenance, but it becomes a much smaller problem if only one table cares about the value.

You can almost always opt for artificial PKs that are more performant.

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.

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.

Reply on news.ycombinator.com