Greenfield, Postgres, per-tenant isolation by org_id. Roughly 50k rows a day across the main tables. I keep reading that random uuids destroy index locality and that bigserials leak business information, which are both true and unhelpful at the same time. What do people actually run?
v7 if ids appear in URLs or get generated client-side, bigserial if they never leave the database.
The locality argument is about v4. v7 is time-ordered, so inserts land near the right edge of the btree the way a sequence does — you do not get the random page-split behaviour that produced all the scary benchmarks. What you do pay is 16 bytes instead of 8, in the table and in every index that references it. At 18M rows a year, that is a rounding error on your disk bill for a long time.
The argument that actually decides it for B2B: sequential ids are enumerable. A customer who signs up, creates one invoice and sees id 48213 now knows roughly how many invoices you have processed. People do check.
The enumeration point is the real one. Nobody has ever been fired over eight extra bytes per row, and several people have had an awkward call about a competitor reading their growth rate off a URL.
One thing that settles it for some apps: if you want to render an optimistic row before the server responds, the client has to mint the id. That requires v7 or ulid and ends the discussion.
Whatever you choose, store uuids in a uuid column, not text or varchar(36). Twice the storage, slower comparisons, and no validation. I have seen this in production more than once.
uuids are always slower to index, that is just how btrees work with wide random keys.
Random keys, yes. v7 is not random in the high bits — it is a timestamp prefix, so inserts are close to sequential. Every benchmark that scares people about uuid primary keys was run on v4.
@migration_marek · 7mo ago · 2 replies
Do both.
bigserial(or identity) as the internal primary key that all your foreign keys use, plus apublic_idwith a unique index for URLs and the API. Joins stay narrow, external ids stay opaque, and you can change the external format later without touching your relationships.Prefix the public id by type —
inv_01hq...,org_01hq.... It costs nothing and the first time you are staring at a log line with a bare id and no idea what table it is from, you will understand.Reply
Report
@nadia_brill · 7mo ago
Went with this. Six months later the prefixes have saved me more debugging time than the choice of id type could ever have cost me.
Reply
Report