Should your primary key be a number or a UUID? Developers argue about this constantly, so I measured it: 5 million rows in PostgreSQL with three kinds of keys.
One of them made the index almost twice as big and wrote seven times more pages. Here's which one, and why.
Video version:
The three contenders
| Key | Example | Size |
|---|---|---|
BIGINT |
1, 2, 3, 4 … |
8 bytes |
| UUID v4 | 2cfaa8c2-a484-42d2-84f6-1706ab865fad |
16 bytes, fully random |
| UUID v7 | 01a1ef62-ec01-707d-88df-a4aca2b3f289 |
16 bytes, timestamp first |
Three UUID v7 values generated one millisecond apart:
01a1ef62-ec01-707d-88df-a4aca2b3f289
01a1ef62-ec02-7022-ad2a-76b09969824a
01a1ef62-ec03-7c6d-acf5-5f35dcc2bed3
The first 48 bits are a Unix timestamp in milliseconds, so new v7 IDs are always (roughly) larger than older ones. The rest is random, so they're still unique and unguessable.
The test
-
PostgreSQL 17.7 on an Apple M4, default settings (
shared_buffers = 128MB), on a throwaway local cluster. - I pre-generated 5,000,000 keys of each kind in a source table, with v7 timestamps 1 ms apart, to mimic a stream of new rows.
- Then, for each key type, I created the same table and inserted all 5M rows in creation order, like an app would:
CREATE TABLE t (
id uuid PRIMARY KEY, -- or bigint
created_at timestamptz DEFAULT now(),
note text
);
CHECKPOINT;
EXPLAIN (ANALYZE, BUFFERS, WAL)
INSERT INTO t (id, note)
SELECT id, 'order' FROM keys ORDER BY n;
I ran 5 rounds, interleaving the three key types.
The results
| Key | PK index size | Pages written | WAL generated | Insert time (median of 5) |
|---|---|---|---|---|
BIGINT |
107 MB | ~65,000 | 715 MB | 35.6 s |
| UUID v7 | 150 MB | ~85,000 | 801 MB | 33.1 s |
| UUID v4 | 192 MB | ~460,000 | 961 MB | 53.1 s |
- UUID v4 was the slowest in every round (35 to 83 s, versus 18 to 40 s for the others).
-
BIGINTand UUID v7 were close; their order flipped between rounds. - The v4 index is 1.8× the size of
BIGINT's, and 28% larger than v7's, even though v4 and v7 keys are the same size.
A note on the timings: my laptop had other heavy processes running, so the absolute times are noisy. Index sizes, pages written and WAL bytes are deterministic, and they were nearly identical in every round.
Why random UUIDs hurt
A primary key is a B-tree index, and a B-tree keeps keys sorted.
- With
BIGINTor UUID v7, every new key is the largest so far, so it goes on the last page of the index. The same few pages stay hot in memory, and full pages are left full. - With UUID v4, every new key belongs at a random position. Each insert touches a different page somewhere in the index, which is far more reads and writes, and when that page is full it splits in half.
pgstatindex() (from the pgstattuple extension) shows the result:
SELECT avg_leaf_density, leaf_pages FROM pgstatindex('t_uuid4_pkey');
| Key | Leaf pages | Avg leaf density |
|---|---|---|
BIGINT |
13,662 | 90% |
| UUID v7 | 19,157 | 90% |
| UUID v4 | 24,517 | 70% |
Random inserts leave pages ~30% empty. More pages means a bigger index, more memory needed to cache it, and more I/O. That cost grows as the index outgrows RAM.
How to use UUID v7
PostgreSQL 18 and later have it built in:
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
...
);
Node.js, with the uuid package:
import { v7 as uuidv7 } from "uuid";
uuidv7(); // '01a1ef78-96ad-75c1-8dcf-76e6cd9740b2'
Python 3.14+:
import uuid
uuid.uuid7() # UUID('01a1ef78-8eac-7216-88a6-79032fb412fb')
On PostgreSQL 17 or older, generate v7 in your app, or use an extension such as pg_uuidv7.
Which should you use?
- ✅
BIGINT(identity): smallest and simplest. A great default. - ✅ UUID v7: when IDs are created in many places (clients, services, offline), when you merge data between databases, or when you don't want guessable
1, 2, 3IDs in URLs. - ❌ UUID v4 as the primary key of big, write-heavy tables. It's fine for tokens, small tables and anything that isn't indexed in insert order.
- ⚠️ UUID v7 contains the creation time. Don't use it where the timestamp is sensitive, and never as a secret token.
Team BIGINT or team UUID? 👇
I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.
Top comments (0)