UUID vs Auto-Increment IDs: Which Is Better for Databases?

Q: What Auto-Increment Gets Right

**Insert performance is hard to beat.** Each new row appends to the end of the B-tree index. No page splits, no fragmentation, perfect cache locality. If you're doing 10,000 inserts per second, auto-increment will handle it with minimal overhead. **Storage is compact.** A `BIGINT` is 8 bytes. A `UUID` is 16 bytes. Over 100 million rows, that's 800 MB of extra storage just for the primary key column. Add the index (which stores the key again) and foreign key references throughout your schema, and the difference compounds. **Debugging is easier.** Finding `ORDER #15382` in logs and mentally linking it to your mental model of the data is faster than decoding `550e8400-e29b-41d4-a716-446655440000`. When you're firefighting at 2 AM, readable IDs genuinely help. **No library dependencies.** You don't need `uuid`, `uuid6`, `pg_uuidv7`, or any other package. The database handles it with native functionality that's been stable for decades.

Q: What Auto-Increment Gets Wrong

**It doesn't scale horizontally.** Two databases running independently produce overlapping IDs. Solutions exist (range allocation, sequence servers, Snowflake IDs) but they all add complexity that UUIDs avoid by design. **It leaks information.** `GET /api/users/15382` tells an attacker the approximate number of users in your system and invites enumeration. Sequential IDs make scraping trivially easy. For public-facing APIs, this alone is reason enough to consider UUIDs. **Migration is painful.** If you ever need to merge two databases or move data between environments, primary key collisions are guaranteed. You'll write migration scripts that remap every foreign key reference. I've done this on a production database and it's not an experience I'd wish on anyone. --- ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT NOT NULL ); ``` UUIDs shift ID generation from the database to the application layer. This one architectural change has ripple effects throughout your system.

Q: What UUIDs Get Right

**Global uniqueness without coordination.** Every service, every instance, every container can generate IDs independently. No central sequencer, no database round-trip just to get the next ID, no race conditions. **Safe data merging.** Importing data from an acquisition, combining staging with production, restoring a partial backup -- no primary key conflicts. The identifiers were unique when they were created, wherever they were created. **No information leakage.** UUIDs don't reveal dataset size, insertion order, or growth rate. For SaaS products where competitors might try to reverse-engineer your metrics from public IDs, this matters. **Offline-first works naturally.** A mobile app that generates records offline and syncs later can't use auto-increment. It needs IDs that are unique before the sync happens. UUIDs are built for this. If you're working with JWT tokens in distributed auth flows, you've seen this pattern before -- the [JWT decoder](/jwt-decoder.html) reveals how JWTs embed unique identifiers (often UUIDs) directly in the token payload, avoiding a database lookup for every request.

Q: Does UUID v7 work with PostgreSQL's built-in UUID type?

Yes. PostgreSQL's `UUID` type stores 128-bit values. UUID v7 is 128 bits. The `UUID` type doesn't care about the version -- it stores whatever 16 bytes you give it. --- If you're designing a schema and want to test both approaches, the [UUID generator](/uuid-generator.html) lets you generate bulk UUID v4, v7, and v1 values in multiple output formats -- JSON arrays, SQL `IN` clauses, CSV-ready lists. Combine that with a local PostgreSQL instance, and you can benchmark your actual workload before choosing an ID strategy.

I've migrated databases in both directions. I've converted auto-increment IDs to UUIDs when a monolith was split into microservices. I've also moved from UUIDs back to auto-increment when a team over-engineered a two-table internal tool. Neither decision is inherently right. What matters is understanding the tradeoffs before your table hits 10 million rows.

The short version: auto-increment IDs are faster, smaller, and simpler. UUIDs are more flexible, distributed-friendly, and future-proof. The question is which matters more for your system.

Auto-Increment IDs: The Default That Works

An auto-increment (or serial) primary key is the simplest possible ID strategy:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

Insert a row, get 1. Insert another, get 2. The database handles everything.

What Auto-Increment Gets Right

Insert performance is hard to beat. Each new row appends to the end of the B-tree index. No page splits, no fragmentation, perfect cache locality. If you're doing 10,000 inserts per second, auto-increment will handle it with minimal overhead.

Storage is compact. A BIGINT is 8 bytes. A UUID is 16 bytes. Over 100 million rows, that's 800 MB of extra storage just for the primary key column. Add the index (which stores the key again) and foreign key references throughout your schema, and the difference compounds.

Debugging is easier. Finding ORDER #15382 in logs and mentally linking it to your mental model of the data is faster than decoding 550e8400-e29b-41d4-a716-446655440000. When you're firefighting at 2 AM, readable IDs genuinely help.

No library dependencies. You don't need uuid, uuid6, pg_uuidv7, or any other package. The database handles it with native functionality that's been stable for decades.

What Auto-Increment Gets Wrong

It doesn't scale horizontally. Two databases running independently produce overlapping IDs. Solutions exist (range allocation, sequence servers, Snowflake IDs) but they all add complexity that UUIDs avoid by design.

It leaks information. GET /api/users/15382 tells an attacker the approximate number of users in your system and invites enumeration. Sequential IDs make scraping trivially easy. For public-facing APIs, this alone is reason enough to consider UUIDs.

Migration is painful. If you ever need to merge two databases or move data between environments, primary key collisions are guaranteed. You'll write migration scripts that remap every foreign key reference. I've done this on a production database and it's not an experience I'd wish on anyone.

UUIDs: The Distributed Systems Default

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL
);

UUIDs shift ID generation from the database to the application layer. This one architectural change has ripple effects throughout your system.

What UUIDs Get Right

Global uniqueness without coordination. Every service, every instance, every container can generate IDs independently. No central sequencer, no database round-trip just to get the next ID, no race conditions.

Safe data merging. Importing data from an acquisition, combining staging with production, restoring a partial backup -- no primary key conflicts. The identifiers were unique when they were created, wherever they were created.

No information leakage. UUIDs don't reveal dataset size, insertion order, or growth rate. For SaaS products where competitors might try to reverse-engineer your metrics from public IDs, this matters.

Offline-first works naturally. A mobile app that generates records offline and syncs later can't use auto-increment. It needs IDs that are unique before the sync happens. UUIDs are built for this.

If you're working with JWT tokens in distributed auth flows, you've seen this pattern before -- the JWT decoder reveals how JWTs embed unique identifiers (often UUIDs) directly in the token payload, avoiding a database lookup for every request.

What UUIDs Get Wrong

Storage overhead. 16 bytes vs 8 bytes per key. Over millions of rows with foreign keys and indexes, this adds up to gigabytes of extra storage and memory pressure.

UUID v4 destroys index locality. Random inserts cause random page splits. At scale, this is the biggest performance downside. UUID v7 fixes this by embedding a timestamp, which restores sequential insert behavior. For the full explanation, read UUID v4 vs v7.

UUIDs are ugly in URLs and logs. 550e8400-e29b-41d4-a716-446655440000 takes up 36 characters in every log line, every URL, every debug output. You get used to it, but it's objectively less ergonomic than 15382.

String comparison is slower than integer comparison. Databases and programming languages compare integers at the CPU level. UUIDs (even in binary form) require byte-by-byte comparison. For primary key lookups, the difference is measurable but usually not dominant. For ORDER BY id on large result sets, it can matter.

Database Performance: The Numbers That Matter

Here's what I've observed running comparable workloads on PostgreSQL 16:

Scenario	BIGINT	UUID v4	UUID v7
Storage per row (PK only)	8 bytes	16 bytes	16 bytes
Index size (10M rows)	~280 MB	~420 MB	~350 MB
Insert latency (p50)	< 1ms	2-5ms	< 1ms
Insert latency (p99)	1-2ms	15-40ms	2-8ms
Point lookup (p50)	< 0.5ms	< 0.5ms	< 0.5ms
Range scan (100K rows)	40ms	85ms	55ms

Point lookups (fetching a row by primary key) are nearly identical across all three. The index structure is the same B-tree regardless of key type; walking it to find a single key is O(log n) and fast.

Range scans and inserts are where the differences emerge. UUID v4's random order fragments the index, making range scans touch more pages and inserts trigger more page splits. UUID v7 nearly closes the gap with BIGINT on both metrics.

Security: Not a Primary Concern, But Worth Knowing

UUIDs are harder to enumerate than auto-increment IDs, but this is a mild defense-through-obscurity, not a security control:

Auto-increment: GET /api/users/1, /api/users/2, /api/users/3
                → Trivially enumerable

UUID:           GET /api/users/550e8400-...
                → Can't guess the next ID

A determined attacker with access to one UUID still can't predict others (for v4 or v7). But UUIDs don't replace authentication and authorization. An unauthenticated request to /api/users/<uuid> should still be rejected if the caller lacks permission to view that user.

For password reset tokens, session identifiers, or anything security-critical, use dedicated cryptographic tokens -- not UUIDs. Our password generator can create cryptographically secure random strings suitable for tokens.

When Auto-Increment Is the Smarter Choice

Use auto-increment IDs when:

You have a single database and no plans to shard. A monolith with one PostgreSQL instance doesn't need distributed ID generation. Keep it simple.

The application is internal. Admin dashboards, internal tools, reporting databases -- nobody's enumerating IDs from outside, so UUIDs provide little benefit.

You need human-readable references. Invoice numbers, ticket IDs, order confirmation codes. Customers read these. #2026-15382 is better than a UUID.

Write throughput is extreme. If you're doing 50K+ writes per second on a single table, BIGINT's 8-byte key size and perfect sequential locality are hard to argue with. (Though UUID v7 gets close.)

You're using MySQL with InnoDB clustered indexes. InnoDB physically orders rows by primary key. UUID v4 primary keys scatter rows randomly across the entire table. This is the worst-case scenario for UUIDs. Either use UUID v7 or stick with auto-increment.

When UUIDs Are the Smarter Choice

Use UUIDs when:

You have (or plan to have) multiple services generating records independently. Microservices, event-driven architectures, CQRS, offline-capable mobile apps -- all benefit from decentralized ID generation.

You expose IDs in APIs and care about enumeration. If competitors or users can see resource IDs, UUIDs provide a baseline level of obscurity that sequential IDs can't match.

You might need to merge databases or migrate between providers. Acquiring another company, moving from self-hosted to managed, consolidating environments -- UUIDs make data merging a non-issue.

You're building a SaaS product that could scale. The cost of switching from auto-increment to UUIDs later (migration scripts, foreign key remapping, API versioning) is far higher than starting with UUIDs now.

UUID v7: The Best of Both Worlds?

UUID v7 combines the global uniqueness of UUIDs with the sequential insert behavior of auto-increment. For new projects in 2026, it's the default I recommend unless you have a specific reason not to.

-- New project default: UUID v7
CREATE TABLE orders (
    id UUID PRIMARY KEY,           -- generated as UUID v7 at app layer
    customer_id UUID NOT NULL,
    amount_cents INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

The tradeoff vs BIGINT is storage (16 vs 8 bytes) and readability. The advantage is architectural flexibility -- if you later split this table across shards or migrate to a distributed setup, the ID strategy doesn't need to change.

FAQ

Are UUIDs slower than auto-increment IDs?

UUID v4 inserts are slower due to random index placement. UUID v7 inserts are comparable to auto-increment because of time-ordered key values. Point lookups are equally fast for all three.

Should I use UUIDs as primary keys in MySQL?

Only with UUID v7 and BINARY(16) storage. UUID v4 on InnoDB clustered indexes is the worst-case combination -- random row placement causes massive fragmentation. If you must use MySQL with UUIDs, use v7.

How much storage do UUIDs waste compared to BIGINT?

8 bytes per row for the primary key, plus another 8 bytes per foreign key reference, plus index overhead. On a 10-million-row table with three foreign keys referencing it, the difference is roughly 400 MB. Whether that's significant depends on your infrastructure budget.

Can I convert auto-increment IDs to UUIDs on an existing production table?

Yes, but it's a multi-day project with downtime considerations. You'll need to add a new UUID column, backfill values, update foreign keys, switch application code, and eventually drop the old integer column. Plan for it during a maintenance window, not during an incident.

Why don't all databases use UUIDs if they're better for distributed systems?

Because most databases aren't distributed. A single PostgreSQL instance with 50 GB of data doesn't need globally unique identifiers. The extra storage, memory pressure, and (for v4) insert overhead aren't justified. UUIDs solve distributed coordination problems. If you don't have those problems, auto-increment is simpler.

Does UUID v7 work with PostgreSQL's built-in UUID type?

Yes. PostgreSQL's UUID type stores 128-bit values. UUID v7 is 128 bits. The UUID type doesn't care about the version -- it stores whatever 16 bytes you give it.

If you're designing a schema and want to test both approaches, the UUID generator lets you generate bulk UUID v4, v7, and v1 values in multiple output formats -- JSON arrays, SQL IN clauses, CSV-ready lists. Combine that with a local PostgreSQL instance, and you can benchmark your actual workload before choosing an ID strategy.