A hot spot is a database index contention problem. When inserts cluster at the same location in a B-tree index, concurrent writers compete for the same index page latch — serializing what should be parallel writes. Understanding hot spots is key to choosing between UUID v4, UUID v7, and sequential IDs as primary keys.

How B-tree hot spots form

B-tree indexes store keys in sorted order in fixed-size pages (typically 8 KB in PostgreSQL). When new records are inserted:

  1. The database finds the correct sorted position for the new key
  2. If the target page is full, a page split occurs
  3. With sequential keys, the rightmost page is always the target

With auto-increment integers, 100% of inserts go to the rightmost page. With UUID v7, inserts within the same millisecond cluster at the rightmost page.

Auto-increment index:        UUID v4 index:          UUID v7 index:
[1, 2, 3] [4, 5, 6] [7,→]   [r, r, r] [r, r, r]   [ts1, ts1, ts1] [ts1, ts2, ts2] [ts2,→]
                 ↑ hot                random                                           ↑ mild hot

UUID v4 vs UUID v7 hot spot comparison

ID typeWrite distributionSort orderHot spot risk
Auto-incrementSequentialChronologicalSevere
UUID v4Fully randomNoneNone
UUID v7Mostly random (74 rand bits)ChronologicalLow to mild
UUID v6Mostly random (62 rand bits)ChronologicalLow to mild

UUID v7 is significantly better than auto-increment for hot spots because 74 bits of randomness spread writes across many index pages. Only at extreme write rates (millions of inserts per second sharing the same timestamp) does UUID v7 clustering become a concern.

Measuring hot spots

-- PostgreSQL: check index bloat and page contention
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Check for page-level lock waits (hot spots manifest here)
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state != 'idle'
GROUP BY wait_event_type, wait_event
ORDER BY count DESC;

Mitigation strategies

1. BRIN index for UUID v7 columns

-- BRIN is ideal for UUID v7 — naturally time-ordered, huge space savings
CREATE INDEX CONCURRENTLY orders_id_brin ON orders USING BRIN (id);
-- Keep B-tree for point lookups, use BRIN for range scans

BRIN indexes store only min/max values per block range — they are 100–1000× smaller than B-tree for time-ordered UUID v7 and avoid page contention entirely.

2. B-tree fill factor

-- Lower fill factor leaves room in each page for concurrent inserts
CREATE INDEX orders_id_idx ON orders (id) WITH (fillfactor = 70);
-- 30% of each page is reserved — reduces page splits and contention

3. Hash sharding (CockroachDB)

-- CockroachDB: hash-shard the primary key to eliminate hot spots
CREATE TABLE orders (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  -- ...
  PRIMARY KEY (id) USING HASH WITH BUCKET_COUNT = 8
);

See UUID v7 in CockroachDB.

4. Partitioning

-- PostgreSQL: partition by time range to spread write load
CREATE TABLE orders (
  id UUID NOT NULL,
  -- ...
) PARTITION BY RANGE (id);

-- UUID v7 makes time-based partitioning trivial:
-- partition boundary = UUID v7 value at partition start time

Hot spots in practice

For most production workloads (up to ~100K inserts/second), UUID v7 causes no observable hot spot. Problems appear at:

In these scenarios, UUID v4 eliminates hot spots but sacrifices time ordering. UUID v7 with BRIN indexes provides both time ordering and hot spot resistance. See UUID v7 Performance Benchmarks for insert throughput numbers.

Frequently asked questions

What is a hot spot in a database index?

A hot spot is a specific page or range in a B-tree index that receives disproportionately frequent read or write operations. The most common cause is sequential primary keys: auto-increment integers always insert into the rightmost index page, so all concurrent inserts compete for a single page latch. This causes lock contention, reduced throughput, and higher tail latency. UUID v4 solved this by distributing writes randomly across all index pages.

Does UUID v7 cause database hot spots?

UUID v7 can cause mild hot spotting at very high insert rates. The 48-bit timestamp prefix means all UUIDs generated within the same millisecond share the same timestamp prefix — they differ only in the 74 random bits. At moderate rates (thousands of inserts per second), this is not a problem. At extreme rates (millions per second), the rightmost index page receives a concentration of inserts. Solutions include hash-sharded indexes (CockroachDB, PostgreSQL) or filling index pages less aggressively with a lower B-tree fill factor.

How do I prevent UUID v7 hot spots in PostgreSQL?

Use a hash-sharded index or a BRIN index for time-ordered UUID v7 columns: CREATE INDEX ON orders USING BRIN (id). BRIN indexes are designed for naturally ordered data and store just a min/max per block, not per row — they are 100–1000× smaller than B-tree indexes for UUID v7 and avoid B-tree page contention entirely. For point lookups, keep the B-tree index but add WITH (fillfactor = 70) to leave room for concurrent inserts.