Changelog

Sep 3, 2024

TimescaleDB extension 2.16.1 for Aiven for PostgreSQL® released

Improved

Aiven for PostgreSQL®

TimescaleDB extension version 2.16.1 is now available for Aiven for PostgreSQL®. It introduces new features, performance improvements, and bug fixes on top of release 2.15.3. Upgrade to 2.16.1 at your earliest convenience to make sure you use the most robust and secure version available.

Refer to TimescaleDB release notes for 2.16.0 and TimescaleDB release notes for 2.16.1 for all the changes introduced in these release.

Key new features

  • Performance-focused optimizations for data manipulation operations (DML) over compressed chunks: Improved UPSERT performance by more than 100x in some cases and more than 1000x in some update/delete scenarios.
  • Ability to define chunk skipping indexes on non-partitioning columns of compressed hypertables: Extended chunk exclusion to use those skipping (sparse) indexes when queries filter on the relevant columns and prune chunks that don't include any relevant data for calculating the query response.
  • For use cases that require foreign keys defined:
    • Ability to add foreign keys from regular tables towards hypertables
    • Removed some locks in the reverse direction that blocked access to referenced tables while compression was running.
  • Extended Continuous Aggregates to support more types of analytical queries:
    • More types of JOINs supported
    • Additional equality operators on JOIN clauses supported
    • JOINs between multiple regular tables supported

More new features

  • #6880: Added support for the array operators used for compressed DML batch filtering.
  • #6895: Improved the compressed DML expression pushdown.
  • #6897: Added support for replica identity on compressed hypertables.
  • #6918: Removed support for PG13.
  • #6920: Reworked compression activity WAL markers.
  • #6989: Added support for foreign keys when converting plain tables to hypertables.
  • #7020: Added support for the chunk column statistics tracking.
  • #7048: Added an index scan for INSERT DML decompression.
  • #7075: Reduced decompression on the compressed INSERT.
  • #7101: Reduced decompressions for the compressed UPDATE/DELETE.
  • #7108 Reduced decompressions for INSERTs with UNIQUE constraints.
  • #7116 Used DELETE instead of TRUNCATE after compression.
  • #7134 Refactored foreign key handling for compressed hypertables.
  • #7161 Fixed mergejoin input data is out of order.

Improvements

  • Improved query performance through chunk exclusion on compressed hypertables: You can now define chunk skipping indexes on compressed chunks for any column with one of the following integer data types: smallint, int, bigint, serial, bigserial, date, timestamp, timestamptz. After you call enable_chunk_skipping on a column, TimescaleDB tracks the min and max values for that column. TimescaleDB uses that information to exclude chunks for queries that filter on that column, and would not find any data in those chunks.
  • Improved UPSERT performance on compressed hypertables: By using index scans to verify constraints during inserts on compressed chunks, TimescaleDB speeds up some ON CONFLICT clauses by more than 100x.
  • Improved performance of UPDATEs, DELETEs, and INSERTs on compressed hypertables: By filtering data while accessing the compressed data and before decompressing, TimescaleDB has improved performance for UPDATEs and DELETEs on all types of compressed chunks, as well as INSERTs into compressed chunks with unique constraints. By signaling constraint violations without decompressing or decompressing only when matching records are found in the case of UPDATEs, DELETEs, and UPSERTs, TimescaleDB 2.16.0 speeds up those operations more than 1000x in some UPDATE/DELETE scenarios, and 10x for UPSERTs.
  • Ability to add foreign keys from regular tables to hypertables with support for all types of cascading options; useful for hypertables that partition using sequential IDs and need to reference those IDs from other tables.
  • Lower locking requirements during compression for hypertables with foreign keys: Advanced foreign key handling removes the need for locking referenced tables when new chunks are compressed. DML is no longer blocked on referenced tables while compression runs on a hypertable.
  • Improved support for queries on Continuous Aggregates
    • INNER/LEFT and LATERAL JOINs are now supported.
    • Ability to join with multiple regular tables
    • Ability to have more than one equality operator on JOIN clauses

Bug fixes

  • #7182 Fixed untier_chunk for hypertables with foreign keys.
  • #6987 Fixed REASSIGN OWNED BY for background jobs.
  • #7018: Fixed search_path quoting in the compression defaults function.
  • #7046: Prevented locking for compressed tuples.
  • #7055: Fixed the scankey for segment by columns, where the type constant is different to variable.
  • #7064: Fixed the bug in the default order by calculation in compression.
  • #7069: Fixed the index column name usage.
  • #7074: Fixed the bug in the default segment by calculation in compression.