3 ms·

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

> 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 https://en.wikipedia.org/wiki/German_tank_problem

jim33442 6mo ago
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.
- evil-olive 6mo ago
  > 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.
  - traderj0e 6mo ago
    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.
    - traderj0e 6mo ago
      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

jim33442 6mo ago

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.

evil-olive 6mo ago
> 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.
- traderj0e 6mo ago
  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.
  - traderj0e 6mo ago
    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