UUID vs Auto-Increment Primary Key

Almost every new table starts with the same fork in the road: should the primary key be a plain auto-increment integer, or a UUID? Most write-ups jump straight into comparing UUID flavors (v4 versus v7) or rival ID schemes (UUID versus ULID versus CUID), but they skip the question that actually decides your architecture. An auto-increment integer and a UUID solve the same job in opposite ways, and the performance gap between them is almost entirely a function of which database engine you run. Get the engine nuance right and the decision becomes straightforward.

What each one actually gives you

An auto-increment integer (MySQL AUTO_INCREMENT, PostgreSQL GENERATED ... AS IDENTITY or a SERIAL/sequence) is a small, monotonically increasing number. It is compact (4 bytes for an INT, 8 for a BIGINT), trivially sortable, and human-friendly. Its weaknesses are well known: the value is only unique within one table on one server, so it does not survive sharding or offline generation, and it leaks business information. A competitor seeing /orders/1042 can read off roughly how many orders you have processed, and can enumerate every record by counting.

A UUID is a 128-bit (16-byte) identifier that you can generate anywhere without coordinating with the database. The standard random form, UUIDv4, is defined in RFC 9562 (which superseded RFC 4122 in May 2024). Because clients can mint UUIDs before an insert, you can build object graphs in memory, merge data from multiple sources, and shard without an ID-allocation server. The cost is size, lack of natural ordering, and a more subtle problem that depends entirely on your storage engine.

The engine nuance most posts get wrong

The claim "UUID primary keys are slow" is true on some engines and overstated on others, because the damage is caused by randomness, not by the UUID itself. A random UUIDv4 has no relationship to the row before it, so each insert lands at an unpredictable point in the index instead of appending neatly to the end.

On a clustered-index engine the effect is severe. In MySQL's InnoDB, the primary key is the table: rows are physically stored inside the B-tree of the primary key. Insert rows in random key order and InnoDB must scatter them across the whole tree, causing frequent page splits that leave leaf pages roughly half full instead of nearly full. Percona has documented how this fragments the actual row storage, lowers the cache hit ratio, and inflates on-disk size. There is a second, less obvious tax: every InnoDB secondary index stores the primary key in its leaf nodes, so a fat 16-byte random PK bloats every other index on the table too. SQL Server behaves similarly, since its default clustered index also orders rows by the key.

PostgreSQL is different by design. It uses heap storage: rows live in an unordered heap and the primary key is just one more secondary B-tree pointing into it. A random UUID never reorders the physical table, so the worst InnoDB symptom simply does not occur. The primary key index itself still suffers page splits and poor cache locality (recent rows scatter across the whole index rather than clustering at the end), but the blast radius is smaller. This is exactly why blanket "never use UUIDs" advice misfires on Postgres.

UUIDv7 changes the math

RFC 9562 also standardized UUIDv7, which puts a 48-bit Unix-millisecond timestamp in the most significant bits, followed by random data. Because the time prefix leads, a lexical sort of UUIDv7 values is also a chronological sort. New IDs therefore append near the right edge of the index just like an auto-increment integer, which restores sequential insert locality and eliminates most of the fragmentation problem on both InnoDB and PostgreSQL. PostgreSQL 18 added a native uuidv7() generator, and you can also generate time-ordered UUIDv7 (or ULID) values in the browser to test layouts with our UUID generator and ULID generator. If you want the full version breakdown, see our guide on UUIDv4 vs UUIDv7.

The trade-off: a v7 UUID embeds its creation time, so anyone holding the ID learns approximately when the row was created. That is fine for internal keys but a consideration for public, guessable-timing-sensitive identifiers.

The hybrid pattern

You do not have to choose. A widely used pattern keeps a sequential surrogate as the real primary key and exposes a UUID externally:

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id   UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Internally, joins and foreign keys use the narrow, sequential id, so the clustered index (or Postgres index) stays compact and append-friendly. Externally, your API and URLs only ever show public_id, so you leak no row counts and never expose an enumerable integer. The price is one extra column and a unique index to maintain. On InnoDB this is often the best of both worlds; on Postgres, a plain UUIDv7 primary key is usually simple enough that the hybrid is optional.

Decision table by scenario

The short version: pick auto-increment unless you have a concrete reason not to. When you do need UUIDs, prefer the time-ordered UUIDv7 over random UUIDv4, and reach for the hybrid pattern on clustered-index engines where random keys hurt most.

Frequently Asked Questions