UUIDv4 or UUIDv7 for a primary key? 5 million rows on PostgreSQL 18
PostgreSQL 18 ships uuidv7() in core. No extension, no application code. So the old question comes back: if you want UUID keys, does the version matter?
I loaded 5 million rows three ways and measured. Short answer: UUIDv7 inserted in 27 seconds, UUIDv4 in 46. The v4 index came out 29% bigger. Random lookups by key cost the same.
Setup
One table per run:
CREATE TABLE t (id <key>, payload text NOT NULL);The key was one of:
bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEYuuid DEFAULT gen_random_uuid() PRIMARY KEY(v4)uuid DEFAULT uuidv7() PRIMARY KEY
Each run inserted 5,000,000 rows in 50 committed batches of 100,000. payload is an md5 string, 32 characters. After loading I ran VACUUM ANALYZE and a checkpoint, then two read queries. Every configuration ran three times on a fresh table. The numbers below are medians. The spread between runs was small, except insert time, which moved by up to 3 seconds.
Machine: a 2020 13-inch MacBook Pro, Intel i5-8257U, 8 GB RAM, internal SSD. PostgreSQL 18.6 built from source, default settings (shared_buffers 128 MB, max_wal_size 1 GB, full_page_writes on).
Results
| bigint | UUIDv7 | UUIDv4 | |
|---|---|---|---|
| Insert 5M rows | 23.2 s | 27.0 s | 46.3 s |
| WAL written | 790 MB | 857 MB | 1,004 MB |
| Table size | 365 MB | 403 MB | 403 MB |
| Primary key index | 107 MB | 150 MB | 193 MB |
| Index leaf density | 90% | 90% | 70% |
| 10,000 random lookups (buffers) | 34,228 | 34,650 | 34,632 |
| Last 1,000 rows by key (buffers) | 16 | 17 | 1,008 |
Buffers are shared hit plus read from the top plan node of EXPLAIN (ANALYZE, BUFFERS).
Where the difference comes from
A UUIDv7 starts with a millisecond timestamp, so each new key is larger than the last one. New index entries land on the rightmost leaf page, the same way a bigint sequence does. When that page fills, PostgreSQL starts a new one and leaves the old one full.
A UUIDv4 is random. Each insert lands somewhere in the middle of the index. Pages split, and after a split both halves are partly empty. That is the 70% leaf density, and it is most of why the v4 index is 43 MB bigger than the v7 one at the same row count.
The WAL number follows from the same thing. With random inserts, more distinct pages get touched between checkpoints, and the first change to each page after a checkpoint writes the whole 8 kB page to WAL. The v4 run wrote 147 MB more WAL than v7 for the same data.
What surprised me
My first run was wrong. I built PostgreSQL without --with-openssl. Without it, PostgreSQL falls back to a slower way of getting random bytes, and generating the UUIDs alone took 68 seconds for 5 million values. That made v4 and v7 look equally slow, and both looked five times slower than bigint. With OpenSSL, generation takes about 5 seconds for either version. Packaged builds (Homebrew, Debian, the Docker image) use OpenSSL, so the second set of numbers is the one that matches what you run. If you build your own, check.
Random lookups did not care. Fetching 10,000 random existing keys touched about 34,000 buffers for all three key types. Each lookup walks the index down to one leaf and then reads one heap page. The key's order does not change that walk much. I expected the bigger v4 index to cost more here, and at this size it didn't.
"Newest rows" is where v4 hurts. ORDER BY id DESC LIMIT 1000 read 17 buffers with v7 and 1,008 with v4. With v4 those are not the newest rows at all. They are 1,000 rows scattered across the table, one heap page each. If your application lists recent records, a v4 key needs a separate timestamp column and index to do it well. A v7 key already sorts by creation time.
What I did not test
- Concurrent inserts from many sessions. This was one session.
- Tables larger than RAM. The whole data set here fit in the OS page cache, so reads from "disk" were mostly memory. The gap on insert time may grow when the index no longer fits.
- Updates, deletes, and foreign keys pointing at the key.
- Replication. More WAL means more to ship, but I did not measure a replica.
- Other hardware. The absolute seconds are laptop seconds. Compare the ratios, not the times.
One thing to know before switching: a UUIDv7 contains the time it was created. Anyone who can see the key can read that timestamp. If your keys are public and creation time is sensitive, that matters more than any number above.
Reproduce it
The script creates the tables, runs the inserts, and writes every raw number to results.json: run.py. It expects a PostgreSQL 18 server and the pgstattuple extension.