Avoid UUID Version 4 Primary Keys | Software Engineer, Author, High Performance PostgreSQL for Rails
A well-researched article suggesting that random UUIDs do not make a good primary key for database tables; I would tend to agree (for cases where performance is important).
- UUID v4s increase latency for lookups, as they can’t take advantage of fast ordered lookups in B-Tree indexes
- For new databases, don’t use gen_random_uuid() for primary key types, which generates random UUID v4 values
- UUIDs consume twice the space of bigint
- UUID v4 values are not meant to be secure per the UUID RFC
- UUID v4s are random. For good performance, the whole index must be in buffer cache for index scans, which is increasingly unlikely for bigger data.
- UUID v4s cause more page splits, which increase IO for writes with increased fragmentation, and increased size of WAL logs
- For non-guessable, obfuscated pseudo-random codes, we can generate those from integers, which could be an alternative to using UUIDs
- If you must use UUIDs, use time-orderable UUIDs like UUID v7
Tags: postgres rails databases sql mysql uuids indexing primary-keys keys lookup storage random