Results of experiments with UUIDv7, UUIDv8

Hi all

My colleague and I did some experiments to see what effect using UUIDs as
2nd-ary indexes has on Index IO. The context is that by default ORM
frameworks will use UUIDs as index keys which I found as a major factor to
performance issues at Celonis. I suspect this isn't specific to Celonis.
The secondary factor is that random IO on Azure Single Server can be slow
as a dog -- thus for large enough indexes that aren't cached, and workloads
doing insert/delete at a high enough QPS, this really hurts.

We found that using UUID v7 (which has a longer time based prefix than v8)
gave 30% in IO savings in index access and roughly the same in index size
after I/D workload. v8 was ~24%. We simulated slow, random IO by running
this on a USB key which seemed to match Azure performance pretty well. SSD
was maybe 2x better.
This is relative to UUID v3 which is essentially random (actually, pretty
good random distribution on a 500Gb table).

This isn't as much as I expected, but, again for large indexes, slow IO, it
was significant.

peter