Definition
- B-tree page split – when a new key must be inserted into a full leaf page, the page splits in half, creating two half-full pages and extra I/O.
- Index fragmentation – after many splits, pages are half-full and scattered, wasting disk and memory.
- Cache/buffer-pool thrash – with random keys, each insert touches a different page, so the working set does not fit in memory and inserts hit disk.
- Clustered index (InnoDB) – the primary key physically orders the row data, so a random PK scatters the actual rows, not just an index.
- Time-ordered id (UUIDv7/ULID) – encodes a timestamp in the high bits so values sort in insertion order, making inserts append-friendly.
- Dual-id pattern – use a sequential clustered PK internally for storage efficiency and expose a separate random UUID as the external public identifier.
Imagine National Bank of India files transactions in a giant sorted ledger. A sequential id is like filing each new transaction at the BACK of the book: quick, the last page is always open on the desk. A random UUIDv4 is like inserting each transaction at a RANDOM page in the middle: the clerk constantly shoves existing entries aside to make room (page split), the book becomes full of half-empty pages (fragmentation), and the clerk must keep fetching different pages from the archive instead of keeping one open (cache misses).
Understanding the Concepts
What happens when you use a random UUID as a primary key?
A database primary-key index is a B-tree sorted by the key. When the key is monotonically increasing (1, 2, 3, or a time-ordered id), every new row's key is larger than all existing keys, so it appends to the rightmost leaf page. That page stays hot in memory and fills up neatly. A random UUIDv4 has no order, so each new value lands at a random position in the B-tree, requiring insertion into the middle of the tree.
package com.bank.model;
import jakarta.persistence.*;
@Entity
@Table(name = "transactions")
public class Transaction {
@Id
@GeneratedValue(strategy = GenerationType.UUID)
private java.util.UUID id; // random UUIDv4 -> scatters across B-tree
@Column(name = "account_id")
private String accountId;
@Column(name = "amount", precision = 15, scale = 2)
private java.math.BigDecimal amount;
}
Random UUIDv4 inserts:
- id = 3f2a... -> insert into MIDDLE of B-tree
- id = a8e1... -> insert into a DIFFERENT middle position
- id = 1b7c... -> insert into yet ANOTHER position
- Each insert targets a random leaf page
What is a B-tree page split?
When a new key must be inserted into a leaf page that is already full, the database performs a page split: it allocates a new page and moves roughly half the rows across to make room. Page splits are expensive because they require extra I/O, extra locking, and leave both the old and new pages approximately half-full.
Sequential keys (no splits):
Page 1: [1, 2, 3, 4, 5] Page 2: [6, 7, 8, 9, 10] Page 3: [11, 12, ...] <- append
All pages full, no splits.
Random UUIDv4 keys (constant splits):
Page 1: [1b7c, 3f2a, ...] <- FULL, new key 2e9f must go here
SPLIT! Page 1 becomes: Page 1a: [1b7c, 2e9f] Page 1b: [3f2a, ...]
Both pages now HALF-FULL -> wasted space = fragmentation
What is index fragmentation?
After many page splits, the index contains many half-full pages scattered across disk. The index occupies far more pages than the data actually needs, wasting both disk and memory. Scans that read the index sequentially must jump between non-contiguous pages, making range queries slower.
Fragmentation comparison (1M rows):
Sequential PK: 5,000 pages, 95% full, contiguous on disk
Random UUID PK: 12,000 pages, 50% full, scattered on disk
-> 2.4x more pages, 2.4x more I/O for full scans
Why is it worse on InnoDB?
InnoDB uses a clustered index where the primary key physically orders the table rows themselves. The row data lives in the leaf pages of the primary-key B-tree. So a random primary key scatters the ACTUAL ROWS, not just a secondary index. Additionally, every secondary index in InnoDB stores the full primary key as its row pointer, so a 16-byte random UUID bloats every secondary index.
InnoDB clustered index:
PK = row ordering. Random PK = random row order.
Secondary index stores: [indexed_column, PK]
With UUID PK: [account_id, 16-byte-UUID] per entry
With BIGINT PK: [account_id, 8-byte-BIGINT] per entry
-> Every secondary index is 2x larger with UUID
PostgreSQL heap tables:
Rows stored in heap (insertion order), PK is a separate B-tree.
Random PK fragments the INDEX but not the ROW storage.
Still bad, but less severe than InnoDB.
How do you fix it with UUIDv7 or ULID?
Use a time-ordered identifier so inserts append near the end while keeping global uniqueness. UUIDv7 encodes a millisecond timestamp in the high bits. ULID is a 128-bit, lexicographically sortable, timestamp-prefixed id.
package com.bank.model;
import jakarta.persistence.*;
import org.hibernate.annotations.UuidGenerator;
@Entity
@Table(name = "transactions")
public class Transaction {
// Time-ordered UUIDv7: inserts append, no fragmentation
@Id
@UuidGenerator(style = UuidGenerator.Style.TIME)
private java.util.UUID id;
@Column(name = "account_id")
private String accountId;
@Column(name = "amount", precision = 15, scale = 2)
private java.math.BigDecimal amount;
}
UUIDv7 values generated over time:
2026-01-15T10:00:00 -> 01930a5f-...
2026-01-15T10:00:01 -> 01930a60-...
2026-01-15T10:00:02 -> 01930a61-...
-> Values sort in time order -> inserts APPEND near the end
-> No page splits, no fragmentation, cache stays hot
What is the dual-id pattern?
If you need a random, non-guessable identifier for external exposure (API URLs, payment references), keep a sequential or time-ordered clustered PK internally for storage efficiency and expose a separate random UUID as the public id with its own index.
package com.bank.model;
import jakarta.persistence.*;
import java.util.UUID;
@Entity
@Table(name = "payments")
public class Payment {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "pay_seq")
@SequenceGenerator(name = "pay_seq", sequenceName = "payment_seq", allocationSize = 50)
private Long id; // internal clustered PK: sequential, fast
@Column(name = "public_ref", unique = true, nullable = false, updatable = false)
private UUID publicRef = UUID.randomUUID(); // external: random, non-guessable
@Column(name = "amount", precision = 15, scale = 2)
private java.math.BigDecimal amount;
}
Dual-id benefits:
- Internal PK (BIGINT): sequential, small, fast inserts, small secondary indexes
- Public ref (UUID): random, non-guessable, used in URLs and APIs
- Best of both worlds
How do sequential, UUIDv7, and UUIDv4 compare?
| Aspect | Sequential BIGINT | UUIDv7 | UUIDv4 (random) |
|---|---|---|---|
| Insert pattern | Append (rightmost) | Append (roughly) | Random (scattered) |
| Page splits | None | Rare | Frequent |
| Fragmentation | None | Minimal | Severe |
| Cache locality | Excellent | Good | Poor |
| Index size | 8 bytes | 16 bytes | 16 bytes |
| Distributed gen | No (single sequence) | Yes | Yes |
| Guessable | Yes (sequential) | Partially | No |
// Sequential: fastest inserts, not distributed
@Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "s")
@SequenceGenerator(name = "s", sequenceName = "txn_seq", allocationSize = 50)
private Long id;
// UUIDv7: append-friendly, distributed, slightly larger
@Id @UuidGenerator(style = UuidGenerator.Style.TIME)
private java.util.UUID id;
// UUIDv4: scattered, DO NOT use as clustered PK
@Id @GeneratedValue(strategy = GenerationType.UUID)
private java.util.UUID id;
What should a senior engineer know?
A senior engineer never uses random UUIDv4 as a clustered primary key on write-heavy tables. They choose UUIDv7 when distributed generation is needed and sequential BIGINT when it is not. They understand that the damage is worst on InnoDB because the clustered index IS the row storage. They use the dual-id pattern when they need both fast inserts and non-guessable external identifiers.
# Monitor index fragmentation in PostgreSQL
# SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
# FROM pg_tables WHERE tablename = 'transactions';
# Monitor in MySQL InnoDB
# SELECT table_name, data_free, data_length
# FROM information_schema.tables WHERE table_name = 'transactions';
Visual Overview
Sequential Keys vs Random UUIDv4
============================================
Sequential (BIGINT / UUIDv7):
+------+------+------+------+
| pg 1 | pg 2 | pg 3 | pg 4 | <- NEW rows append here
| FULL | FULL | FULL | 70% |
+------+------+------+------+
One hot page at the end. No splits. Cache hit.
Random UUIDv4:
+------+------+------+------+
| pg 1 | pg 2 | pg 3 | pg 4 |
| 50% | 50% | 50% | 50% | <- SPLIT everywhere
+------+------+------+------+
Every insert targets a random page.
Page full -> SPLIT -> two half-full pages.
Different page every time -> cache MISS.
InnoDB Clustered Index Impact:
============================================
Primary key = physical row order.
Random PK = random row order in data file.
Secondary indexes store [column, PK]:
+-------------------------+
| BIGINT PK: 8 bytes | <- small secondary index
| UUID PK: 16 bytes | <- 2x larger secondary index
+-------------------------+
Key Takeaway
A random UUIDv4 has no ordering, so when used as a primary-key index every insert lands at a random position in the B-tree rather than appending at the end. That causes page splits, index fragmentation, and cache thrash. It is worst on MySQL InnoDB because the primary key physically orders the row data itself, so a random PK scatters the actual rows, and every secondary index stores the full primary key, making them all larger.
The fix is a time-ordered identifier so inserts append near the end of the index while staying globally unique. At National Bank of India, Rajesh switched the transactions table from random UUIDv4 to UUIDv7 using Hibernate's @UuidGenerator(style=TIME), which kept the globally-unique, app-generatable id for the distributed ledger nodes but made inserts append again.
The senior nuance Pankaj adds is that if the team needs a random, non-guessable identifier for external exposure, they keep a sequential bigint or UUIDv7 as the internal clustered primary key and expose a separate random UUID as the public id with its own index. That way they get fast append-friendly inserts internally and non-sequential, non-guessable ids externally.
Remember one sentence: random UUIDv4 as a clustered primary key causes page splits and index fragmentation; use time-ordered UUIDv7, ULID, or sequential bigint so inserts append.
---