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. ⚡
Benchmark results before (time run1) and after (time run2) this optimization
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
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. ⚡
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. ⚡
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. ⚡
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. ⚡
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(). ⚡
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.
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale
