TimescaleDB 1.7: fast continuous aggregates with real-time views, PostgreSQL 12 support, and more Community features

We’ve just finished our latest release 🥳. Here’s a quick look at what’s new and how to get started.

Fresh from engineering, we’re excited to announce TimescaleDB 1.7, which includes two major updates: PostgreSQL 12 support and Real-time Aggregates. In addition, we’ve made several data management features available in our free Community edition.

PG 12 Support

TimescaleDB 1.7 adds  PostgreSQL 12 support, aligning TimescaleDB with the latest Postgres features.  As always, with TimescaleDB you can build on a proven PostgreSQL ecosystem and leverage Postgres extensions - if it works with Postgres, it works with TimescaleDB.

Introducing Real-time Aggregates

TimescaleDB 1.7 introduces real-time aggregates, which return real-time results for continuous aggregates over selected time intervals.  Now, queries on continuous aggregates (originally introduced in TimescaleDB 1.3) will automatically return the pre-calculated materialized view combined with the latest raw data available at query time, so you always receive up-to-date results.

Real-time aggregates build on continuous aggregates

TimescaleDB continuous aggregates make it really fast to get aggregate answers by precomputing values (such as the min/max/average value over each hour). For example, if you’re collecting raw data every second, querying hourly data over the past week without continuous aggregates means you'd be processing 60 x 60 x 24 x 7 = 604,800 values at query time.  With continuous aggregates, querying hourly data over the past week means reading 24 x 7 = 168 values from the database - clearly a faster query.  But, while continuous aggregates offer huge performance advantages, they don't incorporate the very latest data (i.e., since the last time the asynchronous aggregation job ran inside the database).

Now, with real-time aggregates, a single, simple query will combine your pre-computed hourly rollups with the raw data from the last hour, to always give you an up-to-date answer.  Instead of touching 604,800 rows of raw data, the query reads 167 pre-computed rows of hourly data and 3600 rows of raw data available at query time, leading to significant performance improvements.

Real-time aggregates is now the default behavior for new continuous aggregates. To enable real-time query behavior on existing continuous aggregates, use this configuration setting:

ALTER VIEW continuous_view_name SET (timescaledb.materialized_only=false);

You can also disable the default real-time aggregates behavior - see this section of our documentation for more details.

Coming down the pipeline: real-time aggregates in-depth

Next week, we’ll publish a detailed real-time aggregates blog, showing you different ways to use it and example queries. In the meantime, you can view the full release notes and ChangeLog on GitHub – and reach out to us on Slack with questions at any time.

Data management features in (free) Community Edition

We’ve made features that simplify how you manage your data available for free, in our Community Edition, including:

• Data re-ordering: set policies to reorder data on disk, optimizing for performance, and better manage data that’s queried in an order different from when it’s collected.
• Data retention:  create, remove, or alter policies to automatically “drop” (delete) older chunks on a defined schedule.

Summary and Resources

TimescaleDB 1.7 real-time aggregates help you perform fast SQL analysis across massive, continuously aggregated datasets, while still getting a real-time view over the latest data.

Ready to try it out?

• If you're an existing TimescaleDB user, follow these upgrade instructions.
• If you are brand new to TimescaleDB, get started here.

For more information on getting started with real-time aggregates, check out our docs.

And, if you’re new to continuous aggregates - the base technology of real-time aggregates - and want to learn more, check out the continuous aggregates tutorial.

If you have any questions along the way, we are always available via our community Slack (our engineers, co-founders, and community members are active in all channels).