Should I use UUIDs or auto-increment integers as primary keys?

What it still costs

A UUID is 16 bytes against a bigint's 8. That doubles the size of the primary key and of every foreign key and index entry that refers to it. In InnoDB and SQL Server, every secondary index also stores the clustering key, so the cost multiplies. Expect indexes to be noticeably bigger, and joins slightly slower, than with bigint. The difference is usually in the tens of percent, not the multiples you saw with v4.

Store UUIDs in a native type, never as text. char(36) takes 36+ bytes and compares character by character.

PostgreSQL example

-- PostgreSQL 18+
CREATE TABLE orders (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    customer_id bigint NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Integer alternative
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

Before PostgreSQL 18, generate v7 in the application (most languages have a library for it) or with the pg_uuidv7 extension.

The decision

• Single database, and IDs can come from the database? Use bigint identity. It's the smallest and fastest option, and nothing beats it.
• IDs generated outside the database (distributed services, offline clients, client-side optimistic creates), multi-region merges, or public IDs that shouldn't be guessable? Use UUIDv7 in a native column.
• UUIDv4? Only for tokens and secrets, where unpredictability is the point. Not as a clustered primary key on a large, write-heavy table.

A popular hybrid gets both: a bigint primary key for joins, plus a unique UUIDv7 public_id column that is the only ID the API exposes.

Checklist

1. The pain came from UUIDv4's randomness, not from UUIDs as such.
2. Use UUIDv7 when IDs must be created outside the database.
3. Always store UUIDs in a native 16-byte type.
4. Default to bigint when a single database hands out the IDs.

Postgres vs MySQL performance: which is faster, and when?

Explains by mechanism where InnoDB or PostgreSQL does less work for lookups, updates, joins, connections and replication, then shows how to benchmark your own workload with pgbench and sysbench.

#internals#mvcc#performance