🗄️Database & Data⏱️ 15 min read•Updated October 2026

Database Primary Keys & Distributed ID Architecture: UUIDv4 vs UUIDv7 vs ULID vs Auto-Increment

The definitive architectural guide to database primary keys: Solve B-Tree index fragmentation with time-ordered UUIDv7 and ULID, prevent ID enumeration attacks, compare storage footprints, and implement migrations in PostgreSQL and MySQL.

#Database#PostgreSQL#MySQL#UUID#UUIDv7#Architecture#SQL#Performance

1. The Primary Key Showdown: Matrix Comparison

Choosing a primary key strategy impacts database performance, write throughput, storage size, and distributed scalability. Auto-increment integers provide fast sequential writes but leak business metrics, while completely random UUIDv4 destroys database caching.

Identifier Type	Sortable?	Storage Footprint	Collisions	B-Tree Index Friendly?
`BigInt Auto-Increment`	Yes (strictly sequential)	8 bytes	None within one database; needs coordination across shards	Excellent: append-only inserts. Downside: guessable and leaks total record count
`UUIDv4 (RFC 4122)`	No (completely random)	16 bytes	Practically impossible (122 random bits)	Poor: random inserts cause severe index page fragmentation
`UUIDv7 (RFC 9562)`	Yes (Unix ms timestamp prefix)	16 bytes	Practically impossible (74 bits of randomness per millisecond)	Good: mostly append-ordered and globally unique
`ULID`	Yes (48-bit timestamp)	16 bytes binary / 26 chars Crockford Base32	Practically impossible (80 random bits per millisecond)	Good: time-ordered

2. The UUIDv4 B-Tree Fragmentation Catastrophe

Databases use balanced B+ Trees to index primary keys. When inserting strictly sequential IDs, new records are appended to the rightmost leaf page with zero page splits. Because UUIDv4 is 100% random, each insert lands on a completely random page in the tree. Once the index exceeds RAM (buffer pool), every insert triggers a slow physical disk I/O and causes massive 50% page fragmentation.

The Hidden Cost of Random UUIDv4

/* Illustrative order-of-magnitude figures (workload and hardware dependent, not a universal benchmark)
Insertion performance cliff on 10,000,000 records:
 * Sequential BigInt / UUIDv7 : ~45,000 inserts/sec (cache hits: 99.8%)
 * Random UUIDv4              : ~2,500 inserts/sec  (cache hits: < 20% once RAM is full!)
 *
 * Physical table + index size on disk:
 * BigInt PK Table            : ~1.2 GB
 * UUIDv7 PK Table            : ~1.8 GB
 * UUIDv4 PK Table            : ~3.4 GB (Over 80% larger due to fragmented empty leaf slots!)
 */

Never use UUIDv4 as a clustered primary key on tables expected to hold millions of rows.

3. UUIDv7 Anatomy: The Modern Standard (RFC 9562)

Approved in 2024 as RFC 9562, UUIDv7 combines the best of both worlds: a 48-bit Unix timestamp in milliseconds followed by 74 bits of cryptographically secure randomness. Because the most significant bits represent time, UUIDv7 values sort naturally chronologically, eliminating B-Tree fragmentation.

Bit Breakdown of a UUIDv7

// Example: 018e69d7-8f5b-7c42-9a3e-6f8b1d2e3f4a
// [018e69d7-8f5b] - 48 bits: Unix timestamp in milliseconds
// [7]             -  4 bits: Version indicator (0111 = 7)
// [c42]           - 12 bits: Sub-millisecond sequence counter / random
// [9]             -  2 bits: Variant indicator (10 = RFC 4122)
// [a3e-6f8b1d2e3f4a] - 62 bits: Cryptographic random bytes

UUIDv7 provides microsecond sorting, prevents enumeration attacks, and fits natively into standard 16-byte UUID columns.

4. Database Implementation & Migration Runbook

How to implement native 16-byte binary UUIDs and time-ordered keys in PostgreSQL and MySQL.

PostgreSQL Native UUIDv7 Table Creation

-- PostgreSQL 17+ includes native uuid_generate_v7()
-- For PG 13-16, generate in application layer or use pg_uuidv7 extension
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    user_id UUID NOT NULL REFERENCES users(id),
    total_cents INT NOT NULL CHECK (total_cents >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
);

-- Create B-Tree index (perfect linear append, zero fragmentation)
CREATE INDEX idx_orders_user ON orders(user_id, id);

Store UUIDs as native 16-byte "UUID" type in PostgreSQL, never as varchar(36).

MySQL BINARY(16) Storage Pattern

-- MySQL lacks a native UUID type; store as BINARY(16) to halve index size
CREATE TABLE accounts (
    id BINARY(16) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

-- Convert string UUID to binary on insert
INSERT INTO accounts (id, email) VALUES (UUID_TO_BIN("018e69d7-...", 1), "user@example.com");
-- The second parameter (1) enables time-swapping for older UUIDv1!

Storing UUIDs as VARCHAR(36) in MySQL requires 144 bytes per row in utf8mb4 vs only 16 bytes for BINARY(16).

5. The Dual-Key Architecture for Public APIs

For maximum performance combined with public security, leading engineering teams use a dual-key architecture: an internal BigInt for lightning-fast join foreign keys, and an external UUID/ULID for public API endpoints and URLs.

Production Dual-Key Pattern

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,            -- Internal: High-speed relational joins, compact foreign keys
    public_id UUID NOT NULL DEFAULT uuid_generate_v7(), -- External: Exposed in API, URLs, webhooks
    company_name VARCHAR(120) NOT NULL,
    CONSTRAINT uq_customers_public_id UNIQUE (public_id)
);

-- Public API endpoint looks up by public_id:
-- GET /v1/customers/018e69d7-8f5b-7c42-9a3e-6f8b1d2e3f4a
-- Internal DB tables link via customer_id = 42!

Completely prevents IDOR (Insecure Direct Object Reference) attacks and competitor scraping while maintaining minimal index overhead.

🧰