This is one of the longest running debates in database design. Auto increment integers are small and fast. UUIDs are globally unique and safe to expose. Each camp has strong opinions. The honest answer is that it depends on your workload, and that the arrival of UUID v7 has shifted the balance. Here is how to think about it.
What integers do well
- Size. A bigint is 8 bytes. A UUID is 16. Every index that references the key, every foreign key column, every join buffer pays that cost twice over.
- Insert locality. New rows always get a bigger number than the last one, so they append to the end of the primary index. The pages being written stay in memory.
- Readability. Order 1042 is easy to say on a support call. Order 9b2f8a1d-3c4e-4f5a-b6c7-d8e9f0a1b2c3 is not.
What UUIDs do well
- Generation anywhere. A mobile app can create a record with its ID before it has any connection, then sync later with no risk of conflict. Two databases can be merged without renumbering.
- No information leak. A sequential ID in a URL tells competitors how many customers you have and lets anyone enumerate your records by adding one. A UUID does neither.
- No round trip. With integers the application must insert and then read back the generated ID. With UUIDs the application already knows the ID and can use it in related inserts immediately.
The fragmentation problem with v4
The strongest technical argument against UUIDs is specifically against random ones. A B-tree index keeps keys in sorted order. When each new key is random, it lands on a random leaf page. On a large table most of those pages are not cached, so every insert becomes a read, a modify and a write of a cold page, plus occasional page splits. Benchmarks on both PostgreSQL and MySQL InnoDB regularly show random UUID inserts running two to four times slower than integers once the index no longer fits in memory, with the index itself ending up 30 to 50 percent larger from half empty pages.
MySQL suffers more because InnoDB clusters the whole row by primary key, so the table data is fragmented too, not just the index.
How v7 fixes it
UUID v7 puts a millisecond timestamp in the first 48 bits. New keys are always larger than old ones, so inserts append to the right side of the index exactly like an integer would. You keep the global uniqueness and the safe URLs, and you get back almost all of the insert performance. In most measurements v7 lands within 10 to 20 percent of bigint on insert throughput and index size. Read UUID v4 vs v7 for the format details.
Store it as a real UUID type
Whatever you choose, do not store UUIDs as 36 character strings. That is 36 bytes plus overhead instead of 16, and string comparison is slower than binary.
- PostgreSQL has a native
uuidcolumn type. Usegen_random_uuid()for v4 or, on version 18 and later,uuidv7()as the default. - MySQL has no UUID type. Use
BINARY(16)and convert withUUID_TO_BIN()andBIN_TO_UUID(). Generate v7 in the application, since the built inUUID()function returns v1. - SQL Server has
uniqueidentifier. Be aware it sorts GUIDs by an unusual byte order, so v7 ordering is not preserved without care. - SQLite stores them as a 16 byte BLOB.
A hybrid pattern
Some teams use both: an internal bigint primary key for joins and foreign keys, plus a UUID column with a unique index for anything exposed to the outside world. This gets the smallest indexes and the safest URLs at the cost of one extra column and one extra lookup on external requests. It is a good fit for very large tables with many foreign key references.
Recommendation
- Small to medium tables, or any table where rows are created outside the database: UUID v7 as the primary key.
- Very large tables with heavy joins and no external exposure: bigint, or the hybrid pattern.
- Existing tables with v4 keys that are performing fine: leave them. Switching primary keys is expensive and the gain only matters at scale.
To see what v7 keys look like in practice, generate a batch with our UUID generator and paste them into a test table sorted by primary key.