Why Random UUIDs Fragment Your Postgres Index (and How to Fix It)

Random UUIDv4 primary keys are one of the most common silent performance killers in PostgreSQL. They do not break the heap the way they destroy InnoDB clustered indexes, but they still inflict measurable damage on the primary-key B-tree: page splits, reduced fill factor, cache thrashing, and inflated WAL. This post explains exactly why that happens at the storage-engine level and shows the practical fixes.

How PostgreSQL Indexes Actually Work

PostgreSQL tables are heaps. New rows are placed wherever the free-space map finds room; there is no permanent physical order by primary key. The primary-key constraint is enforced by a separate B-tree index that stores:

• the key value (8 bytes for BIGINT, 16 bytes for UUID)
• a TID (tuple identifier) pointing into the heap

Every insert therefore performs two operations:

1. Append (or reuse free space) in the heap — cheap and largely independent of key order.
2. Insert into the B-tree index — expensive when the key is random.

B-tree Leaf-Page Behavior

A B-tree leaf page is an ordered array of key/TID pairs. When a new key arrives:

• The engine walks the tree to the correct leaf.
• If the leaf has free space, the key is inserted in sorted order.
• If the leaf is full, a page split occurs: roughly half the entries move to a new page, and the parent is updated.

Sequential keys almost always hit the rightmost leaf. That page stays in shared_buffers, splits are rare, and the fill factor stays high (often > 90 %).

Random keys (classic UUIDv4) hit a uniformly distributed leaf. Once the index is larger than shared_buffers, almost every insert requires:

• reading a cold leaf page from disk,
• splitting it when full,
• writing the dirty page back,
• and, after a checkpoint, writing a full-page image into the WAL (full_page_writes = on).

The result is progressive fragmentation.

Quantifying the Damage

Fill Factor and Index Size

After a split, both resulting pages are typically ~50 % full. Subsequent random inserts continue to split, so the average fill factor of a random-UUID index stabilizes well below the sequential case.

Typical measurements on multi-million-row tables:

The extra size is pure waste: half-empty pages that still consume buffer-cache slots and I/O bandwidth.

Cache and WAL Effects

Because every leaf is equally likely to receive the next insert, the working set of the index becomes the entire index. When the index exceeds shared_buffers, cache hit rates collapse and random I/O appears.

WAL volume rises dramatically. Each newly dirtied leaf after a checkpoint triggers an 8 kB full-page write. Sequential keys dirty only a handful of rightmost pages; random keys dirty pages across the whole index.

Observed Latency Impact

On tables large enough that the primary-key index no longer fits in memory, point lookups and especially range scans on a UUIDv4 primary key can show:

• 5–20× more buffer reads than the equivalent BIGINT or UUIDv7 index,
• noticeably higher p95/p99 latency,
• higher CPU time spent in index traversal and page management.

How to Fix It

1. Switch to UUIDv7 (Best Direct Replacement)

UUIDv7 (RFC 9562) embeds a 48-bit Unix-millisecond timestamp in the most-significant bits, followed by random bits. The resulting values are monotonically increasing for practical purposes, so inserts behave like sequential keys while remaining globally unique and client-generatable.

-- PostgreSQL 18+
CREATE TABLE events (
    id         UUID PRIMARY KEY DEFAULT uuidv7(),
    payload    JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Older versions: pure-SQL implementation
CREATE OR REPLACE FUNCTION uuidv7() RETURNS uuid
LANGUAGE sql VOLATILE AS $$
  SELECT encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          placing substring(int8send((extract(epoch from clock_timestamp()) * 1000)::bigint) from 3)
          from 1 for 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex'
  )::uuid;
$$;

Client-side generation (C# example using a standards-compliant library):

// Using a library that implements RFC 9562 UUIDv7
Guid id = Uuid.NewDatabaseFriendly(Database.PostgreSql);
await connection.ExecuteAsync(
    "INSERT INTO events (id, payload) VALUES (@id, @payload)",
    new { id, payload });

JavaScript:

import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();

2. Hybrid Pattern (Often the Pragmatic Winner)

Keep a sequential BIGINT primary key for internal efficiency and expose a UUIDv7 as a unique public identifier:

CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id  UUID NOT NULL DEFAULT uuidv7(),
    customer_id BIGINT NOT NULL,
    amount     NUMERIC(12,2) NOT NULL,
    UNIQUE (public_id)
);

Foreign keys stay 8 bytes wide, the clustering-friendly index remains dense, and the application still obtains a globally unique, opaque identifier.

3. Other Ordered 128-bit Alternatives

All of these restore sequential index behavior. Prefer the one that fits your language ecosystem and operational constraints.

4. What Not to Do

• Do not keep UUIDv4 as the primary key on any write-heavy or large table.
• Do not store UUIDs as text/varchar — always use the native uuid type.
• Do not rely on occasional REINDEX as a permanent solution; it only masks the ongoing fragmentation.

Migration Sketch

If you already have a large table with a UUIDv4 primary key:

-- 1. Add new ordered column
ALTER TABLE big_table ADD COLUMN id_new UUID;

-- 2. Backfill with UUIDv7 (or keep existing values if you only need future inserts ordered)
UPDATE big_table SET id_new = uuidv7() WHERE id_new IS NULL;  -- or a batch job

-- 3. Add unique constraint / new primary key, update FKs, drop old column
-- (exact steps depend on downtime tolerance; use concurrent indexes and careful FK migration)

For zero-downtime migrations the hybrid pattern is usually safer: add a sequential BIGINT primary key, keep the old UUID as a unique secondary column, and gradually move application traffic.

Summary of Trade-offs

Conclusion

Random UUIDv4 primary keys fragment PostgreSQL B-tree indexes through repeated page splits, lowered fill factors, cache pollution, and excess WAL. The damage is real once the index outgrows memory, even though the heap itself is unaffected.

The fix is straightforward: stop using purely random keys for primary indexes. Prefer UUIDv7 for a drop-in ordered UUID, or adopt the hybrid pattern of a sequential BIGINT primary key plus a UUIDv7 public identifier. Both approaches restore the sequential insert behavior that B-trees are designed for, while preserving the architectural benefits that made UUIDs attractive in the first place.

Measure your own index size, buffer-hit rates, and WAL volume before and after the change—the difference is usually visible within minutes on a busy system.

---

Tools and Versions

Need some UUIDs for testing? Try our tools for generating UUIDv4   UUIDv7   ULID   NanoID and decoding validating.
For a detailed comparison of UUID versions, visit our Complete Guide to UUID Versions.