A critical look at new ID standards (CUID, UUIDv7) vs distributed database needs.

SitePoint is a  web development resource that offers tutorials, articles, and courses covering a wide range of topics, from frontend technologies like HTML, CSS, and JavaScript to backend frameworks and tools like Node.js, PHP, and Ruby on Rails. With a focus on practical, hands-on learning, SitePoint provides step-by-step guides, code samples, and real-world examples to help developers master essential skills and techniques. Whether you're a beginner looking to learn the basics of web development or an experienced developer seeking to expand your knowledge, SitePoint offers resources to support your learning journey.

SitePoint

UUIDv4 as a primary key in PostgreSQL causes significant B-tree index fragmentation, cache inefficiency, WAL bloat, and vacuum pressure due to its random nature. This deep-dive compares k-sortable ID formats—UUIDv7, ULID, KSUID, and CUID2—across sortability, storage compatibility, timestamp precision, and security tradeoffs. UUIDv7 (RFC 9562) emerges as the recommended default for distributed PostgreSQL systems: it fits the native uuid type, achieves index density comparable to bigserial (91–93%), and enables time-based partitioning on the id column itself. A complete zero-downtime migration script is provided, covering switching the column default, creating a partial index for recent data, batched backfill metadata tracking, and a rollback path. Edge cases like clock skew, regulated environments, and scenarios where UUIDv4 remains appropriate are also addressed.

Beyond UUIDv4: The Case for Cosmologically Unique IDs in Distributed Systems

The Indexing Problem: Why Random UUIDs Kill Database Performance

The K-Sortable ID Ecosystem: UUIDv7, ULID, KSUID, and CUID2

Head-to-Head Comparison: Choosing the Right ID for Your System

PostgreSQL Deep Dive: Indexing, Partitioning, and Storage

Migration: From UUIDv4 to K-Sortable IDs Without Downtime