Re:New UUID Formats – IETF Draft
by johncolanduoni on Friday August 06, 2021 @05:03PM
(#28092716)
It doesn’t, since Postgres doesn’t actively use the primary key for clustering.
It still increases write rate a lot over more sequential values / reduced cache hit ratio.
With sequential-ish values indexed by something like b-trees the same index pages get modified over and over again because new table rows will be in a narrow part of the index. As databases / operating systems typically buffer writes for a while, this reduced the number of writes hitting storage substantially.
Conversely, with random values, every leaf page will be modified with the same probability. That's not problem with a small index, because you'll soon dirty every page anyway. But as soon as the index gets larger you'll see many more writes hitting storage.
FYI - for anyone running across this thread later. This is a unique problem with (a kind of globally) consistent storage on small (single node equivalent) systems. If you have a large scale distributed system, you WANT writes to be well distributed across all nodes, or you’ll end up with problematic hot spots.
All new writes ending up on the same node/page/index is a good way to crush your system in a cascading-never-coming-back-up-until-you-drain-traffic-kind-of-way
At my last job, this was managed by using two ID's on each row - a serial one that was basically only used for database optimization purposes, and the "real" UUIDv4 ID. It felt gross then, and it feels gross now, but it seemed to do the trick for our needs.