UUIDv4, UUIDv7 or bigint: a primary key the warehouse can live with

A primary key is copied into every change event, fact table and join. bigint, UUIDv4 and UUIDv7 compared on index locality, compressed size, merge cost and what each leaks.

App insert Key generator Primary index Change stream Merge Columnar files Join query Metrics bigint, UUIDv4 or UUIDv7 in the warehouse storage and scan
One line from the app's insert to storage and scan metrics, where every stop after the key generator inherits the key it picked.
On this page
  1. Nothing after the generator gets a vote
  2. Thistle Parcel and its 900 million shipments
  3. What random bytes do to files and merges
  4. Two metadata queries for your own table
  5. Where bigint runs out
  6. Version 7, fine print included

The most copied column in a company’s data gets chosen in a design review the data team isn’t in.

It’s the primary key of a busy table. An application engineer picks its type in about a minute, for sound application reasons. From then on the value is copied into every change event and every fact table, and the warehouse compares it in every join it runs. So the key’s width and its ordering are analytics decisions too. They mostly get made by default.

A Postgres table has three realistic candidates today: a bigint identity column, a random UUID (version 4), and the time-ordered version 7 that RFC 9562 standardized this May. I think only the last one works for both sides.

Nothing after the generator gets a vote

The diagram at the top follows one key value. The application inserts a row, and something generates the key. Postgres files it in the primary index. A change data capture connector such as Debezium keys each event by it and repeats it inside the payload. A warehouse merge matches on it. It lands in columnar files, once in the fact table and again as a foreign key in every table that points there. Then a join compares it a few billion times.

One detail in the middle of that route is easy to miss. Debezium emits a Postgres uuid column as a 36-character string. Unless the sink converts it back, text is what the warehouse stores, and a 16-byte value has more than doubled before anyone queries it.

Thistle Parcel and its 900 million shipments

Thistle Parcel doesn’t exist. I made up the carrier and every figure attached to it, so the comparison would have a scale. Nobody measured these numbers.

Its shipments table has 900 million rows. A nightly copy-on-write merge keeps an Iceberg copy current, sorted by key. About 4 million rows change each night, mostly recent shipments picking up a new status. A much larger scans table carries the shipment key on every row.

Property bigint identity UUIDv4 UUIDv7
Bytes per value 8 16 as binary, 36 as text 16 as binary, 36 as text
Primary index locality Appends to the last page A random page per insert Appends to the last page
Key column in Thistle’s fact table, compressed 3.4 GB 19 GB as text 9 GB as binary
Files Thistle’s nightly merge rewrites Few, the newest 96 percent 7 percent
Unique across sources No Yes Yes
Sorts by creation time Yes, within one source No Yes
Leaks row counts Yes No No, but it shows creation time

What random bytes do to files and merges

Compression needs repetition, and a version 4 UUID is 122 bits of noise. As text it costs Thistle about 21 bytes a row, or 19 GB for the column. Converting it to binary would stop near 14.4 GB, which is 16 bytes times 900 million with nothing left to squeeze. Sorted integers share most of their bytes with their neighbors and come in under four bytes each. Version 7 lands in between. Its leading timestamp bytes nearly vanish and its random tail doesn’t, so it settles at about 10 bytes.

The scans table pays the same rate again on every row.

The merge is worse. A copy-on-write merge rewrites every data file that holds a changed row. With version 7, sorting by key is sorting by creation time, so tonight’s changes sit together in the newest files and the merge rewrites 7 percent of them. Version 4 sprinkles the same 4 million rows evenly through the key range. The merge rewrites 96 percent of the files, nearly the whole table, to change under half a percent of its rows. Merge-on-read only defers the bill, because the delete files it scatters across the table come due at compaction.

The join gains from both effects. Thistle’s shipments-to-scans join takes 212 seconds on version 4 text keys and 138 on version 7 binary. There’s less to read and less to hash per comparison. And when a report covers a date range, an engine that pushes the join’s key range into the scan can skip whole files of scans.

Two metadata queries for your own table

Both read Iceberg metadata tables from Spark SQL, so neither scans any data.

-- Compressed size of the key column across live data files.
-- column_sizes is keyed by Iceberg field id. shipment_id is the
-- table's first column here, so its id is 1.
SELECT
  count(*)                                           AS data_files,
  sum(record_count)                                  AS row_count,
  round(sum(column_sizes[1]) / 1e9, 1)               AS key_gb,
  round(sum(column_sizes[1]) / sum(record_count), 1) AS bytes_per_key
FROM lake.ops.shipments.files
WHERE content = 0;  -- data files only
-- Share of data files each of the last seven merges rewrote.
-- A copy-on-write MERGE commits as an 'overwrite' snapshot.
SELECT
  s.committed_at,
  cast(s.summary['deleted-data-files'] AS bigint) AS files_rewritten,
  cast(p.summary['total-data-files'] AS bigint)   AS files_before,
  round(100.0 * cast(s.summary['deleted-data-files'] AS bigint)
              / cast(p.summary['total-data-files'] AS bigint), 1) AS pct_rewritten
FROM lake.ops.shipments.snapshots AS s
JOIN lake.ops.shipments.snapshots AS p
  ON p.snapshot_id = s.parent_id
WHERE s.operation = 'overwrite'
ORDER BY s.committed_at DESC
LIMIT 7;

A snapshot’s deleted-data-files counts the files that commit replaced, and the parent snapshot supplies the total it started from. If the last column comes back above 90, the job is a full rewrite every night, whatever the SQL calls it.

Where bigint runs out

bigint wins or ties every storage row in that table. For a single database whose IDs never leave the building, I’d still pick it.

It fails when a second source arrives. A regional shard or an acquired system has its own shipment 48112907, so the warehouse adds a source column to the key, and every join and merge condition carries two columns from then on.

It also counts out loud. A sequential ID in a tracking URL is a counter anyone can read. Create a shipment on Monday and another on Friday, subtract, and that’s the week’s volume.

Version 7, fine print included

So I’d choose version 7 and keep it at 16 bytes at every stop. That means the uuid type in Postgres and Iceberg’s uuid type, which Parquet writes as a 16-byte fixed-length value, with a sink in between that converts Debezium’s string back.

The fine print is short. No Postgres release has a version 7 generator yet, and gen_random_uuid() returns version 4, so the application has to generate the key with a library. The first 48 bits are a millisecond timestamp, too, which means anyone holding an ID can read when the row was created. If that’s sensitive, expose a different identifier.

For a table already on version 4, swapping the key is an expand and contract migration across everything that references it, and I wouldn’t start one for this alone. I’d store the key as binary and sort the fact table by a creation timestamp. A lower bound on that timestamp in the merge condition then lets the engine skip old files. That’s the ordering a version 7 key would have carried for free.