UUID vs auto-increment ID: which primary key to use

Every database table needs a primary key. The two common choices are an auto-incrementing integer, where the database hands out 1, 2, 3, and a UUID, a 128-bit identifier generated by anyone, anywhere. Both work. The right choice depends on how records are created, exposed and stored.

Auto-increment integers

• Small: 4 or 8 bytes, so indexes and foreign keys stay compact.
• Ordered: new rows go at the end of the index, which is efficient for inserts.
• Readable: “order 4821” is easy to say on a phone call.
• Guessable: if /invoice/4821 exists, so does 4822. Without proper permission checks, people can walk through other customers' records.
• Revealing: IDs leak how many users or orders you have and how fast they grow.
• Central: only the database can hand them out, which complicates offline creation, merging databases and sharding.

UUIDs

• Unique anywhere: a phone, a browser or a second database can create IDs with no coordination, and merging data never clashes.
• Not guessable: random UUIDs cannot be enumerated (though permission checks are still required).
• Larger: 16 bytes, four times a standard integer, and more if stored as 36-character text.
• Awkward for humans to read aloud or type.

The index problem with UUIDv4, and v7

Version 4 UUIDs are fully random. In databases that keep table rows ordered by primary key, random keys land all over the index, which causes page splits, more disk writes and poorer caching as the table grows. Version 7 UUIDs, standardised in 2024, start with a timestamp, so new IDs sort after older ones and insert efficiently, like an auto-increment, while remaining unique without coordination. For new projects that want UUIDs as primary keys, v7 is usually the better choice. See what a UUID is: version 4 vs version 7.

Store them properly

• Use the database's native UUID type where there is one.
• Otherwise store 16 bytes of binary, not a 36-character string, which more than doubles the size.
• Generate v7 in the application or database consistently, so ordering holds.

Sorting by creation time

With auto-increment IDs, sorting by ID gives creation order for free. With UUIDv4 it does not, so tables need a separate created_at column for that. UUIDv7 restores the property, because its leading timestamp makes IDs sort roughly by creation time, though a real timestamp column is still clearer for reports.

A common compromise

Many systems use both: an integer primary key for internal joins, and a UUID column, indexed and unique, as the public identifier in URLs and APIs. Internal efficiency stays, and public IDs cannot be guessed or counted.

Quick decision guide

• Small internal app, single database: auto-increment is fine.
• IDs visible in URLs or APIs: UUID, or a separate public UUID.
• Offline or client-side creation, multiple databases: UUIDv7.

The UUID Generator creates version 4 and version 7 UUIDs, plus NIL and ULID-style output, for testing schemas and seeding data.