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.