UUIDs vs Auto-Increment IDs: Choosing Primary Keys

Picking a primary key feels like a five-minute decision until your table hits a billion rows or you try to merge two databases. The choice between a UUID and an auto-increment integer touches uniqueness guarantees, write throughput, index bloat, and what your URLs leak to strangers.

This is the tradeoff laid out honestly, with the cases where each one is the right call.

The two options

An auto-increment (or sequence/identity) key is a counter the database hands out: 1, 2, 3, and so on. It's compact (4 or 8 bytes), monotonically increasing, and trivially ordered.

sqlCopy
-- Postgres
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL
);

A UUID is a 128-bit value, usually written as 36 characters like

. The point is that any process, on any machine, can mint one without coordinating with a central authority and still be statistically certain it won't collide.

sqlCopy
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL
);

That single property — generate-anywhere, no coordination — is the entire reason UUIDs exist, and it's the lens for every tradeoff below.

Uniqueness and where IDs get assigned

With auto-increment, the database is the single source of truth for the next value. That's a strength (no collisions, ever) and a constraint (you must round-trip to the DB to learn an ID).

UUIDs flip this. The application, a mobile client offline, or a background worker can all create the ID before the row exists in the database:

• A mobile app can create a record offline, assign its UUID, and sync later without renumbering.
• A service can build an object graph (order + line items pointing at the order's ID) in memory, then insert it all in one transaction — no
  textCopy

  INSERT ... RETURNING
  dance to wire up foreign keys.
• Two systems can generate IDs independently and merge their data with no key conflicts.

If your writes are centralized in one database and you never generate IDs client-side, this advantage doesn't apply to you, and you're paying UUID costs for a benefit you don't use.

Sharding and distributed systems

This is where auto-increment starts to hurt. A single sequence is a single point of contention. Once you shard a table across multiple database nodes, you can't have every shard handing out

— they'd collide on merge.

The usual workarounds for keeping integers in a sharded world:

• Stepped sequences — shard A gives out 1, 4, 7; shard B gives out 2, 5, 8. Brittle when you add shards.
• A central ID service (the approach behind Snowflake-style IDs) — a dedicated service or algorithm packs a timestamp, machine ID, and counter into a 64-bit integer. This works well but is real infrastructure to build and run.

UUIDs sidestep all of it. Every node generates its own keys with no coordination, which is exactly why they dominate in microservices, event-sourced systems, and anything multi-region. If a sharded or distributed future is realistic for your data, that pushes hard toward UUIDs from day one — retrofitting a key change onto a live system is painful.

Leaking information: counts and enumeration

Sequential IDs are an information leak. If a competitor signs up and gets user ID

, waits a week, signs up again and gets , they've just measured that you added ~900 users in a week. The same applies to invoices, orders, and tickets — your URL quietly publishes your business volume.

Sequential IDs also invite enumeration attacks. If

works, an attacker will try 42, 43, 44. The ID itself isn't the vulnerability — missing authorization is (this class of bug is IDOR, insecure direct object reference) — but predictable IDs make the whole table trivially walkable once one check is weak.

UUIDs (the random kind) are effectively unguessable and reveal nothing about volume or ordering. That doesn't replace proper authorization, but it removes the free map you'd otherwise hand out. If you're putting IDs in public URLs, this matters. If you ever need a one-off random identifier for testing or a non-database resource, a quick UUID generator does it in the browser without leaving your machine.

Index performance: the real cost of random UUIDs

Here's the catch that bites teams who reach for UUIDs reflexively.

Most databases store the table or its primary index as a B-tree ordered by the key. With auto-increment, every new row sorts to the end of the index. Inserts touch the same few right-most pages, which stay hot in memory. The tree fills densely and stays compact.

Fully random UUIDs (version 4) scatter inserts across the entire index. Each insert lands on a random page, forcing the database to read and rewrite pages all over the structure. The practical consequences:

• Page splits and fragmentation — the index bloats and uses more disk.
• Cache thrashing — you can't keep the working set in memory because writes hit everywhere, so you go to disk far more often.
• More write amplification — especially brutal in storage engines that cluster the table by primary key (like InnoDB), where a random PK reorders the actual row data, not just an index.

Size matters too: a UUID is 16 bytes versus 8 for a

. Every secondary index carries a copy of the primary key, so that overhead multiplies. Stored as text () instead of a native UUID type, it's 36+ bytes and far worse — never store UUIDs as strings if your database has a native UUID type.

UUID versions, and why v7 changes the math

"UUID" isn't one thing. The version that matters most for primary keys:

• v4 — random. 122 bits of randomness. Maximum unpredictability, worst index locality. The default in most libraries.
• v1 — timestamp + MAC address. Time-ordered, but it embeds the machine's MAC address (a privacy and information leak) and its bit layout isn't sortable as-is.
• v7 — time-ordered random. A Unix-millisecond timestamp in the high bits, randomness in the low bits. Standardized in 2024 (RFC 9562).

UUID v7 is the answer to the index-performance problem. Because the timestamp leads, new UUIDs sort roughly to the end of the index just like an auto-increment key — you get the insert locality of integers and the generate-anywhere, no-coordination property of UUIDs in one value. The trade is that v7 leaks creation time (it's right there in the first bits) and is partially predictable, so it's not the choice when unguessability is the whole point.

A rough decision shortcut:

• Need unguessable public identifiers → v4.
• Need distributed/client-side generation and good database performance → v7.
• Need the smallest, fastest centralized key and don't care about leaking counts → auto-increment.

If your database doesn't generate v7 natively yet, generate it in the application; the format is stable and widely supported now.

A pattern that gets you both

You don't always have to pick one. A common and effective design uses both keys:

sqlCopy
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- internal, fast joins
  public_id UUID NOT NULL DEFAULT gen_random_uuid()    -- external, in URLs/APIs
);
CREATE UNIQUE INDEX ON orders (public_id);

The integer stays the internal primary key — small, dense index, cheap foreign-key joins between tables. The UUID is what you expose in URLs and API responses, so the outside world never sees a sequential counter. You pay for an extra column and one extra unique index, and in return you keep tight internal performance while leaking nothing externally. For many web apps this is the most pragmatic answer.

Practical guidance

• Single database, internal keys only, performance-sensitive: auto-increment
  textCopy

  BIGINT
  . Don't over-engineer it.
• Public-facing IDs in URLs: never expose raw sequential integers. Use a UUID, either as the key or as a separate
  textCopy

  public_id
  .
• Distributed, sharded, or offline/client-generated writes: UUIDs, and prefer v7 unless you specifically need unguessability.
• You chose UUIDs: store them in the native UUID type, not
  textCopy

  varchar
  . This alone fixes a large share of "our UUIDs are slow" complaints.
• Don't use UUIDs as a security control. They make enumeration harder, not impossible, and never substitute for an authorization check on every request.

The honest summary: auto-increment is the better default for a single, centralized database, and the smaller, faster key. UUIDs earn their cost the moment you go distributed, generate IDs outside the database, or put identifiers in front of users — and UUID v7 has quietly removed the old "but they kill insert performance" objection for most workloads. Match the key to where your IDs are born and who gets to see them, and the choice mostly makes itself.