Stop Using UUID v4 as Your Primary Key

UUID v4 is one of the most widely used primary key formats in modern web applications. It's also one of the worst choices for Postgres, MySQL, or SQL Server at any reasonable scale.

This post explains exactly why, shows the benchmark numbers, and gives you a drop-in replacement that keeps the UUID format but fixes the performance problem: UUID v7.

The problem in one sentence

Why databases love sequential inserts

Postgres, MySQL (InnoDB), SQL Server, and SQLite all use a B-tree for primary-key indexes. B-trees are sorted, disk-friendly data structures that work best when new data arrives in order.

When every insert goes to the right side of the tree (as with auto-incrementing integers), three things happen:

• The rightmost leaf page stays hot in memory — subsequent inserts hit cache.
• Pages fill sequentially; there's little wasted space or fragmentation.
• Range scans (ORDER BY id LIMIT 100) read contiguous disk blocks.

This is why auto-increment BIGINTs are so fast.

What v4 breaks

A UUID v4 is 122 bits of pure randomness. Every insert goes to a different, unpredictable leaf page somewhere in the tree. The consequences are brutal:

• Cache misses. Each insert touches a cold page. At table sizes larger than RAM, almost every insert triggers disk I/O.
• Page splits. When an insert lands in a full page, the database has to split it in two, rewriting both pages and updating parent pointers.
• Index bloat. Split pages leave empty space. Your 16KB pages end up half-empty, doubling index size and shrinking effective cache.
• Slow range scans. Because adjacent rows by time are randomly distributed, pagination queries pull from many disparate pages.

The benchmark numbers

Community benchmarks comparing sequential vs. random UUID primary keys on Postgres 16 at 10M and 100M row tables consistently show:

Numbers are approximate — your exact figures depend on RAM, disk, and workload. But the trend is consistent: the bigger the table, the worse v4 looks, and it never catches up.

The fix: UUID v7

UUID v7 (RFC 9562, May 2024) encodes the current Unix timestamp in the first 48 bits of the UUID, followed by 74 random bits. Because the timestamp leads, new v7 UUIDs sort naturally after previous ones — both as binary and as strings.

That means inserts always go to the rightmost leaf, just like auto-increment BIGINTs. You keep the nice properties of UUIDs (globally unique, distributed-friendly, no coordinator needed) and get the insert performance of sequential integers.

How to switch in Postgres

First, install a v7 generator. Either the pg_uuidv7 extension:

CREATE EXTENSION pg_uuidv7;
SELECT uuid_generate_v7();

Or a pure SQL function if you can't install extensions:

CREATE OR REPLACE FUNCTION uuidv7() RETURNS uuid AS $$
  SELECT encode(
    set_bit(set_bit(
      overlay(
        uuid_send(gen_random_uuid())
        PLACING substring(
          int8send(floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint)
          FROM 3
        ) FROM 1 FOR 6
      ),
      52, 1), 53, 1), 'hex')::uuid;
$$ LANGUAGE SQL VOLATILE;

Then change your table default:

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

Existing rows keep their v4 UUIDs — only new inserts use v7. Over time your index rebalances as new entries append to the right side. For an immediate cleanup, run:

REINDEX INDEX CONCURRENTLY items_pkey;

during off-peak hours.

What about the timestamp leak?

v7 UUIDs expose the millisecond they were generated. For internal IDs this is almost always fine — many databases already expose created_at on the same rows anyway.

For IDs that get exposed externally (session tokens, password reset links, magic URLs), keep v4. The typical pattern: v7 as the internal primary key, v4 or a dedicated secret for anything user-facing.

Should I rewrite existing IDs?

No. Don't try to migrate existing v4 primary keys to v7 — it breaks foreign keys, disrupts caches, and invalidates any externally known IDs (URLs, client references, audit logs). Just change the default for new rows. The gains compound as your table grows.

The TL;DR

• v4 UUIDs make your database work harder than necessary.
• The damage scales with table size — painful at 100M+ rows.
• v7 fixes it without changing the UUID format or API surface.
• New projects should default to v7 primary keys.
• Existing projects should change the default for new inserts; no need to rewrite history.

Try v7 now

You can generate v7 UUIDs instantly in our free UUID Generator. Switch the version selector to v7, generate a batch, and paste them into your test harness. The built-in inspector will also decode the embedded timestamp so you can verify everything's working.