Hacker News Reader

Comment by evil-olive | Hacker News Reader

> incorrectly advising people to use uuid4 or v7 PKs with Postgres

random UUIDs vs time-based UUIDs vs sequential integers has too many trade-offs and subtleties to call one of the options "incorrect" like you're doing here.

just as one example, any "just use serial everywhere" recommendation should mention the German tank problem [0] and its possible modern-day implications.

for example, if you're running a online shopping website, sequential order IDs means that anyone who places two orders is able to infer how many orders your website is processing over time. business people usually don't like leaking that information to competitors. telling them the technical justification of "it saves 8 bytes per order" is unlikely to sway them.

0: https://en.wikipedia.org/wiki/German_tank_problem

View on HN

PK isn't the same as public ID, even though you could make them the same. Normally you have a uuid4 or whatever as the public one to look up, but all the internal joins etc use the serial PKs.

> Normally you have a uuid4 or whatever as the public one to look up, but all the internal joins etc use the serial PKs.

what? that's possible, but it's the worst of both worlds. I've certainly never encountered a system where that's the "normal" practice.

the usual reason people avoid UUIDv4 primary keys is that it causes writes to be distributed across the entire B-tree, whereas sequential (or UUIDv7) concentrates them.

but if you then add a "alternate primary key" you're just re-creating the problem - the B-tree for that unique index will have its writes distributed at random.

if you need a UUID PK...just use it as the PK.

The problem isn't so much the writes, it's the reads. Every time you join tables, you're using a PK 2-4x the size it needs to be, and at least that much slower. Even filtering on a secondary index may involve an internal lookup via PK to the main table. It doesn't take long to start noticing the performance difference.

Since you'd have a secondary index for the public UUID, yes that one index suffers from the random-writes issue still, but it takes a lot of volume to notice. If it ever is a big deal, you can use a separate KV store for it. But if you picked UUID as the PK, it's harder to get away from it.

Well that and all the tables you have that don't need a customer-facing ID at all, in which case you also benefit from quicker writes using serial

Reply on news.ycombinator.com