The argument has been running since about 2012. One side says integers are the correct primary key, they are small, sequential, and the B-tree loves them. The other side says UUIDs are the correct primary key, they can be generated anywhere without a round trip to the database, they do not leak how many customers you have, and merging data from two systems never collides. Both sides were right, and the reason the argument never ended is that each was describing a real cost the other was paying.

PostgreSQL 18 ships uuidv7() in core. It is a UUID whose first 48 bits are a millisecond timestamp and whose remaining bits are random. That one change removes the cost the integer side was pointing at, and I think it ends the argument. Here is why, with numbers.

What was actually wrong with UUIDv4#

Nothing about the size. Sixteen bytes against eight is a real difference but it is not the one that hurts.

What hurts is randomness. A B-tree index on a random key is written to in random places. Every insert lands on a page chosen by the roll of a dice, so over time every page of the index is a little bit dirty, every page is a candidate for splitting, and the working set of the index is the whole index. On a table where the recent rows are the hot rows, which is almost every table, that means the index for a 200 million row table needs the whole 6 GB in memory to insert fast, when with a sequential key it would need the last few hundred pages.

I measured this properly on a events table with about 200 million rows in it, on the same instance, same schema, one column type swapped. The workload was 50,000 inserts a second in batches of 500 with a concurrent read load on recent rows.

The size is the same between v4 and v7, they are both 16 bytes. Everything else is different. The v7 index gets written to at the right hand edge, like the integer, because the timestamp prefix sorts new keys after old ones. The splits go away, the hot pages stay hot, and the cache hit rate comes back.

The 4 percent gap left between v7 and bigint is the size difference plus the random tail within a millisecond. That gap I can live with. The 38 percent gap with v4 I could not, and it was the reason the table had been an integer key with a separate public_id uuid column for years, with an extra index to look rows up by it.

The other thing v7 gives you for free#

The timestamp is in the key. You can get it back:

SELECT uuid_extract_timestamp('019627f3-9a5c-7c3b-8e8a-2b0c4a4d9f11');
-- 2026-04-11 14:22:07.836+00

Which means a query like "events in the last hour" can use the primary key index, and a range scan on the primary key is also a time range scan. On the events table that let me drop a separate index on created_at that existed purely for range queries. That index was 3.8 GB.

There is a gotcha here that is worth being precise about. The timestamp in a v7 UUID is the time the key was generated, not the time the row was committed, and not the time the event happened. If the key is generated in the application and the insert is retried twenty seconds later, or if it is generated on a machine whose clock is a few hundred milliseconds off, the key's timestamp and the row's created_at disagree. For sorting and for coarse range queries that does not matter. For anything that has to be exact, keep created_at as a column and treat the key's timestamp as an approximation. I kept the column. I dropped the index on it.

Generating it in the right place#

The whole point of UUIDs was that you could generate them outside the database. uuidv7() in Postgres does not take that away, it just gives you a server side option. Both work and the choice is about where you want the clock.

CREATE TABLE events (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  kind        text NOT NULL,
  payload     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

import { v7 as uuidv7 } from "uuid";
 
// Generated in the app: the id exists before the insert, so it can be
// used in logs, in the outbox message, and in the retry key.
const event = { id: uuidv7(), kind: "order.created", payload };
await sql`INSERT INTO events ${sql(event)}`;

import uuid  # 3.14 added uuid7()
 
event_id = uuid.uuid7()
cur.execute(
    "INSERT INTO events (id, kind, payload) VALUES (%s, %s, %s)",
    (event_id, "order.created", Json(payload)),
)

If the key is generated in the application, make sure every generator in the fleet uses the same algorithm. RFC 9562 fixes the layout, and the mainstream libraries follow it, but a couple of older "ulid as uuid" shims put the timestamp in a slightly different place and those keys will interleave badly with real v7 keys in the same index.

One detail that matters when the application generates keys: within the same millisecond, the random bits decide the order. Postgres's implementation also uses some of those bits as a sub-millisecond counter so that two keys generated back to back on the same server are monotonic. Most client libraries do the same. It means keys from one process are strictly increasing, which is what the B-tree wants, and keys from different processes in the same millisecond are in arbitrary order, which is fine.

Migrating a table that has v4 keys#

You cannot change the keys of existing rows without changing every foreign key that points at them, and you should not try. What you do is stop the bleeding: new rows get v7 keys, old rows keep theirs.

ALTER TABLE events ALTER COLUMN id SET DEFAULT uuidv7();

That is the whole migration if the database generates the keys. If the application generates them, it is a dependency bump and a one line change in the model.

The index does not get better immediately, because the old random keys are still spread across it. It gets better from the right hand side outwards: every new page is a v7 page, tightly packed, and the old pages stop being written to and settle down. After a REINDEX CONCURRENTLY, which you can afford once the write pattern has calmed down, the old part of the index is packed too and only the historical randomness in the key order remains, which costs nothing for a page that never changes.

On the events table I did the default change on a Tuesday, watched the split rate fall over the following two days as the hot region of the index became all v7, and ran the reindex the next weekend. The table has been on the new scheme since April. The public_id column and its index are gone, the integer key is gone, and the primary key is the only key.

When integers are still right#

Small lookup tables. A countries table with 249 rows does not need a 16 byte key and a smallint is the honest choice.

Tables that are only ever written by one process in one place, where the argument for UUIDs never applied. The integer is smaller and there is no coordination to avoid.

And systems where the key is exposed and its length matters, in URLs or on printed documents. A v7 UUID is 36 characters in its usual form. If that is too long for the surface, the answer is a separate short public identifier, not a different primary key.

How the numbers were produced#

The table earlier is only useful if you can reproduce it, so here is the setup.

Three copies of the events table on the same PostgreSQL 18 instance, 8 vCPU, 32 GB, shared_buffers at 8 GB, on local NVMe. Each copy was loaded with the same 200 million rows, differing only in the primary key column: a bigint identity, a uuid filled with gen_random_uuid(), and a uuid filled with uuidv7(). The rows were loaded in time order, which matters, because it means the v7 keys were monotonic at load time the way they would be in production and the v4 keys were random the way they would be in production.

The insert workload was a small Go program with 16 connections inserting batches of 500 rows, as fast as the database would accept them, for 20 minutes per table, with a second program running the read workload: point lookups by primary key on rows inserted in the last minute, 2,000 a second. That read pattern is the "recent rows are hot" shape that most real tables have.

Throughput was measured from the program's own counters. Page splits came from pg_stat_user_indexes before and after, which does not report splits directly but does report the index size growth, and from pgstattuple on the index, which reports average leaf density. A random key index sat at 61 percent leaf density after the run. The v7 and integer indexes sat at 89 and 91. That density difference is the splits, made visible.

The buffer cache hit rate for the index came from pg_statio_user_indexes, the ratio of idx_blks_hit to idx_blks_hit plus idx_blks_read, sampled every minute. The v4 index's rate fell steadily through the run as the working set outgrew the cache. The other two stayed flat.

Nothing in that setup is exotic. The tables, the two programs and the queries fit in a single file each, and the whole run is about an hour. If your table is different in shape, and it is, running the same experiment on a copy of it is the afternoon that tells you what the key type costs you specifically.

Foreign keys pay too#

The primary key is not the only place the key type lives. Every table that references events has a 16 byte column and an index on it, and that index has the same locality problem the primary key index had.

On the schema this table lives in, seven tables reference events. Under v4 keys, the foreign key indexes on those tables were, in total, a bit over 9 GB and had the same low leaf density as the primary key index. Under v7 they are the same size and dense, because a child row inserted now references a parent inserted recently, so the foreign key values are also arriving roughly in order. The join from a child table to events, for a recent time range, went from an index scan that touched pages all over the events index to one that touched a contiguous run of them. On the query that the dashboard runs most, that was a drop from 140 milliseconds to 35.

The size against bigint remains. Sixteen bytes in eight indexes instead of eight bytes in eight indexes is real storage, roughly 4 GB on this schema, and it is the price of a key that can be generated anywhere. I think the price is fair. I would not pretend it is zero.

The timestamp leaks#

One more thing to decide on purpose. A v7 key carries its creation time in plain view. Anyone who sees the key, in a URL, in an API response, in a log, can read the millisecond the row was created. For an order id that is probably fine. For a user id, it tells the world when the account was made, and for some products that is information you would rather not hand out.

The options are the same as they were for integer keys that leaked row counts: expose a separate opaque public identifier and keep the v7 key internal, or accept the leak because the timestamp is not sensitive for that entity. Decide per table. The default I use is that keys that appear in URLs get a separate public id, and everything else uses the v7 key directly.

Where this leaves the argument#

The integer side was right that random keys destroy index locality. The UUID side was right that generating keys without a round trip and without collisions is worth a great deal. v7 gives the UUID side everything it wanted and gives the integer side the locality it was defending. With the function in core as of Postgres 18, uuid.uuid7() in Python 3.14, and v7 in the standard uuid package for JavaScript, there is no longer a setup cost either.

New tables get uuid PRIMARY KEY DEFAULT uuidv7(). Existing v4 tables get the default swapped and a reindex when convenient. The argument is over, and it ended with a function that fits on one line.