Database Indexing Strategies

UUID v4 primary keys are the silent performance killers of modern web applications. They look clean, they're globally unique, and they cause catastrophic B-Tree fragmentation at scale. Let's talk about why—and what to do instead.

The B-Tree Problem

B-Tree indexes perform best when new entries are inserted in sequential order. A UUID v4 is randomly distributed across the entire key space, meaning every INSERT causes a page split somewhere in the middle of the tree. At 10M rows, this translates to a bloated index that is 40-60% larger than necessary.

-- Avoid: random UUID fragmentation
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ
);

-- Prefer: UUIDv7 (time-sortable) or ULID
CREATE TABLE events (
  id TEXT PRIMARY KEY DEFAULT gen_ulid(),
  created_at TIMESTAMPTZ
);