UUID vs Auto-Increment IDs

The choice between UUIDs and auto-incrementing integers as primary keys is one of the most debated topics in database design. Each approach has distinct advantages depending on your application's architecture and scale.

Auto-increment advantages:

• Smaller storage: 4 bytes (INT) or 8 bytes (BIGINT) vs 16 bytes (UUID). This affects index size, join performance, and memory usage for cached indexes.
• Better index locality: Sequential integers append to the end of B-tree indexes, resulting in minimal page splits and optimal write performance.
• Human readability: Order #12847 is easier to communicate than order 550e8400-e29b-41d4-a716-446655440000.
• Natural ordering: IDs implicitly encode creation order.

UUID advantages:

• Distributed generation: Multiple servers can generate IDs independently without coordination, eliminating single-point-of-failure and sequencing bottlenecks.
• Merge-friendly: When combining data from multiple databases (microservices, multi-tenant, sharded), UUIDs never collide. Integer IDs require remapping.
• Security through opacity: Sequential IDs leak information (total record count, creation rate, enumeration attacks). UUIDs reveal nothing.
• Client-side generation: Clients can create IDs before the server round-trip, enabling optimistic UI updates and offline-first architectures.

Performance benchmarks (PostgreSQL, 10M row table):

                  | BIGINT PK  | UUID v4 PK | UUID v7 PK
INSERT rate       | 45K/s      | 18K/s      | 40K/s
Index size        | 214 MB     | 412 MB     | 412 MB
SELECT by PK      | 0.02ms     | 0.03ms     | 0.03ms
Range scan (1000) | 1.2ms      | 3.8ms      | 1.5ms

UUID v7 nearly matches auto-increment insert performance because its time-ordered nature preserves index locality. The remaining gap is due to the larger key size (16 vs 8 bytes).

The hybrid approach: Many production systems use both: a UUID as the public-facing identifier and an auto-increment integer as the internal primary key. This gives you the security of UUIDs in APIs while keeping the performance of integers for joins and indexes.

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  -- ... other columns
);

Recommendation: For monolithic applications with a single database, auto-increment is simpler and faster. For distributed systems, microservices, or applications that need client-side ID generation, use UUID v7.