Developer Tools

UUIDs as Database Primary Keys: The Real Pros and Cons

8 min
By
UUIDs as Database Primary Keys: The Real Pros and Cons

Photo by luis gomes from Pexels

I inherited a database once where every table used an auto-increment integer. Then we needed to merge two production databases. Every foreign key collided. We spent three days renumbering. UUIDs would have saved us.

That is the UUID pitch in one story. But the story is not the whole truth. UUIDs as primary keys have real costs, and they bite you in production, not in the demo.

The Pros

**No central authority needed.** Any client can generate a UUID before talking to the database. This lets you insert from offline systems, queue workers, and edge devices without a round-trip to get an ID first.

**Merge-safe.** Two databases with UUID primary keys can be combined without key collisions. This matters for multi-tenant systems, sharded databases, and any merge-replication setup.

**No information leak.** An auto-increment ID of 142 tells an attacker you have 142 records. A UUID like f47ac10b-58cc-4372-a567-0e02b2c3d479 tells them nothing.

**Stable across imports and migrations.** You can seed a database with UUIDs, export, reimport, and the keys stay the same. Integer IDs shift if you are not careful.

The Cons

**Index fragmentation.** This is the big one. A v4 UUID is random, so new inserts land at random points in a B-tree index. This causes page splits, which cause write amplification, which kills throughput on high-insert tables. Integer IDs append to the end of the index. No splits.

**Storage size.** A UUID is 16 bytes. A 64-bit integer is 8 bytes. Every primary key, every foreign key, every index that includes the key — all double in size. On a 50 million row table with three indexes, that adds up to real money.

**Readability.** SELECT * FROM users WHERE id = 42 is something you can type. SELECT * FROM users WHERE id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479' is not. This sounds trivial. It is not, when you are debugging at 2am.

UUID for SQL Server Primary Key

SQL Server stores UUIDs as UNIQUEIDENTIFIER, which is 16 bytes. The default sort order for UNIQUEIDENTIFIER is not the same as the string representation — SQL Server orders by the internal byte layout, which makes v4 UUIDs even worse for index fragmentation than they look.

The fix: use NEWSEQUENTIALID() instead of NEWID(). NEWSEQUENTIALID() generates UUIDs that increase over time on a given machine, so inserts append to the index instead of scattering. You lose pure randomness, but you keep merge safety and fix the fragmentation problem.

sql CREATE TABLE orders ( id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), customer_id UNIQUEIDENTIFIER NOT NULL, total DECIMAL(10,2), PRIMARY KEY (id) );

## UUID for Postgres Primary Key

Postgres has a native uuid type and the uuid-ossp extension (or pgcrypto for gen_random_uuid()).

sql CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE orders ( id UUID DEFAULT gen_random_uuid(), customer_id UUID NOT NULL, total NUMERIC(10,2), PRIMARY KEY (id) );

gen_random_uuid() produces v4 UUIDs. For the fragmentation problem, consider UUID v7 (time-ordered, sortable) if you are on Postgres 18+, or use a v1 UUID from the uuid-ossp extension. The uuid vs auto increment debate matters less when you pick a sortable UUID variant.

UUID vs Auto Increment: The Honest Answer

Use auto-increment integers when: - You have a single database, no merges, no sharding - Insert throughput is critical and you cannot afford page splits - Humans need to read and type IDs regularly

Use UUIDs when: - You merge or replicate databases - Clients generate IDs before insert (offline, distributed, multi-tenant) - You must not leak record counts - You shard horizontally

UUID for MongoDB

MongoDB's _id field defaults to an ObjectId, which is 12 bytes and time-ordered. It already solves most of what UUID solves. You can use a UUID as _id if you need cross-system consistency, but ObjectId is the better default for most MongoDB workloads because it is smaller and sortable.

javascript db.orders.insertOne({ _id: UUID('f47ac10b-58cc-4372-a567-0e02b2c3d479'), customer: 'Alice' });

## The Verdict

UUIDs as primary keys are the right call for distributed, multi-tenant, or merge-prone systems. They are the wrong call for single-database, high-throughput, human-facing systems where integer IDs do the job. The fragmentation cost is real — mitigate it with sequential or time-ordered UUIDs.

Generate UUIDs for your schema with our UUID generator. For version differences, see UUID v1 vs v4 vs v5. For bulk seeding, read generate UUIDs in bulk.

Frequently Asked Questions

Do UUIDs slow down database inserts?

Random v4 UUIDs cause B-tree page splits on insert, which slows high-throughput tables. Use sequential UUIDs (NEWSEQUENTIALID in SQL Server) or time-ordered UUIDs (v1 or v7) to fix this.

How much storage does a UUID primary key add?

A UUID is 16 bytes vs 8 bytes for a bigint. Every primary key, foreign key, and secondary index that includes the key doubles in size. On large tables this is significant.

Should MongoDB use UUIDs for _id?

Usually no. MongoDB's default ObjectId is 12 bytes, time-ordered, and sufficient. Use a UUID as _id only if you need cross-system consistency with other databases using UUIDs.

Try NovelCrypt Tools

Experience military-grade encryption for your sensitive data. Create self-destructing messages, encrypt files, or explore our experimental lab tools.

Explore NovelCrypt