Avoid UUID Version 4 Primary Keys
6 months ago
- #database-performance
- #postgresql
- #uuid-vs-integer
- UUID Version 4 (random) primary keys cause poor database performance due to randomness leading to inefficient index usage.
- UUIDs consume more space (16 bytes) compared to integers (4-8 bytes), impacting storage and IO.
- Random UUIDs cause index page splits and fragmentation, increasing insert latency and write IO.
- UUID v4s are not secure and should not be used as security capabilities.
- Time-ordered UUIDs (Version 7) mitigate some issues by including a timestamp for better index performance.
- Alternatives to UUIDs include generating obfuscated codes from integers or using sequences/big integers for primary keys.
- Mitigations for UUID performance issues include rebuilding indexes, increasing memory (shared_buffers, work_mem), and clustering on orderable fields.
- Recommendation: Use integers or big integers for primary keys unless UUIDs are necessary, then prefer UUID v7.