Stop Using UUIDv4 for Database Primary Keys
UUIDv4 is completely random. Storing completely random data in a B-Tree index causes page fragmentation and kills insert performance.
UUIDv4 is generated randomly.
When your database (like Postgres) indexes a primary key, it uses a B-Tree. B-Trees perform best when data is inserted sequentially (like auto-incrementing integers).
If you insert random UUIDs, the database has to constantly rebalance the B-Tree, writing to different disk pages for every insert. This causes massive write amplification and index bloat.
The Solution: Use time-sorted unique identifiers. Use UUIDv7, ULID, or Stripe's KSUID. They combine a timestamp prefix with a random suffix. They sort sequentially (fast inserts, no fragmentation) but still provide 128-bit collision resistance and obscurity.
Tags
Related Blogs
Three models, three failure modes
When building multi-tenant systems, you pick your isolation model early and live with it. Here is an analysis of Database-per-tenant, Schema-per-tenant, and Row-level security.
Multi-tenant Postgres without performance cliffs — why I chose RLS
Schema-per-tenant falls apart at 500+ tenants. Database-per-tenant is operationally unsustainable. Row-level security gives you strong isolation with a single connection pool. Here's exactly how.
Designing Scalable Analytics: From Postgres to ClickHouse
How we outgrew PostgreSQL for our blog's analytics engine, the pain of counting billions of rows, and how migrating to ClickHouse solved our aggregate query nightmares.