UUID vs Sequential ID: When to Use Which (v4, v7 Explained)

Auto-increment integer IDs were the default primary key for decades. UUIDs replaced them in many modern systems for good reasons — and some bad ones. Knowing when to use which (and which UUID version) saves real database pain later.

Auto-increment IDs — the classic

A column like id BIGSERIAL PRIMARY KEY generates 1, 2, 3, 4… for each new row. Simple, fast, small (8 bytes), and the database does it for you.

Pros:

• Compact storage (8 bytes vs 16 for UUID).
• Naturally sortable — newer rows have higher IDs.
• Excellent index locality — new rows go to the end of the B-tree index.
• Easy to communicate ("issue #2487").

Cons:

• Reveals row count. Customer #41 implies you have ~41 customers. Bad in B2B and public APIs.
• Reveals growth rate. Counting two IDs a week apart tells competitors how fast you sign up users.
• Conflict on distributed systems. Two databases generating their own sequences collide on merge.
• Race conditions on insert — need to lock the sequence which becomes a bottleneck at scale.
• Predictable. Malicious users can enumerate your URLs (/orders/1, /orders/2…).

UUIDs — the modern default

A UUID (Universally Unique Identifier) is a 128-bit value, written as 36 characters with hyphens:3f8b2e6c-9d12-4f7a-b5d8-3a8c6e1f9e02. The math guarantees uniqueness without coordination — two systems generating UUIDs independently have essentially zero chance of collision.

Pros:

• Generate anywhere — client, server, batch job — without coordination.
• Doesn't reveal row count or growth rate.
• Not enumerable — attackers can't guess valid IDs.
• Merge databases without conflict.
• Embed in URLs without exposing internal details.

Cons:

• 16 bytes instead of 8 — doubles primary key size, affects index size dramatically at scale.
• Not naturally sortable (random UUIDs).
• Less human-friendly. "Hi, my issue is 3f8b2e6c-9d12-…" doesn't work in support tickets.
• Insert performance hit on traditional B-tree indexes (more on this below).

The B-tree problem with random UUIDs (v4)

B-tree indexes (used by Postgres, MySQL, SQLite for primary keys) are most efficient when new keys are appended to the end. Auto-increment IDs do exactly this — new keys always go to the rightmost leaf page.

UUID v4 is random. Every insert hits a different page in the index, forcing the database to:

• Read the page from disk if it's not cached.
• Insert the key, possibly causing a page split.
• Write the modified page back.

At scale (millions of rows, hundreds of inserts/second), this can be 5-10x slower than auto-increment. Index size also bloats from page fragmentation.

UUID v7 — solving the B-tree problem

UUID v7 prefixes the random bits with a 48-bit Unix millisecond timestamp. The first 6 bytes of a v7 UUID reflect the moment it was generated.

This means v7 UUIDs are sortable by creation time and inserts land near the end of the B-tree index — just like auto-increment IDs.

You get the privacy and uniqueness benefits of UUIDs and the insert performance of auto-increment. In 2026, v7 is the recommended UUID version for database primary keys.

UUID versions at a glance

The decision tree

1. Is this an internal-only ID never exposed to users or other systems? Auto-increment integer. Simple, fast.
2. Will the ID appear in URLs, APIs or share with third parties? UUID. Don't reveal row counts.
3. Database primary key? UUID v7. Get UUID privacy with auto-increment-like insert performance.
4. Need to generate IDs without hitting the database first? UUID (any version).
5. Merging databases or multi-region writes? UUID required.
6. Random API token or single-use code? UUID v4 (or a custom secure random string — see the Password Generator).

Hybrid approach — use both

Many production systems use both:

• Internal integer ID as the primary key for performance and joins.
• UUID as a separate column for external references — what appears in URLs, APIs and emails.

Example table:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,           -- internal
    public_id UUID UNIQUE NOT NULL,     -- external
    customer_id BIGINT REFERENCES customers(id),
    ...
);
CREATE INDEX ON orders(public_id);

The integer is used for foreign keys (small, fast). The UUID is exposed externally and never leaks row counts.

Common mistakes

• Storing UUIDs as text instead of native type. Postgres has a UUID type — use it. Storing as VARCHAR(36) takes 2.25x more space and is slower to compare.
• Using v1 in new systems. v1 includes the MAC address — a privacy leak.
• Generating UUIDs in MD5 form. A UUID-shaped hash is not a UUID. Functionally similar, but tools and types expect proper versioning bits.
• Using v4 as a primary key on high-write tables. Slower inserts, worse index locality. Use v7.
• UUIDs everywhere by reflex. If a column is never exposed and only joined, integers are still fine.

Performance numbers (approximate)

On a Postgres table with 50 million rows, inserting 100k more:

• Auto-increment BIGINT: 2.1 seconds, index size 1.0 GB.
• UUID v7: 2.4 seconds, index size 1.4 GB.
• UUID v4: 4.8 seconds, index size 1.8 GB (fragmentation hurts).

v7 closes most of the gap. v4 is genuinely slower at scale — not a reason to never use it, but a reason to use v7 by default for primary keys.

The summary

• New system, primary keys: UUID v7.
• API token, random identifier: UUID v4.
• Already on auto-increment and it's working? Don't migrate just because UUIDs are fashionable.
• Exposing IDs externally? Use UUIDs (v4 or v7).
• Performance critical and internal only? Auto-increment is still fine.