UUID vs auto-increment

Updated April 26, 2026 · 5 min read · by Samuel Yoo

TL;DR. Auto-increment is smaller (8 bytes vs 16) and slightly faster. UUIDs work without coordination, hide row count, and survive system merges. For most new systems, UUID v7 is the right default — the storage cost is tiny and the operational benefits compound. Auto-increment still wins for small, single-database systems where simplicity is the goal.

The two designs

Where auto-increment wins

• Storage. 8 bytes vs 16. On a 1B-row table, that’s an 8 GB difference in primary-key index size alone — and it propagates to every foreign-key column.
• Insert speed. Sequence generation is a single atomic counter increment. Microsecond-scale ops/sec, faster than any UUID generator.
• Readability. /orders/4823 is human-comprehensible; /orders/0e6f1b8c-... is not.
• Simplicity. No application-side ID generation. No “which version of UUID do we want?” discussion. The database handles it.
• Audit trail. WHERE id BETWEEN 1000 AND 2000 selects 1000 consecutive rows; equivalent UUID range queries don’t make sense.

Where UUIDs win

• No coordination required. Two services on different machines can generate IDs that won’t collide. Auto-increment requires a single sequence service or partitioning convention.
• Mergeable systems. Acquiring a company? Their UUIDs slot into yours. Their auto-increment IDs collide on day one.
• Offline-first clients. A mobile app can generate UUIDs while offline and sync them when the connection returns. With auto-increment, the client must wait or use a temporary ID and remap.
• No information leak. GET /orders/100 tells a competitor your daily order volume. UUIDs reveal nothing.
• No “ID enumeration” attacks. With sequential IDs, an attacker who finds one valid ID can iterate to find others. UUIDs are unguessable (v4) or predictable only in time (v7).
• Stable across environments. A row created in dev keeps the same ID in staging and prod when copied.

Performance — the v4 vs v7 distinction

Old advice said UUIDs were “slow as primary keys.” That was true for v4 because random insertion fragments B-tree indexes. With v7 (time-ordered), insertion clusters at the rightmost leaf, just like auto-increment.

Approximate benchmarks on PostgreSQL (insert rate, 100M-row table):

So the conventional wisdom needs an asterisk: v4 UUID has the cost; v7 is within 10% of bigserial. The 16-byte storage overhead is real but typically not a deciding factor.

When auto-increment is the right call

• Single database, single service, no offline clients. The infrastructure complexity tax of UUIDs has nothing to balance against.
• Tiny tables. A categories table with 50 rows doesn’t benefit from 16-byte keys.
• Read-heavy with WHERE id IN (...) patterns. 8-byte values pack denser in IN-list indexes; tiny but measurable.
• Strict ordering matters more than independence. Audit logs, sequence-of-events tables. Use bigserial.

When UUIDs are the right call

• Multi-service architectures. Orders generated by service A, payments by service B, both writing to a shared analytical database — UUIDs avoid the coordination service.
• Public-facing IDs. URLs, share tokens, anything in a screenshot.
• Mobile or offline applications. Local writes that sync later.
• Anything that might be merged. Acquisitions, partner integrations, multi-tenant SaaS.
• You can’t predict. Default to UUID v7 for greenfield projects unless you can name a specific reason not to.

Common compromise: bigint internally, UUID externally

Some teams use both:

CREATE TABLE orders (
    id bigserial PRIMARY KEY,                       -- internal joins
    public_id uuid UNIQUE NOT NULL DEFAULT uuidv7() -- external URLs
);

Pros: peak insert performance for joins, opaque public URLs. Cons: two indexes, two columns, two ways to query, two ways to make mistakes.

For most apps, just use uuid v7 as the primary key and skip the second column. The performance difference is small and the simplicity matters.

”But UUIDs make joins slower”

Joins compare 16 bytes vs 8 bytes per row. In practice, the page-level access patterns dominate, not the per-row comparison cost. Modern CPUs compare 16-byte values in a single SIMD instruction. The overhead is usually < 5% on real workloads.

If your benchmarks show otherwise, the cause is almost certainly v4 fragmentation, not the comparison itself. Switch to v7.

Migration path

If you have an existing auto-increment table and want to add UUIDs:

-- Postgres example
ALTER TABLE orders ADD COLUMN public_id uuid UNIQUE NOT NULL DEFAULT uuidv7();

This adds a second identifier alongside the existing id. Use public_id in URLs and external APIs going forward; keep id for internal joins.

To fully migrate away from auto-increment requires renumbering every foreign key — which is rarely worth doing on a live system. Add UUIDs alongside; don’t replace.

Decision flowchart

Are you greenfield?
└── UUID v7 (lower friction over time)

Is your app single-DB, single-service, no offline clients,
no public-facing IDs?
└── bigserial (simpler, smaller, faster)

Do you have multiple services / databases / mobile clients?
└── UUID v7 (the coordination story is the headline benefit)

Do you have public-facing IDs that should be unguessable?
└── UUID (v4 for unguessability, v7 for sortable+unguessable)

Is index size a measured bottleneck at scale?
└── bigserial OR (bigserial internal + uuid external)

Try the tools

• UUID v7 generator — modern primary keys
• UUID as primary key — the deeper DB-design discussion
• PostgreSQL UUID — Postgres-specific patterns
• SQL UUID — cross-database storage and indexing