Should your relational database primary key be a UUID or a SERIAL/IDENTITY auto-increment integer? Let’s do a deep, practical, systems-level comparison.
Choosing a primary key is one of those “small” schema decisions that quietly affects performance, replication strategy, sharding, API design, caching, privacy, and even incident recovery.
This post compares two common choices for surrogate primary keys in SQL databases:
SERIAL, BIGSERIAL, IDENTITY, AUTO_INCREMENT)UUID type in Postgres, BINARY(16) / CHAR(36) patterns in MySQL)I’ll focus on what matters in practice: storage, indexing, write amplification, locality, distributed systems, security implications, and operational ergonomics.
SERIAL/BIGSERIAL, modern: GENERATED {ALWAYS|BY DEFAULT} AS IDENTITYAUTO_INCREMENTThese generate an increasing integer (or bigint) per row.
A 128-bit identifier, usually rendered as xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
Common versions:
In relational DBs, UUIDs can be stored:
UUID type (Postgres)BINARY(16) (recommended for MySQL when using UUIDs)CHAR(36) (works, but bigger + slower)If you boil everything down:
Neither is “always better”. The right choice depends on how your system is deployed and how identifiers flow through it.
| Dimension | SERIAL/IDENTITY (int/bigint) | UUID (v4 random) | UUID (v7/time-ordered) |
|---|---|---|---|
| Storage for PK | 4 bytes (int) / 8 bytes (bigint) | 16 bytes (binary) + type overhead | 16 bytes (binary) + type overhead |
| Index size | Small | Larger | Larger |
| Insert locality in B-tree | Excellent (append-ish) | Poor (random) | Good (mostly increasing) |
| Write amplification / page splits | Low | Higher | Lower than v4 |
| Generation location | Usually DB | App, DB, anywhere | App, DB, anywhere |
| Multi-writer / offline creation | Hard | Easy | Easy |
| Sharding / merging datasets | Harder | Easier | Easier |
| Public exposure (enumeration risk) | High unless mitigated | Lower | Lower |
| Debuggability / ergonomics | Great (short) | Mixed (long) | Mixed (long) |
| Cross-system uniqueness | Needs coordination | Built-in | Built-in |
Note: “UUID (v4 random)” is the worst-case for index locality. If you want UUID benefits without killing write patterns, consider UUIDv7 (or ULID-like time-ordered IDs).
Primary keys aren’t just a column. They often sit inside multiple indexes.
In most relational engines, secondary indexes store:
So if your PK is large, every secondary index entry becomes larger.
Practical effect:
Most SQL databases use B-tree indexes for primary keys.
Increasing keys mean inserts happen near the “right edge” of the B-tree.
Random keys insert all over the tree:
This difference becomes very visible at scale (high insert rates, large tables, constrained IO).
UUIDv7 is time-ordered. Inserts tend to cluster, closer to append-like behavior while staying globally unique.
If your motivation for UUID is distributed generation (not “randomness”), UUIDv7 is usually a stronger choice than UUIDv4.
With auto-increment IDs, one place is effectively responsible for ID allocation:
This is fine for single-primary setups. It gets trickier when:
You can solve it with sequences-per-shard or ID blocks, but that’s extra design work.
UUIDs let every writer generate IDs safely:
If you anticipate multi-writer or eventual multi-region writes, UUIDs are often operationally simpler.
Sharding isn’t only for huge companies. Even modest systems shard for:
If shard A and shard B both generate id=1..N, merging datasets or moving tenants between shards becomes awkward.
Solutions exist:
UUIDs don’t collide by design. When you:
…you don’t have to worry about PK renumbering or range collisions.
Auto-increment IDs are predictable. That creates risks:
GET /orders/10001 → try /orders/10002You can mitigate with authorization, of course—but predictable IDs increase the blast radius of mistakes.
Common pattern in mature systems:
This gives performance internally and safety externally.
This is underrated.
If your team spends a lot of time debugging production issues with ad-hoc SQL, bigint IDs can materially improve day-to-day speed.
Because inserts hit the end of the index, contention can appear on high concurrency workloads (depending on engine internals and isolation level). Modern engines handle this reasonably well, but it can show up.
Randomness spreads insert locations, which can reduce certain right-edge contention patterns—but at the cost of page splits and cache locality.
In practice:
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
If you want the DB to generate UUIDs:
-- Depending on your setup, you might use pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
If you generate UUIDs in the application (Node.js), you’d insert explicitly.
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email)
);
Storing UUID as CHAR(36) works but is larger and slower. BINARY(16) is typically better.
CREATE TABLE users (
id BINARY(16) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email)
);
You then convert at the app boundary (store as 16 bytes, display as string).
Concrete example:
users, orders, payments, moderate scale, lots of joins, heavy analytics read queries.Concrete example:
If you want UUID benefits without the worst index fragmentation behaviors:
Many teams end up here:
BIGINT (fast, small, index-friendly)UUID (opaque, safe in URLs)Example:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id UUID NOT NULL DEFAULT gen_random_uuid(),
user_id BIGINT NOT NULL,
amount_cents INT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE (public_id)
);
Your API uses public_id:
GET /orders/{public_id}Your internal joins use id:
This pattern is especially good if you want safe external references but don’t actually need distributed minting of the primary key.
Not always—but UUIDv4 PKs commonly cause slower inserts at scale due to index locality and larger indexes. UUIDv7 can narrow the gap.
Security comes from authorization and access control, not ID format. But predictable IDs do increase risk when a bug slips in.
Microservices require clear boundaries and contracts. UUIDs can help with distributed creation and cross-system merging, but they’re not mandatory.
If you want a pragmatic default for most SQL-backed web apps:
BIGINT IDENTITY (or AUTO_INCREMENT) as the primary key.public_id UUID if the ID leaks into URLs, logs, or integrations.Choose UUID as the primary key when distributed generation or cross-database merges are first-class requirements.
If choosing UUID PKs:
UUID type in Postgres, BINARY(16) in MySQL)SERIAL/IDENTITY keys are excellent for performance and simplicity in single-writer relational systems. UUIDs shine when you need coordination-free global uniqueness, safer public exposure, and easier cross-system merging.
The most robust real-world approach is often a hybrid: bigint PK for the database, UUID for the outside world.
I hope this was useful. Feel free to drop your comments below.
- Ayush 🙂