UUID vs Auto-Increment ID: Choosing a Primary Key

The short answer

• bigint identity (auto-increment) is the most compact and fastest key when everything lives in one database and IDs are not exposed.
• UUIDv4 is unique everywhere, but its random order hurts indexes on large tables.
• UUIDv7 and ULID are unique everywhere and time-ordered, so they insert almost like auto-increment.
• A practical default: UUIDv7 for entities visible in your API and URLs, bigint for internal tables such as logs and join tables.

The options side by side

Index size and insert performance

Size. A UUID takes 16 bytes versus 8 for bigint. The difference multiplies across every index and every foreign key that references the table. Store UUIDs in a native type (uuid in PostgreSQL, BINARY(16) in MySQL), not as strings: the text form is more than twice as large and slower to compare.

Inserts. A B-tree index likes new values to arrive at the end:

• Auto-increment, UUIDv7 and ULID grow over time, so new rows are appended to the "right edge" of the index and hot pages stay in memory.
• UUIDv4 is random, so every insert lands at an arbitrary spot in the index. On large tables that means more page splits, more disk reads and a less effective cache.

The effect is stronger in MySQL InnoDB, where the primary key is clustered: the table itself is stored in key order.

Security: guessable IDs in URLs

With auto-increment, the URL /orders/1042 hints that /orders/1041 exists. That leads to two problems:

• Enumerating other people's objects. If the server forgets an authorization check, an attacker just increments the number. Note: what protects you is the authorization check, not the ID format. A random ID makes the attack harder but never replaces the check.
• Leaking business data. Two IDs a month apart let a competitor estimate how many orders you get.

UUIDv7 and ULID have their own quirk: they encode the creation time. Usually that is harmless, but if the moment a user signed up is sensitive, keep it in mind.

Merging databases and distributed systems

Auto-increment is unique only within one table in one database. Problems appear when:

• you need to merge data from two databases (two regions, absorbing another product) and the IDs collide;
• a mobile app works offline and creates records before syncing, so it needs an ID immediately without asking the server;
• services create related records in parallel and need to know IDs in advance.

Any kind of UUID solves this: it can be generated on the client, in a service or in the database without coordination.

How to choose

• One database, IDs never exposed, huge tables (events, logs, join tables): bigint generated always as identity.
• Public entities: users, orders, documents: UUIDv7.
• Offline clients and on-device ID generation: UUIDv7 or ULID.
• UUIDv4: when even the creation time must not leak and the table is not large enough for random inserts to matter.

An alternative is two identifiers: an internal bigint for JOINs and foreign keys, plus a separate public UUID with a unique index for the API. It saves space but complicates the code, since you always have to remember which ID is used where.

CREATE TABLE orders (
  id uuid PRIMARY KEY,          -- UUIDv7, generated by the app or the DB
  customer_id uuid NOT NULL REFERENCES customers (id),
  created_at timestamptz NOT NULL DEFAULT now()
);

Built-in UUIDv7 generation is not available in every database version. If yours lacks it, use a well-maintained library in the application.

Common mistakes

• Storing UUIDs in varchar(36).
• Using integer instead of bigint for growing tables and hitting the limit a few years later.
• Treating a random ID as a substitute for access checks.
• Sorting by UUIDv4 and expecting chronological order.