3 ms·
Are your IDs monotonic by date (i.e. increasing the date will never decrease the ID)? Or, to put that another way, is partitioning by date equivalent to partiti
by derefr 9y ago
Are your IDs monotonic by date (i.e. increasing the date will never decrease the ID)? Or, to put that another way, is partitioning by date equivalent to partitioning by ID for you, such that your date-based partitions will each end up with defined (if unknown) ranges of IDs?
If so, you could potentially create 1. a table that maps ID ranges to partitions, and then 2. create a stored procedure that takes a set of IDs and returns the set of partitions you must scan to find them.
- koolba 9y agoNo they're entirely separate. The IDs are pure UUIDs with no structure tying them to the desired partition key.
- derefr 9y agoCould you switch from UUIDv4s (what I assume you mean by “pure UUIDs”) to UUIDv1s?
- koolba 9y agoNo I've considered that as well as splitting the key to be a composite of the partition key but I'd like an eventual solution for this in the general case where there's no relationship between the partitioning method and the primary key.
- snissn 9y agoAs far as I'm understanding your situtation it seems like you're using range partitioning and then hoping to get benefits of hash partitioning from a range partition. I think you would need to use hash partitions to avoid look ups on every every table. You could do something weird like have each range partition have an exclusion filter and make sure that each of your randomly generated IDs accommodate that exclusion filter, and generate IDs until you find one.. But it sounds like you would benefit from a hash partition here instead of a range one.