UUID or bigint as the primary key: the cost on B-trees, WAL, and foreign keys Intermediate

Problem

The orders table has 10 million rows with primary key id bigint. An order URL is /orders/10000231: anyone placing two orders a week apart can estimate how many orders were sold. The mobile app also wants to generate ids itself while offline.

The team proposes switching to UUIDs. order_items, payments, and shipments all have foreign keys pointing to orders.id, so they must change too.

The question: if we switch to UUIDs, what do the B-tree indexes, WAL, and foreign keys pay, and which kind of UUID costs least?

What to choose (summary first)

Situation	Choose	Condition
The id is used internally only	bigint GENERATED ALWAYS AS IDENTITY	The id is never exposed
You do not want to leak counts via URL/API	bigint PK + a public_id uuid UNIQUE column	Foreign keys stay bigint
Clients or multiple services generate ids	UUID v7 as the PK	Accept leaking the creation time
Neither order nor creation time may leak	UUID v4	Accept larger indexes and WAL
PostgreSQL 17 needs v7	Generate it in the application or an extension	uuidv7() exists only from PostgreSQL 18

Core concepts

	bigint identity	UUID v4	UUID v7
Key size	8 bytes	16 bytes	16 bytes
Order on insert	Increasing (may have gaps)	Random	Nearly increasing by ms
What leaks	Order, count can be estimated	No order	Creation time

• UUID v4: 122 random bits. Two ids generated back to back land anywhere in the index tree.
• UUID v7 (RFC 9562): a 48-bit Unix timestamp (ms) at the front, followed by version and random bits. An id generated later is usually larger than one generated earlier.
• Full-page image (FPI): with full_page_writes = on (the default), the first modification of a page after a checkpoint writes the whole page into WAL.

The key size is repeated in the primary key index, in each order_id foreign key column, and in the index on that column.

How it works

Why does v4 cost so much more than v7 at the same 16 bytes? The answer lies in which pages get touched on insert.

Increasing keys touch only the rightmost leaf; UUID v4 scatters inserts across leaves, one FPI per page after a checkpoint

Enlarge diagram

Leaf 1 · Leaf 2 · Leaf 3 · Rightmost leaf

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

After a CHECKPOINT: the first modification of each page writes a full-page image (FPI) to WAL.

The state after each step:

1. After a checkpoint: no leaf has an FPI in the new cycle yet.
2. Increasing keys (bigint, v7): the new key is always the largest and lands in the rightmost leaf. One FPI, and later inserts reuse the page that is hot in cache.
3. The rightmost leaf fills up: per the CREATE INDEX docs, leaves are filled to fillfactor 90% when the index grows to the right, not split in half.
4. UUID v4: each insert touches an arbitrary leaf, which must be in shared buffers and may produce another 8 kB FPI.
5. A leaf in the middle of the tree fills up: a page split divides the keys into two pages with plenty of free space; the index bloats and adjacent leaves are no longer next to each other on disk.

So insert order matters more than key size: v7 pays the same 16 bytes as v4 but keeps bigint’s insert path.

Operational details

• PostgreSQL 17 only has gen_random_uuid() (v4). PostgreSQL 18 adds uuidv7() and the alias uuidv4().
• PG17’s uuid_extract_timestamp() only reads UUID v1; PG18 reads v7 too.
• Need v7 on PG17: generate it in the application with an RFC 9562 library, or use a third-party extension (check whether your managed service allows installing it). A hand-written SQL function like the demo works but is slow.
• GENERATED ALWAYS AS IDENTITY (SQL standard) rejects user-supplied values unless OVERRIDING SYSTEM VALUE is given. serial is just a sequence plus a DEFAULT, so manually inserting an id slips through.
• Sequences have gaps: rollbacks and INSERT ... ON CONFLICT still consume nextval (see upsert).
• More WAL means replicas and backups receive more too.
• Node.js pg returns bigint as a string (see data type mistakes).

Real-world example

10 million orders, plus 500,000 orders after a checkpoint

Three tables differing only in the primary key. Load 10 million rows into each in batches of 50,000, then CHECKPOINT and insert another 500,000 rows: simulating a large orders table receiving new orders between two checkpoints.

The v7 function in the demo is a hand-written SQL function for PG17, not a built-in:

CREATE FUNCTION uuid_v7_demo() RETURNS uuid LANGUAGE sql VOLATILE AS $$
  SELECT encode(
           substring(int8send(floor(t.ms)::bigint) FROM 3)                         -- unix_ts_ms
        || int2send((x'7000'::int | floor((t.ms - floor(t.ms)) * 4096)::int)::smallint) -- ver + rand_a
        || substring(uuid_send(gen_random_uuid()) FROM 9),                           -- var + rand_b
         'hex')::uuid
  FROM (SELECT extract(epoch FROM clock_timestamp()) * 1000 AS ms) AS t
$$;

rand_a holds the sub-millisecond fraction, a method RFC 9562 allows (section 6.2). Two ids within the same microsecond can still be out of order.

docker compose -f examples/docker-compose.yml exec -T postgres \
  psql -U hub -d hub -q -v rows=10000000 -v extra=500000 \
  < examples/src/uuid-vs-bigint-keys/uuid-keys-demo.sql

One run on PostgreSQL 17.11 (Docker on an Apple M5, 10 CPUs / 8 GB RAM, shared_buffers = 128MB, max_wal_size = 1GB, wal_compression = off):

Measurement	bigint	UUID v4	UUID v7
Load 10 million rows	8.1 s · 1504 MB WAL	40.1 s · 2471 MB WAL	38.0 s · 1644 MB WAL
500,000 rows after CHECKPOINT	0.7 s · 75 MB WAL	3.4 s · 364 MB WAL	1.5 s · 82 MB WAL
FPIs after checkpoint (B-tree FPIs)	7 (10 kB)	49,230 (272 MB)	4 (11 kB)
PK index blocks read outside shared buffers	4	346,791	3
Heap / PK index	603 MB / 225 MB	684 MB / 410 MB	684 MB / 316 MB
avg_leaf_density / leaf_fragmentation	90.09 / 0	69.55 / 49.73	90.04 / 0

Reading the results:

• WAL: v4 writes nearly 5 times as much as bigint; 272 MB of it is B-tree FPIs. v7 is close to bigint.
• Cache misses: v4 reads 346,791 index blocks from outside shared buffers (they may still hit the OS cache).
• Size: the v7 index is about 40% larger than bigint because of the 16-byte key; v4 adds another 30% because leaves are only about 70% full.

Generating 10 million ids alone took 23.8 seconds with the SQL v7 function and 4.5 seconds with gen_random_uuid(), so v7’s load time is mostly the cost of the demo function.

Measurements vary between runs (a previous run: v4 loaded in 33 seconds). FPI counts come from pg_stat_wal and are cluster-wide. This is a dev machine, not a benchmark.

Trade-offs

Pros

• bigint: 8-byte key, inserts to the right, little WAL, easy to read and debug.

• UUID v7: can be generated anywhere, with an insert path nearly like bigint.

• UUID v4: leaks neither order nor time.

• A separate public_id column: hides counts while foreign keys stay 8 bytes.

< slot="cons">

• bigint: leaks counts when exposed; the database must assign ids.

• UUID: every foreign key and index on it doubles the key size.

• UUID v4: page splits, cache misses, and WAL surge once the table outgrows memory.

• UUID v7: leaks creation time; on PG17 it must be generated outside the database.

Cons

Decision tree for orders:

Loading diagram…

Production checklist

1. Decide whether the id leaves the system (URL, API, email); if so, choose a separate public_id or a UUID PK.
2. New tables use bigint GENERATED ALWAYS AS IDENTITY, not integer or serial.
3. If choosing UUIDs, prefer v7; on PG17, document where ids are generated (application or extension).
4. Estimate the cost: the number of foreign key columns and indexes pointing at the table, times 8 bytes per row.
5. Client-supplied ids: validate them, treat 23505 as a repeated request, and do not trust the timestamp inside the id.
6. Track avg_leaf_density, WAL per hour, and index size.
7. Changing the key type on a live table: follow expand–contract.

Common mistakes

The first mistake is changing the whole primary key just to hide counts:

Switching the PK to UUID v4 just so URLs do not leak counts

✕ Common

CREATE TABLE orders (id uuid PRIMARY KEY DEFAULT gen_random_uuid() /* ... */);
CREATE TABLE order_items (order_id uuid NOT NULL REFERENCES orders /* ... */); -- every FK is 16 bytes

✓ Prefer

ALTER TABLE orders ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE;

Why

Only one UUID index is added; foreign keys stay bigint. For a large table, backfill in batches instead of one statement.

The second mistake concerns privacy:

Treating UUID v7 as an unguessable id

The first 48 bits are the creation time, readable by anyone holding the id. RFC 9562 advises against using UUIDs as security capabilities; an id does not replace access checks.

Misconception	Consequence	What to do
“Every UUID is the same 16 bytes”	v4 makes indexes and WAL much larger	Prefer v7
bigint ids are contiguous, so use them as invoice numbers	Gaps from rollbacks and ON CONFLICT	Number invoices separately
The timestamp in a client-generated v7 can be trusted	Client clocks are wrong, sorting goes wrong	Store created_at separately

Self-check

1. Why do 500,000 inserts into the v4 table produce about 49,000 FPIs while the bigint table produces only a few?
2. The v7 and v4 indexes have the same 16-byte key. What makes the v4 index 30% larger?
3. orders has 4 tables pointing foreign keys at it, each with 30 million rows. How many extra key bytes does switching to UUIDs add, before counting indexes?

Summary

Key takeaways

• The big cost of UUID v4 comes from random order: page splits, cache misses, FPIs after checkpoints.
• UUID v7 keeps an insert path close to bigint but leaks creation time; the built-in uuidv7() exists only from PostgreSQL 18.
• Every byte of the primary key is repeated in every foreign key and index pointing at it.
• To hide counts while keeping bigint: add a separate public_id; use GENERATED ALWAYS AS IDENTITY instead of serial.

References

• PostgreSQL 17 — UUID Functions: PG17 only has v4.
• PostgreSQL 18 — UUID Functions: uuidv7(), uuidv4().
• CREATE INDEX, fillfactor: leaf filling when growing to the right, splits causing fragmentation.
• full_page_writes: FPIs after a checkpoint.
• Identity Columns: ALWAYS and BY DEFAULT.
• pgstattuple: the columns of pgstatindex().
• RFC 9562: UUID v7 layout (5.7), ordering within the same ms (6.2), security (8).
• B-tree index, Storage layout: leaf pages and 8 kB pages.