8 Performance Improvements in Recent TimescaleDB Releases for Faster Query Analytics

Timescale makes PostgreSQL faster for demanding workloads like time series, events, and analytics. Many of these performance improvements arise from major capabilities we’ve introduced to PostgreSQL through its extension framework. Capabilities such as continuous aggregates (incremental materialized views), compressed columnar storage, and, just announced yesterday, vectorized query execution.

But performance improvements come not only from big game-changing features but also the continuous focus on continual improvement. We’re (only somewhat) jokingly calling this “kaizen for databases.” These continual improvements have a significant impact over time.

Taking that to heart, each software release of TimescaleDB—like many pieces of software—introduces a number of improvements, including a long list of smaller ones and various bug fixes. These often don’t get the same spotlight as the main “tent-pole” features, but they matter.  

So today’s post is about this very thing: we’ll enumerate eight performance improvements we’re introducing in the latest releases of TimescaleDB (mostly the upcoming 2.13, but occasionally 2.11 and 2.12), detailing quickly how each enhances query analytics and linking to the source-code PR on GitHub that included this change.

From partial aggregates at the chunk level to optimized chunk exclusion and lighter locks during continuous aggregate refresh procedures, these improvements matter, adding up to a faster database and better developer experience.

So instead of our usual 24-minute treatise that begins with the origins of database architecture and indexing algorithms (I’m looking at you, compression deep dive, or you, vector database), we’re keeping it short and sweet today.

Let’s go.

8 Performance Improvements for Faster Query Analytics You Should Know About

1. LIMIT queries with ORDER BY

We introduced a 30-300 % performance increase for LIMIT queries that use an ORDER BY and are compatible with the partitioning of the hypertable, e.g., those hypertables that partition by time, and the query performs an order by time. ⚡

             Learn more: https://github.com/timescale/timescaledb/pull/5575

2. Vectorize aggregate for SUM

We introduced our first implementation of a vectorized aggregate for SUM. With vectorized execution, filtering and aggregating using such a SUM() operator on compressed data will be faster than ever; we’ve observed up to 30x improvements for queries such as the following. ⚡

SELECT sum(power) FROM devices
WHERE device_id > 0 AND device_id < 10

           Learn more: https://github.com/timescale/timescaledb/pull/6050

3. Partial aggregates at the chunk level

We have started using partial aggregates at the chunk level to improve performance so that rather than only performing a final aggregation step at the “root” of a query plan, we perform partial aggregations on each chunk, then aggregate these partial aggregates again. Since partial aggregates are computed on smaller input data, we’ve seen an improved performance of approximately 15 % for aggregation queries. ⚡

         Learn more: https://github.com/timescale/timescaledb/pull/5596

4. Chunk exclusion improvement

We improved chunk exclusion to allow much more efficient processing of LIMIT ORDER BY queries on partially compressed chunks and space-partitioned hypertables. This improved chunk exclusion resulted in up to 20x speed-up for these types of queries. ⚡

          Learn more: https://github.com/timescale/timescaledb/pull/5599

5. Locking discipline redesign

We redesigned our locking discipline, so the continuous aggregate refresh procedure now executes with lighter locks. With this improvement, it’s now possible to concurrently refresh multiple continuous aggregates on different hypertables for better parallelization. ⚡

        Learn more: https://github.com/timescale/timescaledb/pull/5809

6. More decompression parallel workers

We have also improved the number of decompression parallel workers, improving queries' performance on compressed chunks. ⚡

         Learn more: https://github.com/timescale/timescaledb/pull/5655

7. Even speedier real-time continuous aggregates 

We increased the performance of “real-time continuous aggregates,” which ensures that when you query a continuous aggregate, it also includes the latest data by combining, at query time, both the existing pre-computed aggregates with the latest raw data.

It does so by maintaining a watermark to separate data that has already been aggregated with newly inserted data since the last time the aggregation “refresh” was performed (that is, the watermark is an indicator of which buckets have already been materialized). This improvement introduced caching of this watermark, which improved the performance of queries that use the watermark by 2–7x. ⚡

Learn more: https://github.com/timescale/timescaledb/pull/5261

8. Columnar mini-batches sorting

We optimized how we sort our columnar mini-batches into compressed chunks. The orderby column sorts these batches; our improvements now reuse this sorting by merging compressed data to avoid unnecessary re-sorting. This optimization especially benefits queries that limit the number of returned tuples, including those that use LIMIT, first(), or last(). ⚡

Learn more: https://github.com/timescale/timescaledb/pull/5530

More info? Subscribe to our release notes

We hope you’ve enjoyed this quick overview of the optimizations and performance improvements we continually work on. So we can scale PostgreSQL’s performance, and you can focus on your application.

If you are interested in keeping more abreast of what’s happening at Timescale, subscribe to our release notes or biweekly newsletter. Interesting material only.

And if you aren’t already using Timescale, you can create a free account to get started today.