We are kindred spirits I think! I did this too [0] a while back at a previous company and it actually served as part of the inspiration for our compression work! It's fun, but a bit difficult to query at times. Our compressed columns do also get TOASTed and stored out of line.
I'm not sure that it's going to be much more efficient than the Timescale format once it's compressed, we have some pretty good compression algos, but I might be missing something about your case, we generally can achieve close to 10x compression, but right now you can't write directly compressed data, so you would save on the write side I suppose.
It is true that you need to put the uncompressed version into memory at some point, but we do try to limit that and in many cases you end up IO limited moreso than memory limited. We're also thinking about doing some work to push processing down towards the compressed data, but that's still in the "glint in our eye" stage, but I think it has a lot of promise.
(As a side note, TOAST is still the best acronym around ;) ).
Though I didn't write this post, I'd imagine at least part of it is that it's already nearly 4000 words and a 15 minute read and we just didn't want to add another set of things to it, to be perfectly honest.
`pg_partman` is cool! I haven't used it in a while, and because it uses declarative partitioning, it has some locking issues that we address with our partitioning scheme, but implying that it is OSS and we're not in terms of things like data retention features is a bit misleading as well. The `drop_chunks` command used for data retention is in the Apache 2 licensed portion of Timescale.
So we thought about doing something like that with multinode where each of the nodes would maintain their own materialization but abandoned it for that very reason it’s very, very difficult to maintain any sort of consistency guarantees in that case, or even to reason about it.
Instead we use the access nodes as coordinators to do the materialization. right now the materialization only exists on the access node but there’s no reason we couldn’t send it back out to the data nodes, you just need a coordination point to start a distributed transaction to have some semblance of a guarantee.
Yes. this is generally handled automatically, there may be times though where you want to essentially pause refreshing the view for a while while you do some backfilling and then eventually let it catch up, especially if you're overwriting the same time period multiple times in a row. If you can insert in time order then it just breaks up re-calculation into smaller segments, which can be quite useful rather than having to process the whole data set again.
This can be a little bit different if you're doing compression, but with continuous aggregates I think it should work fine. I'm not 100% sure that was what you were looking for, let me know if it's not.
I'm not 100% sure I understand what you're asking, but essentially something that would look for data modifications and at query time run the query over the older regions as well?
If that's what you're asking the answer is yes, we did consider it, but basically decided that it was something that relatively few people needed and the complexity and performance tradeoffs were unlikely to be worth it for most folks.
Essentially, we could do something like this now by looking at our invalidation log and running a join against it to get to a more strongly consistent state (I haven't thought through the full implications and whether it's truly strong consistency, I think it might be, but it'd require a proof / some thinking through of all of our locking logic to really get there). It's interesting to consider though.
Thanks yes! Totally true, was thinking about including some of that but it felt like it opened a can of worms about join types and why certain things would be included and others not (ie inner join needs to see that it's there on both sides whereas the left join doesn't) etc. and the post was already kinda long in the tooth.
NB - Timescale person here.
Totally true! It's also a much harder problem :) One of the things that we try to focus on at Timescale is figuring out how we can simplify problems based on the specific needs of time-series data. Postgres has to solve things for very general cases, and sometimes that just is much harder. And then they often won't work all that well for time-series, because they're not all that optimized for them.
Totally fair and something that I'm actually forming a team to work on! We're starting with some very foundational material [1], that may well be review and it's not as formal / professional as Mongo University or the like, but I am going to be continuing this course and then we'll be iterating more from there. I'd really love some feedback and also your questions, ie what you want to cover or what you find confusing. You can leave comments on the video or in our community Slack channel[2] or forum[3]. Thanks for the feedback and I hope we'll be able to do some of that for you over the coming months!
TimescaleDB is a PostgreSQL extension, just to be clear.