DEV Community

Cover image for UUID vs BIGINT Primary Keys: I Inserted 5 Million Rows to Find Out
Muiduzzaman Lipu for Refactorly

Posted on

UUID vs BIGINT Primary Keys: I Inserted 5 Million Rows to Find Out

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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).
  • BIGINT and 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 BIGINT or 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');
Enter fullscreen mode Exit fullscreen mode
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(),
  ...
);
Enter fullscreen mode Exit fullscreen mode

Node.js, with the uuid package:

import { v7 as uuidv7 } from "uuid";
uuidv7(); // '01a1ef78-96ad-75c1-8dcf-76e6cd9740b2'
Enter fullscreen mode Exit fullscreen mode

Python 3.14+:

import uuid
uuid.uuid7()   # UUID('01a1ef78-8eac-7216-88a6-79032fb412fb')
Enter fullscreen mode Exit fullscreen mode

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, 3 IDs 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)