> > When done that way, you're going to see a lot of index B-tree
> > fragmentation with even DCE 1.1 (ISO/IEC 11578:1996) time based
UUIDs,
> > as described above. With random (version 4) or hashed based (version
3
> > or 5) UUIDs there's nothing that can be done to improve the
situation,
> > obviously.
>
> Is this based on empirical results or just a theory? I'm asking
because
> it's actually a common technique to reverse the natural index key to
> construct basically exactly this situation -- for performance reasons.
> The idea is that low order bits have higher cardinality and that that
> can *improve* btree performance by avoiding contention.
>
> I'm not sure how much I believe in the effectiveness of that strategy
> myself or for that matter whether it's universally applicable or only
> useful in certain types of loads.
>
> I'm not saying you're wrong, but I'm not sure it's a simple open and
shut
> case either.
>
What hinted me off to this was the same problem occurring in SQL Server,
where changing the behavior gave them a much lower I/O load with
replication (which utilizes UUIDs).
A blog post from an MS developer at http://tinyurl.com/2xy5jn talks
about how this has allowed a tighter index as well as not requiring
random searches on the b-tree, significantly reducing I/O.
A performance analysis at http://tinyurl.com/2ysora has a table
comparing using an integer, UUID and sequential UUID (when the system
orders UUIDs sequentially by time, like SQL Server already does).
Obviously this is not SQL Server we're dealing with, but I can see many
of the same issues unavoidable and equally impactful as we both use the
same index data structure. I'll
That being said, I'm going to perform tests today or this weekend on
different loads to see how PostgreSQL would be affected by this change.
I'll be very interested to see the results of random b-tree searches on
every insert vs the contention from sequentially generated UUIDs.
-- Robert
