Here are the new performance improvements in TimescaleDB 2.7, starting with a 400x speed improvement for queries using the now() PostgreSQL function.
- Read about how now( ) works, how we improved performance, and how we compare with vanilla #PostgreSQL in our new blog post: How We Fixed Long-Running PostgreSQL now( ) Queries (and Made Them Lightning Fast)
Alongside 400x faster queries with now( ), other notable performance improvements in TimescaleDB 2.7 include:
-
Faster DELETES and UPDATES thanks to runtime chunk exclusion for DELETE and UPDATE statements. -
~2x faster queries using the FIRST() and LAST() aggregate hyperfunctions -
Faster reads from compressed chunks -
Query planning time improvements
See the TimescaleDB 2.7 release notes on GitHub for more details.
Here’s an overview of each one:
-
Runtime chunk exclusion for DELETE and UPDATE statements: TimescaleDB 2.7 enables runtime chunk exclusion for UPDATE / DELETE for PostgreSQL 14. Previously any constraints in UPDATE and DELETE statements that were not applied during planning would not lead to chunks being excluded. This means all those chunks would have to be processed by the executor. With this optimization we added an additional exclusion step for UPDATE/DELETE during execution similar to how we do for SELECT statements. This allows skipping chunks that are not required due to constraints.
-
FIRST( ) and LAST( ) aggregate speed improvement: TimescaleDB 2.7 optimizes how FIRST( ) / LAST( ) initializes the compare function. Previously the compare function would be looked up for every transition function call but since polymorphic types are resolved at parse time for a specific aggregate instance the compare function will not change during its lifetime. Additionally this patch also fixes a memory leak when using FIRST( ) / LAST( ) on pointer types. These changes lead to a roughly 2x speed improvement for FIRST( ) / LAST( ) and make the memory usage near-constant!
-
Faster reads from compressed chunks: While analyzing the performance of some queries, we noticed that we could remove some unneeded projections performed by Timescale. Namely, we don’t have to project the compressed scan result before decompressing the columns. This sped up the scans on compressed hypertables by tens of percent, improving basically any queries that read sufficient amounts of data. Why are they important for performance? PostgreSQL normally uses row-based storage. This means that reading each row, it has to read all of its columns. If not all columns are required, unlike the columnar storages, PostgreSQL can’t save work on this, but on the contrary has to do extra work of projection to rearrange the row and remove the unneeded columns. Projections are also one of the few operations that are JIT-compiled in PostgreSQL. Since JIT compilation is not cached, and has to happen for every chunk for every query, it also adds significant overhead.
-
Query planning time improvements: To plan a query that uses hypertables, we have to look up a lot of metadata such as chunks and dimensions and constraints in Timescale catalog tables in _timescaledb_catalog schema. We made many improvements to how we do this, and how the metadata is organized, which allowed us to plan queries faster. These improvements apply to a wide range of queries that reference hypertables.
If you have any questions or feedback on any of the above features or anything else in TimescaleDB 2.7, please let us know ! 