UUID vs Auto-Increment IDs: Which Primary Key Should You Use?

Every table needs a primary key, and there are two mainstream choices: an auto-incrementing integer (1, 2, 3…) or a UUID (0192f3a7-5c1e-7b2a-9d4f-3e8a1c6b7d20). Both work. They differ in size, speed, security and convenience — and the arrival of UUIDv7 has shifted the balance.

(Basics first: primary key vs foreign key.)

Auto-increment integers

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Pros:

• Small: 8 bytes for a bigint. Smaller indexes, smaller foreign keys, more rows in memory.
• Fast inserts: new IDs always go at the end of the index.
• Readable: "order 48213" is easy to say, type and search for.
• Naturally ordered by creation.

Cons:

• Guessable. /invoices/1042 invites someone to try /invoices/1041. That's only a problem if your access control is broken — but it often is.
• Leaks business information. Sign up today as user #8,412 and next month as #9,100, and a competitor knows your growth rate.
• Generated by the database. You don't know an ID until you insert the row, which complicates creating related records offline or on the client.
• Merging data is painful. Two databases both have a user #5.

Use bigint, not int — a 32-bit integer runs out at about 2.1 billion, and migrating a primary key column on a big table later is painful. (Postgres migrations on large tables.)

UUIDs

A UUID is a 128-bit identifier, written as 36 characters, designed so anyone can generate one anywhere without collisions.

Pros:

• Not guessable (for random versions).
• Generate anywhere — app server, browser, mobile app — before touching the database.
• Globally unique, so merging data, syncing between systems and multi-region setups are easy.
• Don't leak counts.

Cons:

• Twice the size (16 bytes), in the primary key and in every foreign key and index that references it.
• Random UUIDs hurt index performance (see below).
• Unwieldy for humans to read or say.

The UUIDv4 performance problem

The classic UUID is version 4 — completely random. Database indexes (B-trees) like new values to arrive in order. Random keys land all over the index, so inserts touch many different pages, fill them unevenly, and cause more disk writes and cache misses. On large, insert-heavy tables, that's a measurable slowdown and larger indexes.

UUIDv7: the best of both

UUIDv7 (standardised in 2024) starts with a timestamp, followed by random bits. So:

• new IDs are roughly sequential — inserts behave much like auto-increment,
• still globally unique and generatable anywhere,
• still not guessable in practice (the random part is large),
• sortable by creation time.

One trade-off: a UUIDv7 reveals when the record was created. Usually harmless; occasionally sensitive.

Generating UUIDv7

• PostgreSQL 18+ has a built-in function:

  id uuid PRIMARY KEY DEFAULT uuidv7()
  

• Older Postgres: generate in your app with a library (the popular uuid npm package supports v7), or use an extension. gen_random_uuid() is built in from Postgres 13 but produces v4.

• Most languages have UUIDv7 libraries now.

Alternatives you'll see

• ULID — similar idea to UUIDv7 (time-ordered, random), encoded as a shorter 26-character string. Predates v7.
• nanoid / cuid2 — short, random, URL-friendly string IDs. Good for public IDs; stored as text, so larger and slower as primary keys.

The hybrid: internal ID + public ID

A common, practical pattern:

CREATE TABLE invoices (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- internal, joins
  public_id uuid   NOT NULL DEFAULT uuidv7() UNIQUE,          -- in URLs and APIs
  ...
);

Joins and foreign keys use the compact integer; URLs and APIs expose only public_id. You get small indexes and unguessable, non-leaking external identifiers. The cost is two columns and remembering which to use where.

How to choose

• Simple app, single database, IDs mostly internal: bigint identity. Add proper access control (you need it regardless).
• IDs appear in URLs/APIs, or records are created offline / in several systems: UUIDv7.
• Want both compactness and opaque public IDs: the hybrid.
• Avoid UUIDv4 as a primary key on large, insert-heavy tables if you can use v7.

Whatever you pick, be consistent across tables — mixed ID types make joins and code more confusing.

The summary

• Auto-increment: small and fast, but guessable and leaks counts. Use bigint.
• UUIDv4: unique and opaque, but random keys slow down indexes.
• UUIDv7: time-ordered UUIDs — unique, generatable anywhere, index-friendly. Built into Postgres 18 as uuidv7().
• The hybrid (integer internally, UUID publicly) is a solid compromise.
• Random IDs don't replace access checks.

---

EasySpawn provisions PostgreSQL on every server, so Claude Code can test schema choices like these against real data — and migrate them safely with daily backups behind you. See how it works or join the waitlist.

Related: How to Design Your First Database · Database Indexes · API Pagination · Postgres vs MySQL