hi!
in our current data, our events we have a monotonic batch id (non decreasing, non unique), along with a timestamp. these events have high cardinality labels by account id.
some accounts are updated every hour, but other accounts are updated very rarely (<100 times a year). 95% of accounts are updated very rarely, but the 5% of accounts that are frequent take up most of the events.
i need to be able to run both time based analytical queries and aggregates and ALSO fast point-in-time by batch id lookups
so what i did is create a two dimensional partition over both timestamp and batch id.
so now im looking at one of our queries which gets the most recent event of a given account by the timestamp, then event id.
but timescale seems to have a really hard time doing this query very quickly, since timescale/postgres doesn’t know about this relationship between timestamp and batch id, and so it has to scan through all the batch id chunks within the timestamp based chunk to get the latest item, but in truth, it is safe to use the latest batch id
i’m not sure if im doing something wrong, if i could be formulating my query better (it is a simple select order by ts, batch desc limit 1), or maybe there is some timescale feature that i am not using that could help here?
in an ideal world, i think i would just want to have one chunk instead of two dimensional chunk for both batch and time, and have minmax on every single chunk that has information for the both, guaranteeing that there will never be overlaps. but i dont think that is possible with timescale?
basically, i know that if you have found the entry you need iterating down chunks by batch id, you no longer need to look at any more chunks with the same timestamp range since you know for sure that you have newer timestamps you have newer batch id. but i dont know how to model the data to allow timescale/postgres to take advantage of this correlation