Better continuous aggregates performance in TimescaleDB 2.7

[carlota]

Starting in TimescaleDB 2.7, continuous aggregates will be implementing a completely redesigned materialization process that solves many of the usability issues you may have encountered with continuous aggregates in the past.

In practice, this means that:

• Continuous aggregates are now much faster. We tested the performance of the new continuous aggregates using multiple queries and two different datasets, and we see performance improvements that range from 2.6x to 44,595x. Check out this blog post to see a comprehensive benchmark including different queries and datasets.

• You can now use DISTINCT, ORDER BY, FILTER with HAVING, and other query clauses with continuous aggregates. Check out the TimescaleDB 2.7 release notes for a complete list.

• Continuous aggregates now have less storage footprint. In our testing, the new continuous aggregates were on average over 60 % smaller than before.

How to start using the new continuous aggregates

We recommend you upgrade to TimescaleDB 2.7 to start taking advantage of all these improvements in continuous aggregates:

• If you’re self-hosting TimescaleDB, check out our docs for instructions on how to upgrade.
• Timescale Cloud avoids you the manual work involved in updating your TimescaleDB version, as updates take place automatically during a maintenance window picked by you. You can try Timescale Cloud for free, no credit card required.

By default, TimescaleDB 2.7 and later will use the new continuous aggregate format, which will be applied automatically to all your new continuous aggregates.

For your existing continuous aggregates - we recommend you recreate them once you’ve upgraded to TimescaleDB 2.7, to take advantage of these improvements. To do this, you need the original data by now - but we’re working on a migration path that will allow you to migrate to the new continuous aggregate format without the original data. We will keep you posted.

If you have any questions or feedback, please let us know. We’d love to hear about your experience with the new continuous aggregates!

[ituralde]

A confusing thing is that there is no documentation saying that you cannot use OFFSET and ORIGIN in time_bucket when creating MATERIALIZED VIEW. The command will just say invalid time_bucket.

I am using TimescaleDB 2.7.0. The issue has been explained here: [Error while using origin parameter in time_bucket].
In fact, there is an open github issue too: Error creating continuous aggregate view with time_bucket origin/offset parameter · Issue #2265 · timescale/timescaledb · GitHub

Any comment?

[carlota]

Hi ituralde! Thanks so much for raising the issue - I saw your previous post, I believe @charis and the rest of our docs team are already looking into how to make this more clear in our docs. This is already in our backlog!