Re:Understanding How UUIDs Are Generated
by marcus_holmes on Thursday October 01, 2020 @09:16AM
(#24650463)
Well, for some databases, yes, this is "clustering", and there's a command to deal with it ([0] is postgres, which doesn't do this particularly well, other flavours of database have similar commands).
But it's the usual thing with databases: if you're trying to optimise performance by hacking the engine, then your schema probably needs a good hard looking at.
[0] https://www.postgresql.org/docs/current/sql-cluster.html
Re:Understanding How UUIDs Are Generated
by lazulicurio on Thursday October 01, 2020 @11:39AM
(#24652159)
I don't consider being aware of your data distribution to be "hacking the engine". It's part of a good DBA's job. For any data set of appreciable size you can't treat the data as a black box. Even if you're not clustering the underlying table in postgres the performance of your indices will be heavily affected by data layout.
Re:Understanding How UUIDs Are Generated
by marcus_holmes on Friday October 02, 2020 @08:41AM
(#24661979)
I agree. Hacking the ID field to include index data so the storage clustering is a bit more performant is not "treating the data as a black box" to my mind.
But even without the need to sort by id, one of the advantage is that sequential ids makes it easier for databases to fetch data, as data stored ends up being more sequential in disk as well.
More reading on that:
- https://eager.io/blog/how-long-does-an-id-need-to-be/#locali...
- https://www.percona.com/blog/2019/11/22/uuids-are-popular-bu...