Using IDs as Database Keys

A random 128-bit primary key, like a UUID v4, costs you insert throughput, index size and cache locality. Every insert lands at a random point in the B-tree, splitting full pages a sequential key would have skipped, and the same random bytes get copied into every secondary index. A time-ordered key, like UUID v7, gives most of that back by making inserts append instead of scatter.

Generate a UUID v7 and use it as the primary key whenever inserts happen close to real time: the leading 48 bits are a millisecond timestamp, so new rows append instead of landing at random offsets. Use a plain bigint when the table lives on one node forever and the ID never leaves your database. Reach for UUID v4 only when hiding the creation order matters more than insert cost.

UUID v4 vs UUID v7 vs bigint, at a glance

The three keys differ in how much of the value is random, and that difference is exactly what a B-tree cares about.

Bit counts come from RFC 9562 for the UUID versions and ulid/spec for ULID; the bigint range is PostgreSQL's documented numeric type.

What does a random primary key do to a B-tree?

A B-tree index keeps its leaf pages sorted, so an insert that lands inside an already-full page forces a split; an insert that extends the sorted order past the last page does not.

Every leaf page holds a fixed number of keys. Insert a new maximum value and most B-tree implementations append it to the rightmost page, and once that page fills, open a new one after it, with no rebalancing and no rewritten neighbors. Insert a value that has to land inside a page that's already full, which is what a random 128-bit key does roughly as often as it doesn't, and the page splits into two half-full pages instead. Postgres describes this as a cascading split in its own B-tree structure documentation, and the mechanism applies to any B-tree, not only Postgres's.

The cost lands in different places depending on the engine. Postgres stores table rows in a heap, so a random key only bloats the primary key's own index. MySQL's InnoDB clusters the table by its primary key, so the same random key fragments the table storage itself, and InnoDB's own documentation says a long or poorly ordered primary key makes every secondary index bigger too, since each one stores a copy of it. SQLite's default rowid tables behave more like Postgres: the primary key lives in a separate index over rows already kept in rowid order, so a random key bloats that index without moving the rows.

Run 24 inserts, four keys per page, in sequential order and you get zero splits and six pages. Run the same 24 values in random order and you get six splits and eight pages, because roughly half the inserts land inside a page that's already full.

What's the cheapest way to store a 128-bit primary key?

Postgres's native uuid type and MySQL's BINARY(16) both store the value in the 16 bytes it needs; every text encoding costs more, and you pay that cost again in every secondary index that includes the key.

Four storage shapes cover almost every schema. A native binary column, Postgres's uuid type or MySQL's BINARY(16), holds exactly 16 bytes. A fixed char(36) column holds the hyphenated hex text, and a varchar(36) holds the same text plus a length header, one extra byte in Postgres and in MySQL's UTF-8 encoding.

SQLite has no native uuid type at all: store the value as a BLOB and it's kept exactly as written, 16 bytes if you pass it in binary, 36 if you pass it in as text.

At 100,000,000 rows with three secondary indexes that each carry a copy of the primary key, storing it as varchar(36) instead of a 16-byte uuid costs an extra 7.82 GB: 100,000,000 rows × 21 extra bytes per value × 4, the primary key column plus the three secondary indexes that store a copy of it.

When is an integer still the right answer?

A single-node table that will never shard and never puts its key in a URL is better off with a bigint. The moment the key does reach a URL, the question stops being about index size and starts being about what the ID gives away.

A bigint sequence is half the width of a UUID and roughly a fifth the width of its hex text, so it halves the size of every index built on it before you've written a line of application code. It needs no library, no extension, and no version check, and a database sequence hands out strictly increasing values with no risk of two rows landing on the same value, which a UUID v7 minted on two different application servers in the same millisecond can do.

The tradeoff is real: a bigint reveals row count and insertion order to anyone who sees it, and two systems can't generate one offline and merge later without a coordinator assigning ranges. If neither of those is true for your table, the UUID buys you nothing.

How do you migrate to UUID v7 without downtime?

Add the new column nullable, backfill it in batches, and only then swap the primary key, so nothing ever locks the table for writes.

On PostgreSQL 18, adding a column with a default that computes a new value per row, like uuidv7(), forces a full table rewrite, so skip the default at first and fill it with a trigger instead:

-- PostgreSQL 18
ALTER TABLE orders ADD COLUMN id_v7 uuid;

CREATE FUNCTION orders_set_id_v7() RETURNS trigger AS $$
BEGIN
  NEW.id_v7 := coalesce(NEW.id_v7, uuidv7());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_id_v7 BEFORE INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_set_id_v7();

New rows get a UUID v7 the moment they're inserted. Backfill the rest, assuming the existing primary key is id, in batches small enough that no single transaction holds a long lock:

-- PostgreSQL 18, repeat until it updates zero rows
UPDATE orders SET id_v7 = uuidv7()
WHERE id_v7 IS NULL
  AND id IN (SELECT id FROM orders WHERE id_v7 IS NULL LIMIT 5000);

Once every row has a value, mark the column NOT NULL without a blocking table scan, using a NOT VALID check constraint validated separately, then build the new unique index without blocking writers and swap it in:

ALTER TABLE orders ADD CONSTRAINT id_v7_not_null
  CHECK (id_v7 IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT id_v7_not_null;
ALTER TABLE orders ALTER COLUMN id_v7 SET NOT NULL;

CREATE UNIQUE INDEX CONCURRENTLY orders_id_v7_key ON orders (id_v7);
ALTER TABLE orders DROP CONSTRAINT orders_pkey,
  ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_v7_key;

Every step here is documented on PostgreSQL's ALTER TABLE reference. Before you cut over, generate a UUID v7 and compare a fresh value against the ones your migration just wrote.

Common questions

Does UUID v7 always insert faster than UUID v4?

No. The gap comes from page splits, and a page split only costs real time once the table and its indexes stop fitting in memory. On a table small enough to stay cached, a v4 write and a v7 write hit the same warm pages, and the difference disappears into noise.

Confirming exactly where that gap becomes visible would take a real benchmark: the same schema and workload run with both key types, pg_relation_size and median insert latency measured across a range of row counts on fixed hardware.

What about ULID instead of UUID v7?

ULID carries the same 48-bit millisecond timestamp as UUID v7 and sorts the same way. The difference is text encoding: a ULID is 26 case-insensitive Crockford base32 characters with no hyphens, against a UUID's 36. Neither Postgres nor MySQL has a native ulid column type, so stored as text it costs exactly what the char/varchar rows above cost, just ten characters shorter.

Can Postgres read a UUID's version and timestamp back out?

Yes, but not all in the same release. Postgres 17 added uuid_extract_version() and uuid_extract_timestamp(). Postgres 18 added the generation side, uuidv7() and uuidv4(), and extended uuid_extract_timestamp() to read a v7 timestamp as well as a v1 one.

Does every database build the new index without blocking writers?

Postgres does, with CREATE INDEX CONCURRENTLY, at the cost of scanning the table twice instead of once. MySQL's InnoDB and SQLite each have their own online-DDL rules, and they don't all block the same operations. Check your engine's own documentation before assuming the Postgres steps above translate directly.

How we computed the numbers on this page

The B-tree diagram runs a real leaf-split simulation in your browser: four keys per page, 24 inserts, using a seeded 32-bit LCG for the random order so the same run happens on the server and the client. It mirrors one thing about real B-trees that matters here, appending past the last page instead of splitting, but it isn't a database: it doesn't model internal pages, fill factor, or the write-ahead log.

The storage table is arithmetic, not a measurement: rows × bytes per value × (the primary key column plus the secondary indexes), which ignores per-page overhead, alignment padding and TOAST, so a real table lands above every number it prints. Both are visible with JavaScript disabled, since the numbers above come from the same calculation rendered on the server.

uuid.lol generates UUID v4, UUID v7, ULID, and every other format on this site entirely in your browser. Nothing you create here is sent anywhere.