Hacker News·23wAvoid UUID Version 4 Primary Keys
UUID Version 4 primary keys cause significant performance problems in PostgreSQL due to their random nature. Random values trigger excessive index page splits during inserts, create fragmented indexes with poor density (~79% vs ~98% for integers), and require accessing 31,000% more buffer pages for queries. The randomness prevents efficient B-Tree index operations and degrades cache hit ratios. Time-ordered alternatives like UUID Version 7 perform better by including timestamps in the first 48 bits. For most applications, integer or bigint primary keys backed by sequences remain the optimal choice, offering better performance, smaller storage footprint (4-8 bytes vs 16 bytes), and natural ordering. When obfuscation is needed, pseudo-random codes can be generated from integers using XOR operations and base62 encoding.
