UUID Primary Keys vs Auto-Increment

Auto-increment integers are the simplest primary key for a single-database application. UUIDs are the correct default for anything distributed. UUID v7 has largely closed the performance gap that made UUID v4 a painful choice for high-write tables. This article works through the trade-offs systematically.

What auto-increment gives you

Auto-increment (SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, AUTOINCREMENT in SQLite) generates a sequential integer from a central counter. The benefits are concrete:

• Compact storage. 4 bytes (INT) or 8 bytes (BIGINT) versus 16 bytes for a UUID — a factor of 2–4 in primary key size, which flows through to every index that includes the key.
• Optimal B-tree inserts. Sequential values always insert at the rightmost leaf page. No page splits. The index grows append-only.
• Human readable. user_id=42 is easier to reason about in logs, support tickets, and debugging than a 36-character UUID string.
• Natural ordering. ORDER BY id gives you insertion order with no extra column.

The limitation is that the counter is centralised. Two databases cannot independently generate unique sequential IDs without coordination.

What UUID v4 costs

UUID v4 trades away all of the above:

• 4× the storage of a BIGINT primary key
• Random inserts scatter across the B-tree, causing page splits on every write as the table grows
• Larger secondary indexes because every index that references the primary key carries a 16-byte value instead of 4–8 bytes
• Not human readable — opaque in logs

The benefit is complete independence: any node, offline client, or edge function can generate a globally unique ID without coordination.

What UUID v7 changes

UUID v7 keeps the independence of UUIDs but recovers the sequential insert property. Its 48-bit timestamp prefix means consecutive inserts arrive in order, so:

• Insert pattern is sequential (to millisecond precision) — same B-tree behaviour as auto-increment for rows created in normal operation
• No page splits for time-ordered inserts
• Index performance at scale is close to auto-increment, not random

The remaining costs: UUID v7 is still 16 bytes versus 4–8 for an integer, and it still looks opaque in logs. But the index fragmentation problem — the main practical argument against UUID primary keys — is gone for typical insert patterns.

Direct comparison

When to use auto-increment

• Single-database, single-writer architecture
• IDs are internal only and never appear in public URLs
• Maximum write throughput is a hard requirement
• You need the simplest possible schema

When to use UUID v7

• Multiple services or databases need to generate IDs independently
• You use read replicas and want IDs to be consistent across replica lag
• IDs are used as event or message IDs across a distributed system
• Mobile or offline clients create records before syncing
• You use sharding or multi-region deployments
• PostgreSQL 18 is available (zero-friction DEFAULT uuidv7())

When to keep UUID v4

• IDs appear in public URLs (share links, invite codes, password reset tokens)
• Creation time of a record must not be inferrable from the ID
• Session identifiers, API keys, capability tokens

The enumeration risk of auto-increment

Auto-increment IDs are enumerable: anyone who sees invoice_id=1042 knows there are at least 1041 other invoices and can likely request them sequentially. This is a significant concern for any ID that appears in a URL or API response.

The standard mitigations are: never expose internal IDs in URLs (use a separate public slug or token), or use UUIDs for public-facing identifiers even if you use auto-increment internally. Many applications use both — an internal BIGINT primary key for join efficiency and a UUID column for the external identifier.

PostgreSQL setup in 2026

-- Auto-increment (traditional)
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- UUID v7 (PostgreSQL 18)
CREATE TABLE orders (
  id UUID DEFAULT uuidv7() PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Dual-key pattern: integer PK + UUID public identifier
CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  public_id  UUID DEFAULT uuidv7() UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

The dual-key pattern gives you optimal join performance (integer FK) and safe public identifiers (UUID) at the cost of storing two keys per row.

Further reading

• UUID in Databases — storage types, index behaviour, migration paths
• UUID v4 vs UUID v7 — choosing between the two UUID versions
• PostgreSQL 18 Ships Native UUID v7 — what changes with the new built-in function