Choosing UUIDs for primary keys is usually argued on the merits of the identifier itself. They can be generated client side, they do not leak row counts, and they let you merge data from separate systems without collisions. All true, and all reasons I still reach for them.
What rarely comes up in that conversation is what a random identifier does to the index underneath it. A UUIDv4 is 122 bits of randomness, which means every insert lands in an unpredictable place in the index. That single property is enough to turn one logical write into several physical ones, and the cost grows with the size of the table rather than staying constant.
This post is about the mechanism, because once you can see it you can decide for yourself whether it applies to your workload. A lot of writing on this subject jumps straight to a benchmark result. Benchmarks are hardware, and yours is not mine.
#A B-tree stores keys in order, and that is the whole problem
A primary key in PostgreSQL is backed by a B-tree index. The important property is that the index is kept sorted. Keys live in fixed-size pages, and a page holds a contiguous range of the key space.
When you insert a row, the index has to place its key in the correct page. If that page is full, the page splits: PostgreSQL allocates a new page and divides the entries between them.
Now consider what each kind of key does to that structure.
A sequential key, such as a bigint from an identity column, always sorts after every key already present. Every insert therefore targets the same page, the rightmost one in the tree. PostgreSQL recognises this case and splits that page asymmetrically, leaving most of the entries behind and starting the new page nearly empty so the next run of ascending inserts has somewhere to go. One page stays hot. The rest of the index is never touched.
A random key sorts anywhere. Each insert targets a different page, chosen essentially at random from the whole index. A split in the middle of the tree has no ascending pattern to exploit, so it divides the page roughly evenly, and both halves are left partially empty.
That difference produces three separate costs, and they compound.
#Cost one: the index stops fitting in memory
PostgreSQL reads and writes pages through a shared buffer cache. Sequential inserts touch one page repeatedly, so that page is essentially always resident and the write is absorbed in memory.
Random inserts touch a different page every time. Once the index is larger than the cache, most inserts require reading a page from disk before they can modify it. This is the cost people notice, and it is also the one that appears suddenly: the table performs fine until the index outgrows memory, and then it does not.
Note the shape of that. It is not a gradual slope. It is a cliff you walk off at a size you did not predict.
#Cost two: pages are left half empty
Even splits leave both pages around half full. Because subsequent random inserts are spread across the whole key space, those gaps fill slowly and unevenly.
The index therefore occupies more pages than the data strictly requires, which makes it larger, which makes it less likely to fit in cache, which brings you back to cost one. A random-keyed index and a sequential one holding identical data will not be the same size.
#Cost three: write-ahead log amplification
This is the cost that is almost never mentioned, and it can be the largest.
To protect against partial page writes during a crash, PostgreSQL writes the entire contents of a page to the write-ahead log the first time that page is modified after a checkpoint. Subsequent modifications to the same page within the same checkpoint interval only log the change.
Sequential inserts modify one page repeatedly, so you pay the full-page write once and then log small records.
Random inserts modify a different page each time. Every one of those is a first touch since the checkpoint, so a large fraction of them pay the full-page cost. You are writing pages to the WAL to record the insertion of a single row.
That WAL is also what replication streams and what your backups archive, so the amplification does not stay local. It shows up in replica lag and in storage bills.
#What a time-ordered key changes
UUIDv7, specified in RFC 9562, keeps the shape and the uniqueness guarantees of a UUID but puts a timestamp at the front. The leading 48 bits are Unix time in milliseconds, followed by the version, then random bits.
Because the timestamp is the most significant part, UUIDv7 values generated over time sort in roughly the order they were created. Inserts append to the right of the index instead of scattering, which recovers the sequential behaviour described above while keeping every property that made you want a UUID.
PostgreSQL 18 ships uuidv7() as a built-in, along with uuidv4() as an explicit alias and two inspection helpers:
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
customer_id uuid NOT NULL,
total_cents bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
-- The timestamp is recoverable from the key itself.
SELECT uuid_extract_timestamp(id) AS issued_at,
uuid_extract_version(id) AS version
FROM orders
LIMIT 5;The PostgreSQL implementation also fills the 12 bits immediately after the timestamp with a sub-millisecond fraction, which the specification permits but does not require. The practical effect is that values generated within the same session stay monotonic even when several are created inside the same millisecond, rather than falling back to random ordering within that window.
If you are on an earlier version, generate the value in the application. Most languages have a UUIDv7 implementation now, and the column type does not change.
#Measure it on your data rather than trusting mine
The honest answer to "how much does this cost" is that it depends on your row width, index count, insert rate, checkpoint configuration and how much memory you have. Here is how to find out rather than guess.
Compare physical size for the same logical content:
SELECT pg_size_pretty(pg_relation_size('orders_v4_pkey')) AS v4_index,
pg_size_pretty(pg_relation_size('orders_v7_pkey')) AS v7_index;Measure WAL generated by a batch of inserts, which is where the amplification hides:
SELECT pg_current_wal_lsn() AS before \gset
-- run your insert workload here
SELECT pg_size_pretty(
pg_wal_lsn_diff(pg_current_wal_lsn(), :'before'::pg_lsn)
) AS wal_written;And check whether you are actually reading from disk, which tells you if you have crossed the cache threshold at all:
SELECT indexrelname,
idx_blks_read, -- pages fetched from disk
idx_blks_hit -- pages served from the buffer cache
FROM pg_statio_user_indexes
WHERE relname = 'orders';If idx_blks_read stays near zero, your index fits in cache and none of this is currently costing you anything.
#When this genuinely does not matter
I would rather you skip the migration than perform it as a ritual.
A random key is fine when the index comfortably fits in the buffer cache, and it will keep fitting. Small reference tables, configuration, anything bounded.
It is fine when your write rate is low. Amplifying a handful of inserts per second by a factor of several is still a handful of inserts per second.
It is fine when the table is overwhelmingly read. The costs described here are all on the write path.
The workloads where it bites are high insert rate against a large and growing table: events, logs, orders, messages, telemetry. That is also, unhelpfully, exactly the category where people reach for UUIDs to allow client-side generation.
#What a time-ordered key costs you
Being fair to the alternative matters, so here is what you give up.
It reveals creation time. Anyone holding one of your identifiers can extract the millisecond it was generated. If your identifiers are exposed publicly, you have published a timeline. Sometimes that is harmless. If your row identifiers are visible to customers and the creation ordering is commercially meaningful, it is not.
Random bits are fewer. UUIDv7 spends 48 bits on the timestamp, so a smaller portion is random compared to UUIDv4. Collision probability is still negligible for any realistic workload, but if you were relying on the identifier being genuinely unguessable, note that guessing is now constrained to a narrower space per millisecond.
That last point deserves to be said plainly: an unguessable primary key was never authorization. If knowing an identifier is sufficient to access a record, that is the bug, and it was a bug before you changed the key format. Fix the access check, then pick the key on its performance merits.
#Migrating without rewriting a live table
Changing a primary key type on a large table in place means rewriting the table and every index that references it, holding a lock the whole time. Avoid that if you can.
The sequence I would use:
Start with new tables. Anything created from today onward gets uuidv7() as its default. This costs nothing and stops the problem growing.
For an existing table that genuinely needs it, do not convert the type. Add a new column with the new default, backfill it in batches so no single transaction holds a long-running lock, build the replacement index concurrently, and only then swap the constraint. Foreign keys pointing at the old column have to move in the same coordinated step, which is the part that makes this genuinely expensive.
Given that cost, be honest about whether the table is in the category that suffers. Most are not.
#A note on the alternative you already have
If you do not need client-side generation or cross-system merging, a bigint identity column remains the cheapest primary key available. It is eight bytes rather than sixteen, it is naturally ordered, and every foreign key referencing it is also half the size, which compounds across a schema.
UUIDs solve real problems. They are just not free, and the bill arrives on the write path rather than the read path, which is why it takes so long to notice.
#References
- RFC 9562: Universally Unique IDentifiers, which specifies UUID versions 6, 7 and 8
- PostgreSQL 18 release announcement
- PostgreSQL documentation: UUID functions