UUID vs Auto-Increment ID: Which Should You Use?

Side-by-Side Comparison

Where Auto-Increment Wins

• Speed and size. Smaller keys mean smaller indexes and foreign keys, more rows per page and better caching.
• Natural ordering. IDs sort in creation order.
• Simplicity. The database handles everything, and IDs are easy to discuss ("order 48213").

Where UUIDs Win

• Distributed creation. Microservices, mobile apps and offline clients can create IDs without asking the database, which also simplifies optimistic UIs and event-driven designs.
• Merging and replication. Combining tenants, shards or environments doesn't produce ID clashes.
• Privacy of counts. A sequential ID in a URL tells competitors and users roughly how many orders or users you have.
• Enumeration resistance. With sequential IDs, changing /invoice/1001 to /invoice/1002 is a classic way people stumble on other records when authorisation checks are missing.

On that last point: UUIDs make guessing harder, but they're not access control. Every request must still check that the user is allowed to see the record.

The Performance Question

Random UUID v4 keys insert at random positions in a B-tree index, causing page splits and fragmentation as tables grow, especially where the primary key is also the clustered index (SQL Server, MySQL InnoDB). UUID v7, which starts with a millisecond timestamp, inserts near the end of the index like an integer would, removing most of the penalty while keeping distributed generation. UUID v4 vs UUID v7 compares them, and GUID as a primary key covers SQL Server specifics.

Storage still matters at scale: 16-byte keys repeated across foreign keys and secondary indexes add up compared with 8-byte BIGINTs.

The Hybrid Approach

A common, pragmatic design:

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id  uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  created_at timestamptz NOT NULL DEFAULT now()
);

(PostgreSQL syntax.) Joins and foreign keys use the compact integer id; APIs and URLs use public_id. You get fast internal joins and non-guessable public identifiers. The cost is an extra column and index, and you must be careful never to leak the internal ID in public responses.

Decision Checklist

Choose auto-increment if:

• One database creates every record.
• IDs never appear in URLs or public APIs, or revealing counts doesn't matter.
• Raw performance and storage are top priorities.

Choose UUID (v7 preferred) if:

• Clients or several services create records.
• You'll merge or replicate across databases.
• You want IDs before the database round-trip.

Choose hybrid if:

• You want integer performance internally and opaque IDs externally.

Generating UUIDs for Your Design

For public IDs and test fixtures, the UUID v4 generator creates random UUIDs in bulk in your browser. If you're leaning towards UUID primary keys, try the UUID v7 generator to see how time-ordered values share an increasing prefix. And if uniqueness worries you, UUID collision probability puts the risk in perspective.

Exposing IDs Safely Either Way

Whichever you choose, a few practices keep IDs from becoming a security problem:

• Always check authorisation on every request, regardless of how hard an ID is to guess.
• Avoid leaking internal IDs in error messages, logs shared with third parties or analytics.
• Rate-limit endpoints that look up records by ID, to slow down enumeration attempts.
• Use separate public IDs if you ever need to change internal keys without breaking links.

Migration Paths

• Integer to UUID: add a UUID column, backfill it, expose it in APIs, and keep the integer key internally. This avoids rewriting foreign keys.
• UUID v4 to v7: change the default generator; existing values stay valid.
• UUID to integer: rarely worth it; consider adding an integer surrogate key for internal joins instead.

Summary

Integers are compact, fast and simple; UUIDs are distributed, private and merge-friendly. Use UUID v7 if UUIDs are your keys, or combine an integer key with a UUID public ID for the best of both.