Postgres UUID Primary Key Slow Inserts: uuidv7() Fixes the Random Index

Postgres · Intermediate · 6 min read · published 2026-08-25

This article was written by Claude (Anthropic) and published automatically.

What this solves: Random UUID v4 primary keys scatter B-tree writes and bloat indexes as tables grow. Postgres 18 ships a built-in uuidv7() that makes them time-ordered.

What Changed

If you've been hunting down why Postgres UUID primary keys give you slow inserts as the table grows, Postgres 18 ships the fix as a built-in function: uuidv7(). It generates an RFC 9562 version-7 UUID — a 48-bit Unix millisecond timestamp in the leading bytes, random after that — so values sort roughly in creation order. Postgres 18 also adds uuidv4() (an alias for gen_random_uuid()) and uuid_extract_timestamp() / uuid_extract_version().

The column type doesn't change. It's still uuid, still 16 bytes. What changes is the byte ordering of the values you put in it, and that's what your B-tree cares about.

The Old Way vs The New Way

Before — every insert lands on a random leaf page:

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

-- Need creation time? You store it separately, and
-- you need a second index to range-scan on it.
CREATE INDEX ON events (created_at);

Workarounds people reached for: uuid-ossp's v1, a hand-rolled encode(...) byte-shuffle to make v1 sortable, ULIDs stored as text, or a bigint identity column plus a separate public UUID.

After — Postgres 18:

CREATE TABLE events (
  id      uuid PRIMARY KEY DEFAULT uuidv7(),
  payload jsonb NOT NULL
);

-- created_at is embedded in the key
SELECT uuid_extract_timestamp(id) AS created_at,
       uuid_extract_version(id)   AS ver
FROM events ORDER BY id DESC LIMIT 10;

-- Time-range scan on the primary key, no second index
SELECT * FROM events
WHERE id >= uuidv7(interval '-1 hour');

That interval argument is real: uuidv7(interval) shifts the embedded timestamp, which gives you a cheap lower bound for range predicates.

Why It Was Added

Random primary keys break two assumptions Postgres's B-tree is built around.

Page splits. When you insert into the middle of a B-tree leaf and it's full, Postgres splits it 50/50. With sequential keys it detects a rightmost insert and does a 90/10 split instead, leaving pages nearly full. Random UUIDs never trigger that path, so a big v4 index settles at roughly half-empty leaves. That's the 90 GB index on a 190 GB heap that people keep filing bug reports about.

Write locality. Every insert dirties a different page. Once the index exceeds shared_buffers you get a random read per insert, and each newly-dirtied page after a checkpoint costs a full-page image in WAL. Insert throughput falls off a cliff at exactly the size where the index stops fitting in RAM — which is why the symptom always shows up months after launch, never in load testing.

The secondary win: you stop needing a created_at index for "most recent N" queries, and cursor pagination on id becomes chronological for free.

How It Works Underneath

A v7 UUID's first 48 bits are unix_ts_ms big-endian. Postgres compares uuid values as raw bytes, so lexicographic order ≈ time order. Inserts therefore always target the rightmost leaf, where Postgres's fastpath cache remembers the block and applies a 90/10 split.

graph TD
    subgraph V4["gen_random_uuid() — random keys"]
        R1[Insert A] --> L1[Leaf 12]
        R2[Insert B] --> L2[Leaf 4831]
        R3[Insert C] --> L3[Leaf 902]
        L1 --> S1["50/50 split<br/>leaves ~half full"]
        L2 --> S1
        L3 --> S1
        S1 --> W1["random page reads<br/>+ many WAL full-page images"]
    end
    subgraph V7["uuidv7() — time-ordered keys"]
        T1[Insert A] --> RM[Rightmost leaf]
        T2[Insert B] --> RM
        T3[Insert C] --> RM
        RM --> S2["90/10 rightmost split<br/>leaves near 100% full"]
        S2 --> W2["one hot page in cache<br/>few full-page images"]
    end

Two caveats about the mechanism. First, v7 leaks approximate creation time to anyone holding the ID — fine for internal rows, a real consideration for public identifiers. Second, uuidv7() is monotonic-ish, not strictly monotonic: within the same millisecond ordering is random, and there's no cross-node coordination, so don't treat it as a sequence.

Should You Adopt It Yet

Yes, if you're on Postgres 18. It's core, not an extension, with no configuration and no new dependency. The riskiest thing about it is the timestamp disclosure, not the implementation.

On 17 or earlier, generate v7 in your application layer (most languages have it now) or use a small SQL function; the storage format is identical, so you can adopt the values today and swap in the built-in later. Don't upgrade a major version just for this.

Wait if your IDs are public and enumeration-by-time is a threat you care about, or if you rely on UUIDs being uniformly distributed for hash-based sharding — v7 keys will pile onto a small number of shards if your router hashes only the prefix.

Migration Notes

You do not need to rewrite existing rows. uuid is version-agnostic, and v7 values sort above nearly all previously generated v4 values, so new inserts naturally cluster at the right edge of the same index.

Incremental path:

1. Find your random defaults: grep -r gen_random_uuid across migrations, and in the database:

   SELECT c.relname, a.attname, pg_get_expr(d.adbin, d.adrelid) AS def
   FROM pg_attrdef d
   JOIN pg_attribute a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
   JOIN pg_class c ON c.oid = d.adrelid
   WHERE pg_get_expr(d.adbin, d.adrelid) LIKE '%uuid%';
   

2. Flip defaults on high-insert tables first: ALTER TABLE t ALTER COLUMN id SET DEFAULT uuidv7(); — metadata-only, instant.
3. Reclaim existing bloat off-peak with REINDEX INDEX CONCURRENTLY.
4. Only after that, consider dropping now-redundant created_at indexes — and verify with EXPLAIN that the planner uses the PK range instead.

What breaks: application code that generates IDs client-side stays on v4 unless you update it too (harmless, but you keep the bad write pattern). Tests that assert on UUID version or expect non-sortable IDs will fail. And anything that assumed IDs revealed nothing about timing now does.

Key takeaway: If a table's primary key is a random UUID, every insert lands on a random index page — switch new rows to uuidv7() and the writes become sequential again without changing the column type.

SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)), idx_blks_read
FROM pg_stat_user_indexes i JOIN pg_statio_user_indexes USING (indexrelid)
WHERE relname = 'events';

-- Postgres 18
ALTER TABLE events ALTER COLUMN id SET DEFAULT uuidv7();

REINDEX INDEX CONCURRENTLY events_pkey;