Primary key index sizes for bigint, uuidv7 and uuidv4 on 10 million rows, in Pilotbase

PostgreSQL 18 ships a function many teams have been installing extensions or writing PL/pgSQL for: uuidv7(). It returns a UUID that starts with the current time, so values generated one after another sort one after another. The promise is the thing people like about UUIDs (ids you can make anywhere, that don't give away how many rows you have) without the thing they don't (random keys that scatter every insert across the whole index).

We wanted numbers before telling anyone to change their primary keys, so we built three tables that differ only in the key, bigint, uuidv4() and uuidv7(), put 10 million rows in each, and measured the index, the write-ahead log and the time. The short answer: if your keys are random UUIDs today, switch the default to uuidv7(); it costs one statement and the difference is large. If they're bigint, the case for switching is weaker than the internet suggests.

What uuidv7 is

A version 7 UUID, defined in RFC 9562 (2024), is 128 bits like any other: the first 48 are a Unix timestamp in milliseconds, then the version, then random bits. PostgreSQL's implementation uses the first 12 of those random bits for a finer fraction of the millisecond, which the RFC allows (its "Method 3"), and that makes the values strictly increasing within one database session even when you generate many in the same millisecond or the clock steps back. Across sessions they're ordered by the millisecond, and random within it.

sql
SELECT uuidv7();
--  01a12ea5-881c-7e83-bef4-f3528b5e8613
--  ^^^^^^^^ ^^^^ timestamp (48 bits)   ^ version 7

SELECT uuid_extract_timestamp('01a12ea5-881c-7e83-bef4-f3528b5e8613');
--  2026-10-12 05:46:02.652+00

SELECT uuid_extract_version(uuidv7());   -- 7
SELECT uuidv4();                         -- new name for gen_random_uuid()

We generated a million in a row in one session and compared each with the next: none was out of order. uuidv7() also takes an optional interval that shifts the timestamp, uuidv7(interval '-3 years'), which turns out to be the useful part when you migrate an existing table (below).

The test

sql
CREATE TABLE t_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL,
                       created_at timestamptz NOT NULL DEFAULT now(), payload text NOT NULL);
CREATE TABLE t_v4     (id uuid PRIMARY KEY DEFAULT uuidv4(), ...same columns...);
CREATE TABLE t_v7     (id uuid PRIMARY KEY DEFAULT uuidv7(), ...same columns...);

-- 10,000 times, from 4 connections at once:
INSERT INTO t_v7 (customer_id, payload)
SELECT (random()*100000)::bigint, repeat('x', 60) FROM generate_series(1, 1000);

Batches of 1,000 rows, four connections, the way an import job or a busy API writes. PostgreSQL 18.6 with default settings (shared_buffers 128 MB, max_wal_size 1 GB) on an Apple M4. We timed every million rows, recorded the WAL written, and ran the whole thing three times.

text
10 million rows        bigint        uuidv7        uuidv4
insert time (3 runs)   7.2-7.8 s     11.1-13.8 s   14.9-23.2 s
WAL written            2.0 GB        2.2 GB        3.1-3.3 GB
WAL per million rows   204-208 MB    221-231 MB    231 MB, rising to 479 MB
primary key index      214 MB        301 MB        387 MB
leaf pages full        90%           90%           70%
table                  1,117 MB      1,202 MB      1,202 MB
Pilotbase query result: t_bigint primary key 214 MB at 90% leaf density, t_v7 302 MB at 90%, t_v4 388 MB at 70% density and 50% fragmentation
The three primary keys after 10 million rows, measured with pgstatindex in Pilotbase.

Why random keys cost more

A B-tree keeps keys in order. With bigint and uuidv7, each new key is larger than the last one, so inserts go to the rightmost leaf page. When that page fills, Postgres splits it and leaves the old page 90% full (the B-tree default fillfactor), because nothing will ever be inserted there again. That is the 90% in the table.

With uuidv4, each key lands on a random page anywhere in the index. When a page fills it splits in half, and both halves will get more inserts later. Over millions of rows the pages settle around 70% full, which is close to the textbook figure for random insertion. So the same 10 million keys need 387 MB instead of 301 MB, and pgstatindex reports half the leaf pages as out of physical order.

Size is the smaller problem. The larger one is how many pages each batch touches. Here is one batch of 10,000 inserts into each table at 10 million rows, with EXPLAIN (ANALYZE, BUFFERS, WAL), three runs each:

text
10,000 rows into   time           pages dirtied   WAL
t_bigint           6.0-6.9 ms     ~170            2.3 MB
t_v7               8.4-9.3 ms     ~193            2.6 MB
t_v4               162-250 ms     ~7,500          37-56 MB
explainViewer comparing the two insert plans: uuidv4 728 ms with 7,344 full-page images and 45.9 MB of WAL, uuidv7 17.8 ms with 2.2 MB of WAL, 41 times faster
The same 10,000-row insert, uuidv4 before and uuidv7 after, in explainViewer (one of the slower uuidv4 runs). Note the WAL line: fpi=7344.

Ten thousand random keys dirtied about 7,500 pages, nearly all of them index leaves. Ten thousand time-ordered keys dirtied under 200, most of them the table's own new pages. And the WAL column shows why that matters even when everything is in memory. The first time a page is changed after a checkpoint, Postgres writes the whole 8 kB page into the WAL (a full-page image) so a crash can't leave it half written. Touch 7,000 pages and you write 7,000 full-page images: 46 MB of WAL for 10,000 rows of about 100 bytes each. That WAL goes to every replica, into every archive and every point-in-time backup, and it fills max_wal_size sooner, which brings the next checkpoint closer, which starts the cycle again.

We checked whether this was only a small cache at work. With shared_buffers raised to 1 GB and the whole index loaded into it, uuidv4 batches still took 150 to 380 ms and wrote 37 to 56 MB of WAL each; uuidv7 stayed at 8 ms and 2.6 MB. A bigger cache spares you the disk reads. It doesn't spare you the full-page images.

You can watch it build up in the per-million numbers: uuidv4's WAL per million rows grew as the index grew, from 231 MB to as much as 479 MB, while uuidv7 and bigint stayed flat. The bigger the table, the worse random keys get. Ours was 10 million rows; plenty of tables are a hundred times that.

Where uuidv7 doesn't beat bigint

A UUID is 16 bytes and a bigint is 8. That shows up everywhere the key is stored: the primary key index was 41% bigger with uuidv7 (301 MB against 214 MB), the table 8% bigger, and every foreign key column and index that points at this table grows the same way. Inserts were slower too, 11 to 14 seconds against 7 to 8 for the same 10 million rows from four connections, and 8 to 9 ms against 6 to 7 ms for a single batch. Generating the value isn't the reason: a million uuidv7() calls took 0.25 s against 0.15 s for a million nextval(). Wider keys account for part of the rest; we didn't pin all of it down. Measure on your own hardware before you count on either figure.

What you get for that: ids that any application server, phone or offline client can create without asking the database, that don't collide when you merge two databases, and that don't tell a visitor you have 48,213 orders. If you need none of those, bigint GENERATED ALWAYS AS IDENTITY is still the smallest and fastest key Postgres has.

What uuidv7 gives away

The timestamp is readable by anyone who sees the id. uuid_extract_timestamp() returns the moment the row was created, to the millisecond, so a uuidv7 in a public URL tells the world when that account signed up or that order was placed, and a handful of them shows your rate of sign-ups. RFC 9562 is blunt about the general case: UUIDs "MUST NOT be used as security capabilities", and where one is needed for a security purpose, uuidv4 is the version it recommends. Use a separate random token for password resets, invite links and anything else that grants access, whatever your primary key is.

One small surprise on the way: min() and max() don't accept a uuid in PostgreSQL 18, so the newest row is ORDER BY id DESC LIMIT 1. With uuidv7 that query reads five buffers and takes 0.01 ms on 10 million rows, because the newest keys are all on the rightmost leaf. With uuidv4, "newest by id" means nothing; you need an index on created_at.

Moving an existing table

Your primary key is a random uuid today. This is the easy case, and the one that pays off most:

sql
ALTER TABLE t_v4 ALTER COLUMN id SET DEFAULT uuidv7();

Nothing is rewritten and nothing is locked for more than a moment. The column type is the same, so foreign keys don't change and old ids keep working. We ran it on the 10 million row uuidv4 table, and the very next batch of 10,000 inserts took 10 ms and wrote 2.7 MB of WAL, down from 150 to 380 ms and 37 to 56 MB on the same server a few minutes earlier. New keys all sort into one narrow band of the old index (64,301 of the 10 million old keys sort before it, about 0.6% of the way in), so inserts hit the same few pages again and again, just as on a fresh uuidv7 table. The old 70%-full pages stay as they are; a REINDEX CONCURRENTLY during a quiet hour packs them back to 90% if the size bothers you.

If ids are generated in the application rather than by a column default, change it there. Python 3.14 has uuid.uuid7() in the standard library, the uuid package on npm has v7(), and most languages have a maintained library. On PostgreSQL 17 and older, generating in the application (or the pg_uuidv7 extension) is the way to get the same keys before you upgrade.

Your primary key is a bigint and you want public ids. Don't change the primary key. Every foreign key in every table that points at it would have to change too, for a key that is bigger and slower. Add a second column for the outside world, and fill it so the ids still sort by creation time:

sql
ALTER TABLE accounts ADD COLUMN public_id uuid;

-- in batches of, say, 50,000 ids on a big table; this is one statement for clarity
UPDATE accounts SET public_id = uuidv7(created_at - clock_timestamp());

ALTER TABLE accounts ALTER COLUMN public_id SET DEFAULT uuidv7();
CREATE UNIQUE INDEX CONCURRENTLY accounts_public_id_key ON accounts (public_id);
ALTER TABLE accounts ALTER COLUMN public_id SET NOT NULL;

The shift argument makes each backfilled id carry the row's own created_at instead of today's date. On a million rows spread over nearly three years the update took 2.2 seconds, uuid_extract_timestamp(public_id) matched created_at to the millisecond on every row, and the ids came out in the same order as the rows were created. The unique index built in 0.23 s. The last statement scans the table under an exclusive lock, which took 38 ms here but takes longer on a big table; adding a CHECK (public_id IS NOT NULL) NOT VALID constraint first and validating it separately avoids the long lock.

A backfill like this rewrites every row once, so it's also a burst of WAL and dead tuples. Batches with short transactions let autovacuum keep up and keep replicas close behind. If the table also carries indexes nobody uses, drop those first: every one of them gets a new entry for every updated row.

So, should you switch?

If your keys are uuidv4 or gen_random_uuid(): yes. Change the default to uuidv7() today. On our 10 million row table it cut the time of each insert batch by about 20 times and the WAL by about 15, with one statement and no downtime. Keep uuidv4 for tokens that must reveal nothing.

If your keys are bigint: only if you need what a UUID gives you, ids made outside the database, merges without collisions, or ids that don't reveal your row count. For public ids on an existing table, add a uuidv7 column next to the bigint rather than replacing it.

If you're starting a new table: bigint identity for internal tables, uuidv7() where ids leave the database. There's no longer a good reason to choose uuidv4 as a primary key. (Moving an app's first database to Postgres? SQLite to Postgres when the side project grows up covers the key and type changes that come with it.)

Run the index size query from this article against your own tables in Pilotbase and see how full your primary key pages are before you decide. Desktop for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Sources: PostgreSQL 18 documentation, UUID Functions (uuidv7() and its shift argument, uuid_extract_timestamp, uuid_extract_version), CREATE INDEX (B-tree fillfactor 90), WAL configuration (checkpoints and max_wal_size), pgstattuple (pgstatindex); PostgreSQL 18 release notes; RFC 9562 (UUIDv7 layout, Method 3, security considerations); Python uuid module (uuid7(), new in 3.14). Every number, plan and screenshot is from our own runs: PostgreSQL 18.6 on an Apple M4 with default settings unless stated, 10 million rows per table inserted by pgbench in 1,000-row batches from 4 connections, three runs; plans shown in explainViewer, sizes queried in Pilotbase.