PostgreSQL UUID v4 Kills Performance: 50% I/O Waste

UUID v4 primary keys are silently killing PostgreSQL performance. A Hacker News discussion exploded today (316 points, 323 comments on December 15, 2025) as developers finally reckon with the hidden costs. The problem: random insertions trigger B-tree page splits that bloat indexes, amplify write I/O by 50%, and slow queries by orders of magnitude. Buildkite measured a 50% reduction in write I/O after ditching UUID v4. PostgreSQL 18, releasing September 2025, adds native UUID v7 support—a time-ordered alternative that solves the performance problem while keeping UUID benefits intact.

Why UUID v4 Destroys B-Tree Performance

UUID v4 generates random 128-bit identifiers. That randomness is poison for PostgreSQL’s B-tree indexes. Unlike sequential integers that append cleanly to the rightmost leaf page, random UUIDs scatter across the index like shrapnel. Each insertion lands in an unpredictable location, triggering page splits even when empty space exists elsewhere. The result: severe fragmentation.

The numbers don’t lie. Research on 10 million rows with 1 million updates shows UUID v4 indexes achieve only 79% leaf page fill compared to 97% for integers—that’s 20% wasted space. Buffer hits during index-only scans tell an even uglier story: integers generate 27,332 hits, while UUID v4 explodes to 8,562,960 hits. That’s a 31,229% increase accessing 68.3 GB more data and adding 0.86-3.4 seconds of latency purely from the data type choice.

Buildkite’s case study delivers the most damning evidence. They observed a 50% reduction in WAL (Write Ahead Log) generation after switching to time-ordered UUIDs. Every UUID v4 insertion is a tiny performance leak that compounds as tables scale past 1 million rows. Page splits generate WAL entries, bloating storage and slowing writes. Cache efficiency drops, disk I/O skyrockets, and queries crawl.

UUID v7 Fixes It With Time-Ordered Prefixes

UUID v7 solves the problem with a simple but powerful change: a 48-bit timestamp prefix. IDs become time-ordered while preserving UUID’s 128-bit format and global uniqueness. PostgreSQL 18 makes this official with a native gen_random_uuid_v7() function.

Performance benchmarks show UUID v7 matches integer speed. Inserting 1 million rows takes 290 seconds for both bigint and UUID v7, while UUID v4 lags at 375 seconds—29% slower. PostgreSQL 18’s native function generates UUIDs 33% faster than v4. Indexes shrink 26-27% compared to v4. ORDER BY queries run 3x faster. For older PostgreSQL versions, the pg_uuidv7 extension backports the feature.

Here’s what UUID v7 looks like in PostgreSQL 18:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid_v7(),
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Pre-18 users can use the extension:

CREATE EXTENSION pg_uuidv7;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  email TEXT NOT NULL
);

You get UUID benefits—distributed generation, no coordination, merge-friendly operations—without the performance penalty. If you need UUIDs, there’s no reason to use v4 anymore. UUID v7 performs like integers while remaining globally unique and compatible with existing UUID tooling. Database performance optimization starts with smart primary key choices.

Related: Azure HorizonDB: Microsoft’s 11-Year-Late Challenge to AWS Aurora

The Debate: Sequential vs Random, Not Integer vs UUID

The classic “integer vs UUID” debate misses the point. The real question is sequential vs random. Auto-incrementing integers excel for single-database apps: optimal performance, 8 bytes instead of 16, perfect B-tree append patterns. UUID v7 wins for distributed systems where coordination is expensive or impossible—no database round-trip needed, offline generation works, multi-region deployments stay simple.

ULID (Universally Unique Lexicographically Sortable Identifier) and Snowflake IDs offer middle ground. ULID packs a timestamp and randomness into 26 Base32 characters—human-readable and sortable. Snowflake IDs combine timestamp, machine ID, and sequence number into a 64-bit integer, generating IDs in 0.4 milliseconds compared to 5.6ms for UUID v4.

The trade-offs matter. Small projects with under 100,000 rows? UUID v4’s performance cost is negligible. Large tables exceeding 1 million rows with heavy writes? Switch immediately—the 50% I/O reduction at Buildkite translates to real cost savings in cloud environments where I/O pricing stings. Distributed microservices? UUID v7 or Snowflake IDs handle the coordination problem elegantly.

Don’t follow “always use X” advice blindly. Understand your workload. Match the identifier strategy to your architecture needs: single DB gets integers, distributed systems get UUIDs, maximum performance demands Snowflake.

Stop Using UUID v4: Migration Path and Recommendations

For new projects, the choice is clear. Use UUID v7 if you’re on PostgreSQL 18+. Use integers if you’re running a single database. For existing UUID v4 tables, evaluate migration when table size exceeds 1 million rows AND write volume is high. The migration isn’t trivial—changing primary keys requires rebuilding foreign key constraints—but the I/O savings often justify the effort.

PostgreSQL versions before 18 can use the pg_uuidv7 extension to backport UUID v7 support. Alternatively, adopt a hybrid approach: integer primary key for internal references, UUID index for external API exposure. This gives you integer performance internally while maintaining UUID benefits for external consumers.

Don’t panic-migrate everything. Small lookup tables under 100,000 rows aren’t worth the migration cost. But if you’re scaling past 1 million rows with write-heavy workloads, Buildkite’s 50% WAL reduction isn’t just a performance win—it’s a cost reduction in cloud environments where IOPS pricing adds up fast.

Key takeaways:

• UUID v4 primary keys cause measurable performance degradation: 20% worse index density, 31,000% more buffer hits, 50% WAL bloat
• UUID v7 solves the problem with time-ordered prefixes while maintaining global uniqueness and distributed generation benefits
• Choose based on architecture: integers for single databases, UUID v7 for distributed systems, Snowflake IDs for maximum performance
• PostgreSQL 18 (September 2025) makes UUID v7 the new default—pre-18 users can use the pg_uuidv7 extension today