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
Table of contents
IntroductionUUID context for this postScope of web app usage and and their scaleRandomness is the issueWhy choose UUIDs at all? Generating values from one or more client applicationsMisconceptions: UUIDs are secureCreating obfuscated values using integersReasons against UUIDs in general: they consume a lot of spaceReasons against: UUID v4s add insert latency due to index page splits, fragmentationExcessive IO for lookups even with orderable UUIDsWorking with integers, UUID v4, and UUID v7Inspecting density with the pageinspect extensionUUID Downsides: Worse cache hit ratioMitigations: Rebuilding indexes with UUID valuesMitigation: Shared buffers and work_mem memory sizingMitigation in Rails: UUID and implicit order column Active RecordMitigating poor performance by clustering on orderable fieldRecommendation: Stick with sequences, integers, and big integersUUID v4 alternatives: Use time-ordered UUIDs like Version 7SummaryLearn More10 Comments
Sort: