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!
Glad you liked it! Please do give us feedback especially about how it is to use with your library…will be intrigued to see how they interact. Also , cool to hear about the library more generally, any particularly good syntax you think we should try to learn from?
This is all true! We have a bit more info on that choice in this post [1].
But in general the Toolkit extension [2] is meant to have a bit of a lighter touch than the core TimescaleDB extension [3], it's meant for moving faster and working almost entirely with the custom functions/types/operators type approach rather than the core extension which is integrated much more deeply into the planner & executor and deals with a number of other hooks. It would be possible to do that in Rust, but because it's so deeply integrated with the main Postgres codebase there would just be a lot of translation going on and you'd get a lot less of the benefits of working in Rust. So C is more natural for much of the core DB extension.
PGX [4] is also a huge help for writing this sort of extension. The point of Toolkit is really to move much faster, try lots of things, mostly using the more SQL facing API (CREATE FUNCTION, CREATE AGGREGATE and the like). And PGX helps manage all of that really nicely. The rest of Rust being available is also great and we like working in the language, but without PGX it'd be a lot harder and we couldn't move nearly as fast.
Kinda. It's close to that, except in order to make it do that we had to actually make the functions return special types so it's more equivalent to something like
Oh yeah, and building on this, this is also the source of the ability to actually store the pipeline element in the database, say in a column and apply it later that I mentioned in a comment below. That's one of the cool mind-bending things this allows.
The example I gave below was building on the example in the post, where we had thermometers. Except now think about if you have thermocouples and each thermocouple had its own calibration curve, and they were different types so they might even have different types of equations providing the calibration curve.
You can now store the pipeline that does the correct adjustments and calculates the actual temperature separately for each `device_id` and JOIN to the "calibrations" table to apply the calculation.
It's kinda crazy, but really cool and I'm hoping to do a post on that whole thing at some point to encourage people to play around with it and see what sorts of things it allows...
The docs have a bit more on it [1]. The $$ is really just a dollar quoted string[2], you can use any sort of string in there, but dollar quoting just makes it a lot easier to read.
On your other question, not right now, but if it's something you want, file a github issue, it's something we've thought a bit about and it gets hairy pretty quickly, but also increases the power of these things dramatically. We'll probably do something with it, but it may be a little while and we may let this stuff bake for a bit first...
Yep! you got it exactly right! We're doing it for syntax first and then hopefully go back and start doing interesting performance optimizations on top. There are definitely some things we're thinking about.
Yeah. It's a bit mind bending I guess. Will try to explain but might just need a longer format thing and we will probably do a piece on this in the future.
Essentially, each function returns a special "PipelineElement" datatype that identifies the function, but not what it's acting on. When you string them together, it returns a "PipelineElement" that represents the composition of all the functions with the correct ordering etc. That can then be applied to the incoming `timevector`.
As far as I know, no one's done this before. I don't think it's a completely new idea, Stonebreaker thought about how you can treat code as data and thought that bringing data and code closer together was an important goal of the Postgres project, but this specific way of doing it is pretty new and out there I think!
I think most people would think that in order to do this you'd need to do something like modify the parser or something more invasive.
(We do use one planner hook on the function call, the expression simplification one that allows us to convert something like `timevector(ts, val) -> sort() -> delta() -> abs() -> sum()` into `timevector(ts, val) -> (sort() -> delta() -> abs() -> sum())` so that all the operations can be done in a single pass. But that's pretty minor. If we need to figure something out around the common subexpression elimination we may need to hook into the planner a little more, we'll see).
This is in the TimescaleDB Toolkit extension [1] which is licensed under our community license for now and it's not available on DO. It is available on our cloud service fully managed. You can also install it and run it for free yourself.
We're planning on providing various outputs, json is definitely one format we'd want to support, want to file a github issue with the layout that you'd want and we can start getting some feedback to implement?
TimescaleDB is a PostgreSQL extension, just to be clear.