UUID vs auto-increment primary keys

The choice between UUID and auto-incrementing integer primary keys is one of the most-debated schema decisions in modern application development. The debate is older than most current developers' careers and has recycled through several technology generations — relational databases in the 1990s, web applications in the 2000s, distributed systems in the 2010s, and now distributed systems at much larger scale.

Most articles on the topic produce a balanced list of pros and cons and stop there, leaving the reader with the impression that the choice is genuinely 50/50. It usually isn't. For any given system, one of the two is meaningfully better than the other, and the question is which one and why. This article tries to make the decision concrete.

What each format optimises for

The fundamental difference is what each format is designed to be good at.

Auto-incrementing integers are designed for a single database to issue unique values cheaply, in order. The database holds a sequence, increments it on each insert, and returns the new value. Two properties follow naturally: values are small (typically 4 or 8 bytes) and they sort in insertion order. The cost is that the database must coordinate sequence generation, which means a single source of truth must exist for each sequence.

UUIDs are designed for any system, anywhere, to generate unique identifiers without coordinating with anything else. The space is large enough (122 random bits in v4, similar in v7) that the probability of two independently-generated IDs colliding is negligible for any practical workload. The cost is that values are larger (16 bytes) and, in the case of v4, randomly distributed rather than ordered.

Almost every difference between the two follows from this fundamental trade-off: small-and-coordinated versus large-and-uncoordinated.

The decision factors

Six factors usually drive this choice. They're listed roughly in order of how often they're the deciding factor.

1. Distributed generation

If your IDs need to be generated by multiple independent systems — application servers, mobile clients, IoT devices, batch import jobs — and you can't guarantee a single coordinator, UUIDs are the answer. Auto-incrementing integers require either centralised generation or coordinated sequence allocation, neither of which is operationally simple at scale.

If all IDs are generated by a single database (or a single primary in a primary-replica setup), auto-incrementing integers are fine on this axis.

This is the factor that tips most architectural decisions toward UUIDs in modern systems. Microservices, multi-region deployments, offline-first mobile applications, and distributed event-sourcing all push in the same direction: independent ID generation without coordination. For the full landscape of distributed-generation strategies — Snowflake, ULID, KSUID, and where each fits — see generating IDs in distributed systems.

2. Performance characteristics

This is where the conversation has changed substantially in the last two years and where most older articles are now out of date.

For storage: auto-incrementing integers win unambiguously. An 8-byte bigint is half the size of a 16-byte UUID. For a table with billions of rows and many indexes referencing the primary key, that storage difference compounds significantly.

For insert performance: the answer used to be "integers win by a lot" because UUID v4's random distribution caused B-tree index fragmentation. With UUID v7, that gap closes substantially. v7's time-ordered structure produces append-only insert patterns that look much like integer inserts to the index.

A representative benchmark on PostgreSQL inserting 10 million rows into a single table:

PostgreSQL benchmark inserting 10 million rows into a single table, comparing primary key types by p99 insert latency and final index size.
Primary key	Insert latency (p99)	Final index size
`bigserial`	0.18 ms	215 MB
`uuid` (v7)	0.22 ms	280 MB
`uuid` (v4)	0.41 ms	410 MB

The v7 vs bigserial gap is small enough that most applications won't notice it. The v4 vs bigserial gap is large enough to matter for write-heavy workloads.

For query performance: auto-incrementing integers compare slightly faster than UUIDs (4 or 8 bytes versus 16), and join performance correspondingly slightly better. The difference is rarely measurable in real applications — modern CPUs compare 16 bytes nearly as fast as 8 — but it exists.

For range scans by creation time: UUID v7 wins. Because v7 IDs cluster chronologically, a query like WHERE id BETWEEN x AND y for IDs from a known time window can use the primary key index efficiently. With either v4 or auto-incrementing integers, you typically need a separate index on created_at to support this access pattern.

3. Information leakage

Auto-incrementing integers leak information that UUIDs don't:

Total volume. Anyone with access to an ID knows roughly how many records exist. An order with ID 47,832 tells the customer something about the business's scale. Some businesses care; some don't.
Insertion order. Two consecutive IDs are sequential. This can be useful (debugging, support workflows) or harmful (a competitor can estimate growth by sampling IDs over time).
Enumerability. If your URLs include the ID — /orders/47832 — an attacker can enumerate by incrementing. Combined with broken authorisation, this is a common cause of data exposure.

UUID v4 leaks none of this. UUID v7 leaks creation time but not volume or sequential relationship.

For internal systems where IDs never reach external eyes, this factor is irrelevant. For public-facing systems where IDs appear in URLs, API responses, or webhook payloads, it's often the deciding factor — even when the performance and operational arguments would favour integers.

4. Debugging and operational ergonomics

Integers are easier to read, easier to type, easier to remember, and easier to compare visually. Support staff can quote a four-digit order number to a customer; nobody quotes a UUID over the phone.

This factor matters more than it sounds. Systems with high human interaction (admin tools, support workflows, debugging sessions) benefit substantially from human-readable IDs. Systems where IDs are mainly machine-to-machine (event payloads, microservice contracts) don't.

A common compromise is to use UUIDs as the canonical primary key and surface a separate, smaller, sequential display_id for human use. This is the "use both" pattern discussed below.

5. URL and API aesthetics

UUIDs are 36 characters. They make for long, ugly URLs. NanoIDs at 21 characters are better; auto-incrementing integers at 4–10 characters are best.

For public-facing URLs (especially permalinks, share URLs, anything users might paste into messages), this matters. For internal URLs (admin tools, internal APIs), it doesn't.

A common pattern: use a UUID as the primary key, expose a separate URL-friendly slug for public URLs. The slug doesn't have to be globally unique — it only needs to be unique within whatever scope determines URL collisions, which is often much smaller than the full table.

6. Multi-region and geographically-distributed data

If your data lives in multiple regions and any region can write, UUIDs are essentially required. Coordinating an auto-increment sequence across regions either requires a global coordinator (a latency bottleneck) or partitioned sequences (operationally complex).

UUIDs eliminate the coordination requirement entirely. Each region generates IDs independently; collisions don't happen at any practical scale.

The "use both" pattern

For systems that want most of the benefits of both, a common pattern is to use a UUID as the canonical primary key and a separate auto-incrementing integer as a secondary identifier. The integer serves human-facing purposes (URLs, support workflows, debugging) while the UUID serves machine-facing purposes (foreign keys, distributed generation, API payloads).

CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    order_number bigserial UNIQUE NOT NULL,
    -- ... other columns
);

The trade-offs:

You pay the storage and index cost of both
You have two identifiers for the same row, which adds a small amount of cognitive overhead
Joins from other tables use the UUID; URLs and support workflows use the integer

For systems where both factors matter — public-facing applications with significant human interaction and distributed components — this is often the right answer. The extra storage and indexing cost is real but typically modest, and the operational flexibility is substantial.

Migration paths

The question "we started with one, should we migrate to the other?" comes up regularly. The honest answer is almost always no.

Migrating primary keys is a substantial operation. Every foreign key reference must be updated, every index rebuilt, every cache invalidated, every application path that hard-codes the key type changed. For most systems the cost dramatically exceeds the benefit. Better strategies:

Adopt the new format for new tables only. Existing tables keep their existing format; new tables use whatever's currently the better choice. This is the lowest-cost path and works well for systems where individual tables are independent.
Use the "use both" pattern going forward. If you've been on integers and need UUIDs for distributed generation, add a UUID column to relevant tables and use it for new external interfaces. The integer remains the primary key; the UUID handles distribution.
Migrate only the specific table where the format is causing real problems. If a single write-heavy table is suffering from v4 fragmentation, migrating just that table to v7 is much cheaper than a system-wide migration.

The exception: if you're moving from a single-region to a multi-region deployment, you may genuinely need to migrate auto-incrementing IDs to UUIDs for tables that will be written from multiple regions. This is a significant project that needs careful planning, but it's sometimes unavoidable.

Decision guide

A short recommendation tree:

Use UUID v7 if:

IDs are generated by multiple independent systems
IDs appear in public-facing URLs or APIs
The table is write-heavy and you're on PostgreSQL or another database with good UUID support
You need geographic distribution with regional writes

Use UUID v4 if:

All the conditions above apply and IDs must not leak creation time
Privacy-sensitive identifiers where temporal information is sensitive

Use auto-incrementing integers if:

All IDs are generated by a single database
IDs are exclusively internal (never exposed to users)
Storage efficiency is a serious constraint
The table sees significant human interaction (support workflows, debugging)

Use the "use both" pattern if:

The system has significant exposure to both distributed generation needs and human-interaction needs
The storage and indexing cost of two identifiers is acceptable

What most modern systems actually choose

For new projects starting today, the default has shifted decisively toward UUID v7 with integer secondary IDs for human-facing surfaces where needed. The reasons are largely operational: most production systems end up distributed eventually, public-facing URLs are now the norm rather than the exception, and v7's performance characteristics removed the strongest historical argument against UUIDs.

That doesn't mean integers are wrong. Plenty of well-designed systems still use auto-incrementing integers and will continue to do so successfully. But the burden of proof has shifted — choosing integers today is choosing to limit your future options around distributed generation, and that limitation should be a conscious decision rather than a default.

For an internal admin tool with one database server and no external API, integers are still fine. For anything more ambitious, UUIDs (specifically v7) are usually the better starting point.

For deeper coverage of UUID v7 specifically, see UUID versions explained and UUID v7 in PostgreSQL — a practical guide. If you're also weighing UUID against a shorter, URL-friendly identifier, its companion comparison is NanoID vs UUID. Generate UUIDs and other identifiers in your browser with the UUID v7 generator and UUID v4 generator.