UUID or Auto-Increment IDs

04 Mar 2026

Auto-increment integers are faster to index and impossible to keep private. UUIDs solve the privacy problem and create a performance one. UUIDv7 fixes most of that trade-off, and by 2026 it’s the default worth reaching for on a new table.

The core trade-off

	Auto-increment	UUIDv4	UUIDv7
Index size	Smallest (8 bytes)	Largest (16 bytes, random)	Larger (16 bytes, but sequential)
Index locality	Sequential, cache-friendly	Random, causes index bloat	Sequential, cache-friendly
Guessable	Yes — `id=1043` reveals row count and order	No	No
Safe to expose in URLs	No	Yes	Yes
Mergeable across databases	No, collides	Yes	Yes
Sortable by creation time	Yes, by definition	No	Yes, built in

Why auto-increment leaks information

/orders/1043 tells a competitor roughly how many orders you’ve processed. /users/12 tells anyone your twelfth signup exists and can probably be enumerated by trying /users/13, /users/14. This isn’t hypothetical — sequential IDs in URLs are a standard enumeration attack, and the fix (random IDs) is cheap enough that “we’ll deal with it later” isn’t a good trade.

Why plain UUIDs (v4) hurt at scale

A v4 UUID is 128 bits of random. Random values inserted into a B-tree index don’t append to the end — they scatter across the whole tree, forcing constant page splits and pushing recently-used pages out of cache. On a small table you won’t notice. On a table with tens of millions of rows and a primary key of UUIDv4, insert throughput and index size both suffer measurably compared to a sequential key.

-- v4: random, no ordering, worst case for B-tree insert locality
SELECT gen_random_uuid();

-- v7: first 48 bits are a millisecond timestamp, rest is random
-- sorts by creation time, inserts append to the end of the index like an auto-increment would

UUIDv7 in practice

Postgres 18+ has native uuidv7(). Earlier versions and most app frameworks have a library:

// Node, using the `uuidv7` package
const { uuidv7 } = require('uuidv7')
const id = uuidv7() // "0190f8b2-3e5a-7abc-9def-1234567890ab"

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuidv7(), -- Postgres 18+
  -- id UUID PRIMARY KEY, -- earlier: generate in application code, insert explicitly
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Because the timestamp prefix keeps IDs roughly ordered, you get most of the index-locality benefit of auto-increment, all of the non-guessability benefit of a random UUID, and — as a side effect — an ID you can sort by to approximate creation order without a separate index scan.

When auto-increment still wins

Internal-only tables never exposed via any API or URL, where guessability doesn’t matter.
Extremely high-insert-rate tables where every byte of index size is measured and matters (rare outside very large scale).
Join-heavy internal schemas where the smaller 8-byte key measurably reduces index size across many foreign keys — this does add up on genuinely large databases.

The practical default for 2026

New table, exposed via an API, at small-to-mid scale: UUIDv7. It removes the enumeration risk without the index penalty that made people avoid UUIDs for years. Reach for plain auto-increment only for internal tables that never leave your own backend, and reach for v4 only if you’re stuck on tooling that doesn’t yet support v7.

IAnswerable