2 ms·

You're right that random unordered writes are worst case for an indexed (ordered) key. ULID and ordered UUIDs (v6+) help solve this. For dimensions, UUIDs are

You're right that random unordered writes are worst case for an indexed (ordered) key. ULID and ordered UUIDs (v6+) help solve this. For dimensions, UUIDs are usually fine since writes are infrequent. For facts or timeseries data, ordered IDs are more efficient.

WorldMaker 5y ago
Yes, ordered UUIDs help solve this. The unfortunate deep dive rabbit hole here is how UUIDs are sorted in different databases and making sure your UUID generation matches that. One fun for instance I worked directly with: Microsoft's SQL Server made some interesting assumptions based on UUID v1 and sorts the last six bytes first. In UUIDv1 those would have been the MAC addresses and clustering by originating machine first has some sort of sense to it in terms of ordered writes. The ULID timestamp is coincidentally also six bytes (48 bits) so (ignoring the Endian issues of the other "fields" in the UUID) you can get Microsoft's SQL Server to order UUIDs in mostly the same way as their ULID representation by just transposing the first six bytes to be the last six bytes. Unfortunately UUID v6+ won't sort well in Microsoft SQL Server's sort order today. Other databases will vary on what you need to do to get sortable UUIDs. A reference to me on all of this deep rabbit hole was Raymond Chen's blog on the many GUID/UUID sort orders just in Microsoft products: https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450 https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=10... (With the fun punchline at the bottom being the link to the Java sort order. My sympathies to anyone trying to sort UUIDs in an Oracle database.)