practices
guide
08 Oct 2026
One million rows, seven primary keys, and the write ahead log each one cost
The same million rows loaded seven times into Postgres 17, changing only the key. Index size moved by 3.5x and WAL by 2x, and UUID v7 lost its whole advantage at a rate most people never check.
By Purvansh Parmar
Checked 2026-10-21
Read 9 min
Entries 7
Pick the primary key once and you pay for it on every insert, every index page, every byte of replication traffic and every base backup for as long as the table exists. The usual argument stops at whether random keys fragment the index. That is the small half of the bill. Loading one million identical rows into Postgres 17 seven times, changing nothing but the key, moved the index by 3.5x and the write ahead log by 2x, and the scheme most often recommended as the fix turned out to depend on a number nobody measures: how many ids you generate inside a single millisecond.
How this was checkedLoaded the identical million row payload into Postgres 17.11 seven times, changing only the primary key, and measured COPY time, heap size, index size, WAL bytes from the pg_lsn delta, and B-tree leaf density from pgstatindex. Then generated UUID v7 at three different rates to find where its ordering stops working. At ten ids per millisecond v7 indexes exactly like a counter at 90 percent density. At a thousand per millisecond it drops to 67 percent and its index comes out larger than random v4's.
What I compared on
- WAL bytes written during the load, because that is what replicas and backups carry
- primary key index size after the load
- B-tree leaf density, which is the cause the other two are effects of
A counter in the database, eight bytes, strictly increasing, the default nobody argues for.
Wins
It is the cheapest key on every axis at once. The 90.1 percent density is the fillfactor doing exactly what it was designed to do, because an append only key never splits a page in the middle.
Loses
The value is guessable and it leaks volume: anyone holding id 4,100,322 knows roughly how many rows you have. It also has to come from the database, which rules it out when ids are assigned before the insert.
21 MB index96 MB WAL90.1 percent leaf density
Timestamp in the high bits, machine id and a per millisecond counter in the low bits, still eight bytes.
Wins
It is the only scheme here that gets counter class numbers without the database assigning the value. Eight bytes, sortable by time, and no coordination between writers beyond a machine id.
Loses
You have to operate it: a clock that moves backwards produces duplicates, and the epoch and bit layout are decisions you are stuck with. The 9.7 percent fragmentation shows the per millisecond counter is not perfectly ordered across writers.
24 MB index98 MB WAL80.8 percent density
Sixteen bytes, millisecond timestamp in the leading bits, the rest random, stored in a native uuid column.
Wins
Below roughly one id per millisecond per writer it is a counter with none of the coordination: same density, same page behaviour, and the value is safe to expose. It is the right default for most services.
Loses
The ordering is only as fine as the timestamp. Generate more ids in a millisecond than fit on a leaf page and the random tail scatters them inside that millisecond's neighbourhood, and you have bought sixteen byte keys for v4 behaviour.
30 MB at 10 ids/ms41 MB at 1000 ids/ms90.0 vs 67.0 percent density
Sixteen random bytes in the native type, the key everyone reaches for and everyone is warned about.
Wins
Nothing about it can leak and nothing has to coordinate. Against the text version of the same value it saves 35 MB of index and 63 MB of WAL per million rows, which makes the column type the easiest win on this list.
Loses
It is the slowest of the binary keys and it never improves: there is no rate, no batch size and no tuning that makes a random key fill a page. The 50 percent fragmentation is permanent.
38 MB index126 MB WAL71.6 percent density
Timestamp plus randomness, rendered as 26 Crockford base32 characters, usually stored as text.
Wins
It sorts lexicographically as text, so an ordinary string index gives you time ordering without a special type, and the 26 character form is case insensitive and safe in a URL.
Loses
Keeping it as text is the whole problem: 26 characters against 16 bytes, and it carries the same millisecond resolution ceiling as v7 without the native type to soften it. Stored as bytea it would be a different entry on this list.
63 MB index162 MB WAL26 characters
Twenty one random URL safe characters, no timestamp, designed to be short rather than sortable.
Wins
It is the shortest value here that is still unguessable, which matters when the id ends up in a URL a human reads aloud. Five characters shorter than a ULID and fifteen shorter than a text uuid.
Loses
No ordering at all, so it has every page splitting cost of v4 plus string comparison on top, and it was the slowest load in the set. There is no version of this key that indexes well.
51 MB index146 MB WAL21 characters
The same random uuid, stored as its 36 character hyphenated string because the ORM defaulted that way.
Wins
It is portable across databases that have no uuid type and it needs no cast anywhere, which is teh entire reason it keeps happening.
Loses
You pay 20 extra bytes per row in the heap and in every index that includes the key, for a value that has one canonical binary form. This is the one entry on the list that is simply a mistake.
73 MB index189 MB WAL36 characters for 16 bytes
Side by side
| COPY | PK index | WAL | Leaf density |
|---|
| bigint identity | 1.65 s | 21 MB | 96 MB | 90.1% |
| Snowflake bigint | 1.65 s | 24 MB | 98 MB | 80.8% |
| UUID v7 (10/ms) | 2.21 s | 30 MB | 113 MB | 90.0% |
| UUID v7 (1000/ms) | 2.76 s | 41 MB | 124 MB | 67.0% |
| UUID v4 (uuid) | 3.54 s | 38 MB | 126 MB | 71.6% |
| ULID (text) | 3.24 s | 63 MB | 162 MB | 67.5% |
| nanoid (text) | 4.06 s | 51 MB | 146 MB | 69.0% |
| UUID v4 (text) | 4.57 s | 81 MB | 189 MB | 69.9% |
Verdict
Use UUID v7 in a native uuid column. Then count your ids per millisecond, because that number is the whole decision. At ten per millisecond it matched a strictly monotonic key exactly, 30 MB of index and 90 percent density, while giving you a value the application generates and can safely expose. At a thousand per millisecond it was worse than v4 on density and 8 percent worse on WAL, so the scheme is only as good as the rate you feed it. If you are already writing fast enough to break that, the honest answer is a Snowflake bigint: half the bytes, 24 MB of index, and a clock problem you can actually see.
Sources