UUID vs bigint Primary Keys: What the Performance Gap Really Is, and uuidv7

For an ordinary service starting on a single database, the primary key defaults to bigint GENERATED ALWAYS AS IDENTITY, and you reach for uuidv7 only when a UUID requirement (distributed generation, external exposure) exists. That was the one-table conclusion in basics chapter 2; this article is the deep dive into the grounds — what “UUIDs are slow” actually means, and what uuidv7 changed.

Why bigint is the default #

• Size: bigint is 8 bytes, UUID is 16. More important than the PK itself is the ripple: the PK is carried in every secondary index and duplicated into every child table as an FK column. Double the PK and the indexes across the board grow with it — and that much less index fits in cache.
• Insert locality: IDENTITY numbering is monotonically increasing, so new rows always land at the right edge of the B-tree. Writes touch a handful of pages, cache hits stay high, and less WAL is produced.
• Readability: in logs and incident response, id = 4213 is easy to handle. It looks trivial and is felt daily in operations.

What “UUIDs are slow” really is: v4’s randomness #

It’s not UUIDs that are slow — it’s UUID v4’s randomness. v4 is fully random, so new rows land anywhere in the B-tree. Three consequences follow: every insert touches a different index page, so cache hit rates collapse (the moment the index outgrows memory, disk reads mix into every insert); page splits multiply and the index bloats; and every touched page must be logged, so WAL volume grows. This is why insert-throughput differences become measurable from the millions-of-rows scale. Losing PK range scans (“newest signups first”) comes along as a bonus loss.

uuidv7: dissolving the randomness problem with time ordering #

uuidv7 puts a millisecond timestamp in the first 48 bits and fills the rest with randomness. As a UUID that sorts by creation time, new rows cluster near the right edge of the index just like bigint. v4’s cache, page-split, and WAL problems mostly disappear, and from PostgreSQL 18 it’s a built-in function.

CREATE TABLE orders (
    id         uuid PRIMARY KEY DEFAULT uuidv7(),
    user_id    bigint NOT NULL REFERENCES users (id),
    created_at timestamptz NOT NULL DEFAULT now()
);

Know the remaining trade-offs precisely. The 16-byte size stays, so the index and FK ripple remains; and the creation time is exposed in the ID (if your reason for choosing v4 was unguessability, v7 makes the time portion guessable — not a problem for most services, but worth weighing where creation time itself is sensitive). The bigint problem of sequential exposure — letting outsiders infer counts — doesn’t exist here.

The decision table #

The hybrid design is also a strong practical answer: keep internal PK, FK, and joins on bigint, and add a separate public_id uuid DEFAULT uuidv7() UNIQUE column for external exposure. You get join performance and exposure safety both, at the cost of one extra column and index. API responses and URLs carry only the public_id.

One common misconception to close on: “we might shard someday, so UUID from day one” is mostly over-engineering. When that day comes there are migration paths — partitioning or the hybrid design — and taxing every present join and index for a future that may not arrive is a decision made in the wrong order.

Summary #

• The default is bigint IDENTITY. The grounds are the size ripple (every index and FK) and insert locality.
• “UUIDs are slow” really means v4’s random inserts: cache collapse, page splits, WAL growth — a measurable mechanism.
• uuidv7 dissolves most of that with time ordering and is built into PostgreSQL 18. The 16-byte size and creation-time exposure remain as trade-offs.
• With distributed-generation or exposure requirements, use uuidv7; to have both, use internal bigint + external uuidv7.
• Preemptive UUIDs for hypothetical sharding are mostly overkill. Decide from the workload you have.