Auto-Increment ID vs UUID: The Hidden Cost of Distributed Primary Keys

The article analyzes and compares three popular primary key strategies—Auto-Increment ID, UUIDv4, and UUIDv7—from a system architecture perspective, aiming to help engineers choose the optimal option based on write topology and access patterns rather than choosing by default.

Quick Answer

Auto-Increment ID: Maximizes insert performance and index locality within a single database instance, but exposes business scale and introduces coordination bottlenecks in distributed write topologies.
UUIDv4: Enables fully decentralized, application-level ID generation, but significantly degrades B-tree index performance and increases random disk I/O under heavy write workloads.
UUIDv7: A strong default candidate for distributed relational systems that require independent ID generation combined with time-ordered index locality.

Context

Choosing a primary key (PK) strategy is an immutable architectural decision made during the early bootstrapping of a greenfield service. This choice deeply affects database performance, horizontal scaling capabilities, and long-term data evolution paths.

Assumptions:

The system uses relational databases (e.g., PostgreSQL, MySQL) leveraging B-tree based indexing for primary keys.
The system architecture is expected to evolve from a monolithic structure to a distributed or sharded model over time.
Basic security requirements do not strictly mandate unguessable IDs for simple resource enumeration, though API design should still account for identifier visibility.

Deep Dive

1. Database Indexing Mechanics & Insert Performance

When using an Auto-Increment Integer/BigInt with a single writer, new records are always appended to the right side of the B-tree index, preserving index locality and optimizing cache hit ratios.

In contrast, UUIDv4 values are entirely random. Inserting a UUIDv4 into a clustered B-tree index forces random leaf page insertions under write-heavy workloads, which triggers frequent page splits and increases random I/O scatter

UUIDv7 mitigates this by embedding a millisecond-precision Unix timestamp in its leading bits. This ensures that new records are appended in chronological order, restoring index locality while maintaining global uniqueness. Note that the time component in UUIDv7 only provides approximate temporal ordering for storage optimization and should not replace an explicit created_at column or business event timestamp

2. Write Topology and Distributed Scalability

Auto-increment IDs perform exceptionally well within a single database instance. However, as systems scale to multi-master, sharded, or multi-region environments, maintaining a global sequential order requires heavy coordination mechanisms or centralized sequence servers.

Both UUIDv4 and UUIDv7 allow decentralized ID generation directly at the application layer without database round-trips, eliminating coordination bottlenecks during horizontal scaling.

Pain Points / Failure Modes

Reduced Index Locality (UUIDv4): High-write environments using UUIDv4 suffer from degraded query latency over time due to disk scatter and frequent page splits.
Information Disclosure (Auto-Increment): Sequential IDs expose transaction volumes and growth metrics directly via public APIs if exposed without an opaque mapping layer.
Storage Overhead (UUID vs Integers): The 16-byte size of UUIDs is significantly larger than 4 or 8-byte integers, increasing the memory footprint in RAM buffer pools and bloating foreign key relationships.

Trade-off

Criteria	Auto-Increment ID	UUIDv4	UUIDv7
Insert performance	High (Sequential appends)	Variable (Random scatter)	High (Time-ordered appends)
Distributed generation	Requires coordination	Decentralized	Decentralized
Sortability	Native chronological	None	Approximate temporal
Storage footprint	Small (4-8 bytes)	Large (16 bytes)	Large (16 bytes)
Best fit	Single-writer CRUD	Distributed tokens	Distributed relational systems

Decision / Engineering Judgment

Given a distributed relational database with multi-node writers and high-write requirements → choose UUIDv7 because it supports decentralized ID generation while preserving index locality far better than UUIDv4.

If the system is a simple monolith with a single writer and strict constraints on foreign key storage efficiency → stick to BIGINT Auto-Increment.

Architectural Takeaway

Complexity: Choose identifier strategies based on actual write topologies and access patterns, rather than blindly relying on framework defaults.
Performance: Carefully evaluate the impact on index locality before adopting random identifiers for high-write tables.
Evolution: Designing for distributed systems early by adopting time-ordered unique identifiers prevents costly architectural refactoring paths later.