Developer Guide

UUID vs Auto-Increment IDs: Which Primary Key to Use

By ToolFlic Expert Team Updated 9 October 2026 1016 words

Picking a primary key is a genuine trade-off, not a style choice. An auto-increment integer is tiny, ordered and cheap for a B-tree index, but it can only be minted by the database and it leaks how many rows you have. A UUID v4 is globally unique and can be generated anywhere - even on a phone, offline - but it is sixteen bytes of randomness that scatters index writes and widens every foreign key.

The UUID generator produces random UUID v4 per RFC 4122, using crypto.randomUUID() where the browser offers it, in batches you can copy. This guide explains when a UUID earns its costs and when a plain sequential ID is simply the better answer.

What each identifier actually is

An auto-increment ID is a whole number the database bumps by one on each insert: 1, 2, 3... It is compact (an 8-byte BIGINT holds enormous counts), sorts naturally, and makes range queries cheap. A UUID is a 128-bit value usually written as 36 characters 8-4-4-4-12; version 4 means the bits are essentially random apart from a version nibble and two variant bits, leaving about 122 random bits of entropy.

Both are valid primary keys; they optimise for opposite things - locality and size versus decentralised uniqueness.

Criterion	Auto-Increment	UUID v4
Size	4-8 bytes	16 bytes (36 as text).
Uniqueness scope	Unique within one table.	Globally unique across systems.
Ordering	Sequential, sorts by creation.	Random, no creation order.
Index locality	Excellent - appends to the end.	Poor - scattered inserts.
Generate anywhere?	No - DB assigns it.	Yes - client/offline too.
Leaks volume?	Yes - enumerable and countable.	No - opaque, unguessable.

Read the table as a decision: the top rows favour integers, the bottom two - global uniqueness and offline generation - are exactly where UUIDs pay for their costs.

Why random UUIDs are safe from collisions

v4 randomness makes accidental duplicates astronomically unlikely. The usual honest framing: generating about one billion UUIDs every second for 85 years would only give you roughly a 50% chance of a single collision. For any real application, you can treat a v4 UUID as unique without checking - which is precisely why it is safe to mint on a device that cannot reach the database to be handed the next number.

That property is the whole reason distributed systems, mobile apps, event logs and offline-first tools reach for UUIDs: each node invents identifiers that are guaranteed not to clash when records later merge.

The real costs of randomness

Random keys are hostile to B-tree indexes. A sequential integer always appends to the leaf page that is already hot in cache, but a random UUID lands anywhere, forcing page splits, fragmenting the index and thrashing the buffer pool - measurably slower bulk inserts and larger indexes at scale. The 16-byte key also inflates every secondary index, because each one carries the primary key as its row pointer.

There is a product angle too: sequential IDs are enumerable. If URLs expose /orders/4382, an attacker can walk to 4383 and learn both your volume and other customers' orders. That is an authorization bug if access is ever gated on guessing the id, but random, opaque ids raise the bar so you are not relying on secrecy by accident.

The time-ordered middle ground

You do not have to choose between "ordered but centralised" and "unique but random." UUIDv7, Snowflake IDs and ULIDs put a timestamp in the leading bits and randomness after, so keys sort by creation and stay globally unique - you recover the index locality that v4 sacrifices while keeping offline generation. Separately, storing a UUID as BINARY(16) (or a base64 form of the bytes) reclaims most of the space the 36-character text wastes.

Be honest about scope: the free generator here emits v4, which is the widely compatible random form; time-ordered variants are a different tool you would generate server-side.

A plain decision rule

For a single-database application where rows are only ever created by that database, a sequential integer (or its opaque-token sibling for public identifiers) is usually the pragmatic, faster, smaller choice. Reach for UUIDs when identifiers must be created before or outside the database, across shards, or merged between systems. And if you adopt random UUIDs on a hot table, mitigate the locality cost by storing them as 16-byte binary or moving to a time-ordered form.

Whatever you pick for internal keys, keep anything a user can see and guess - order numbers, password-reset ids - as unguessable random tokens, not your row counter; that is exactly the job the password / random-token generator handles well.

Frequently Asked Questions

Are UUID v4 truly random?

Essentially yes - about 122 of the 128 bits are random, with only a fixed version nibble and two variant bits. The generator here uses crypto.randomUUID() where available, which is a cryptographically strong random source rather than a predictable one.

Do random UUIDs make the database slower?

They can. Random keys scatter inserts across the B-tree, causing page splits and poorer cache locality, and the 16-byte key widens secondary indexes. At moderate scale it is invisible; at high insert volume you mitigate by storing BINARY(16) or switching to a time-ordered id such as UUIDv7.

Can two UUIDs collide?

In theory yes, in practice no. With 122 random bits you would need to generate roughly a billion per second for 85 years to reach a coin-flip chance of one collision, so applications treat v4 as unique without a duplicate check.

Is an auto-increment ID a security problem?

It is enumerable and leaks your row count, so never let the secrecy of a sequential id be your only defence for a public resource. Use it happily as an internal primary key, but expose unguessable random tokens in user-facing URLs and identifiers.

How can I shorten a UUID?

Store the raw value as 16 binary bytes instead of 36 text characters, or encode those bytes in a compact alphabet such as base64. This saves space without changing the identifier; just decode consistently wherever it is read.

When should I actually use UUIDs?

When records are created outside or before the database - mobile and offline-first apps, distributed or sharded systems, event logs, or anything whose rows are merged between sources. If a single database assigns all ids and scale is modest, a sequential integer is simpler and faster.