UUID vs Auto-Increment PKs
Choosing between auto-incrementing integers and UUIDs (or similar) as primary keys, balancing index performance, security, and distributed generation.
Description
Auto-incrementing integer primary keys (SERIAL/BIGSERIAL in PostgreSQL, AUTO_INCREMENT in MySQL) are compact (4-8 bytes), produce sequential values that are optimal for B-tree index insertion (no page splits), and are human-readable for debugging. However, they leak information (order and volume of records), cannot be generated client-side or in distributed systems without coordination, and can cause insert contention on the sequence in high-throughput scenarios.
UUIDs (128-bit, 36-character strings) can be generated anywhere without coordination, are non-guessable (important for URLs and APIs), and work well in distributed systems and merge scenarios. However, UUIDv4 (random) causes poor B-tree index performance because random values lead to scattered index page writes and higher WAL amplification. UUIDv7 (time-sorted, RFC 9562) solves this by embedding a timestamp prefix, making values roughly sequential while remaining globally unique and non-guessable.
The modern recommendation for most applications is UUIDv7 (or ULID, which is similar). It combines the distributed generation benefits of UUIDs with the sequential insertion performance of auto-increment keys. In PostgreSQL, use the uuid data type (16 bytes) rather than storing UUIDs as text (36 bytes). If you need compact, human-friendly IDs for customer-facing purposes (like order numbers), generate those as a separate column and keep the UUID as the internal primary key.
Prompt Snippet
Use UUIDv7 as the primary key for all new tables, generated application-side using a library like uuid v7 or uuidv7 (npm). Store as PostgreSQL's native uuid type (16 bytes), not TEXT. Create the column as id UUID PRIMARY KEY DEFAULT gen_random_uuid() as a fallback, but prefer application-generated UUIDv7 for time-sortability. For customer-facing identifiers (order numbers, invoice IDs), generate a separate short_id column using a prefix + NanoID (e.g., ord_a1b2c3d4). Never expose auto-increment IDs in API responses or URLs as they enable enumeration attacks.
Tags
Related Terms
Database Indexing Strategies
Creating and managing indexes to accelerate query performance while balancing write overhead and storage costs.
Database Sharding
Distributing data across multiple database instances by splitting rows based on a shard key, enabling horizontal scaling beyond the capacity of a single server.
Foreign Key Constraints
Database-enforced rules that guarantee a value in one table's column must reference an existing row in another table, preventing orphaned records.