What This Video Covers
This deep-dive style video walks through UUID primary key performance from a database engineering perspective — covering why random UUIDs cause problems, how to measure the impact, and what UUID v7 and BINARY storage fix.
Skill level: Intermediate to advanced Best for: Backend engineers and DBAs choosing primary key strategies for high-volume tables
Key Concepts You Will Learn
1. B-tree page splits — the core problem
A B-tree index stores rows in sorted order. Auto-increment integers always append to the rightmost leaf — efficient, predictable, cache-friendly. UUID v4 values are random, so each insert targets a random leaf node. When a leaf is full, it splits — a costly operation that cascades up the tree and fragments the index over time.
The fragmentation means:
- More pages needed for the same number of rows
- Lower buffer pool hit rate (random pages evicted more often)
- Slower range scans and
ORDER BY idqueries
2. UUID v7 solves page splits
Because UUID v7 has a millisecond timestamp prefix, consecutively generated values sort next to each other. Inserts cluster at the right edge of the index — the same pattern as auto-increment. The FILL FACTOR remains high, range scans are fast, and the index is compact. See PostgreSQL 18’s native uuidv7() for the server-side implementation.
3. Storage format comparison
| Format | Size | Type | Notes |
|---|---|---|---|
uuid (PostgreSQL) | 16 bytes | Binary | Native type, index-aware |
BINARY(16) (MySQL) | 16 bytes | Binary | Use UUID_TO_BIN(uuid, 1) |
VARCHAR(36) | 36 bytes | String | Avoid — 2× size, slower index |
CHAR(32) (no hyphens) | 32 bytes | String | Smaller but still string |
4. Migration from v4 to v7 without downtime
Existing UUID v4 rows do not need to change — the column type stores both v4 and v7 identically as 128-bit values. The migration is purely about new inserts: update application code or set a PostgreSQL 18 column default to uuidv7(). See the full zero-downtime guide at Migrating from UUID v4 to v7.
5. PostgreSQL 18 native support
PostgreSQL 18 ships a built-in uuidv7() function. Setting it as a column default means every insert path — ORM, raw SQL, migrations — automatically gets a v7 value without application changes.
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT uuidv7(),
status text NOT NULL
);
Related Reading
- UUID in Databases — comprehensive storage and index guide
- UUID vs Auto-Increment Primary Keys
- MySQL UUID Storage — BINARY(16) vs VARCHAR(36)
- PostgreSQL 18 Ships Native UUID v7
- Zero-Downtime Migration from UUID v4 to v7
- UUID in MongoDB — Replacing ObjectId
- UUID in SQLite — TEXT vs BLOB
Watch on YouTube
Search for UUID database primary key videos on YouTube ↗
Try the Tools
- Generate a UUID v7 — time-sortable primary key
- UUID Decoder — extract the embedded timestamp
- UUID Converter — convert to BINARY hex for MySQL
Frequently asked questions
Do videos on UUID database performance actually explain B-tree fragmentation?
Good UUID database performance videos explain that UUID v4 causes B-tree page splits because each new value inserts at a random position, fragmenting the index. UUID v7 solves this — its timestamp prefix means consecutive inserts cluster at the rightmost leaf, the same behaviour as auto-increment. See UUID Database Primary Keys video walkthrough and the full UUID in Databases guide.
Do UUIDs hurt database index performance?
UUID v4 does — its randomness causes every insert to land at a different leaf page, leading to page splits and poor cache locality. UUID v7 embeds a millisecond timestamp so consecutive inserts cluster together, behaving like an auto-increment integer for B-tree purposes.
Does PostgreSQL 18 support UUID v7 natively?
Yes. PostgreSQL 18 ships a built-in uuidv7() function that generates RFC 9562-compliant UUID v7 values directly in SQL — no extensions or application-level generation required. The values store in the existing uuid column type unchanged.
Why are UUID v4 primary keys slow at large table sizes?
UUID v4 values are random — each new insert lands at a random position in the B-tree index. This forces the database to read and write a random page for every insert, causing frequent page splits and cache misses. At millions of rows the fragmentation is severe enough to measurably slow inserts, range reads, and backups. UUID v7's timestamp prefix means new values always insert near the rightmost leaf, clustering writes exactly as auto-increment integers do. See UUID in Databases and UUID vs Auto-Increment.
What is the best column type for UUIDs in PostgreSQL vs MySQL?
In PostgreSQL, use the native uuid column type — it stores 16 bytes internally and the query planner understands it natively. In MySQL, use BINARY(16) with UUID_TO_BIN(uuid, 1) and BIN_TO_UUID(id, 1); the 1 flag reorders bytes for better index locality. Never use VARCHAR(36) — it stores 36 bytes and string comparison is slower than binary. See MySQL UUID Storage Best Practices.