Timescaledb: 2.25.0 Release

This release contains performance improvements and bug fixes since the 2.24.0 release. We recommend that you upgrade at the next available opportunity.

Highlighted features in TimescaleDB v2.25.0

This release features multiple improvements for continuous aggregates on the columnstore: * Faster refreshes: You can now utilize direct compress during materialized view refreshes, resulting in higher throughput and reduced I/O usage. * Efficiency: The enablement of delete optimizations significantly lowers system resource requirements. * Smaller transactions: Adjusted defaults for buckets_per_batch to 10 reduces transaction sizes, requiring less WAL holding time. * Faster queries: Smarter defaults for segmentby and orderby yield improved query performance and better compression ratio on the columnstore.

Sunsetting announcements * This release removes the WAL-based invalidation of continuous aggregates. This feature was introduced in 2.22.0 as tech preview to use logical decoding for building the invalidation logs. The feature was designed for high ingest workloads, reducing the write amplification. With the upcoming stream of improvements to continuous aggregates, this feature was deprioritized and removed. * The old continuous aggregate format, deprecated in version 2.10.0, has been fully removed from TimescaleDB in this release. Users still on the old format should read the migration documentation to migrate to the new format. Users of Tiger Cloud have already been automatically migrated.

Features * https://github.com/timescale/timescaledb/pull/8777 Enable direct compress on continuous aggregate refresh using new GUC timescaledb.enable_direct_compress_on_cagg_refresh * https://github.com/timescale/timescaledb/pull/9031 Change default buckets_per_batch on continuous aggregate refresh policy to 10 * https://github.com/timescale/timescaledb/pull/9032 Add in-memory recompression for unordered chunks * https://github.com/timescale/timescaledb/pull/9017 Move bgw_job table into schema _timescaledb_catalog * https://github.com/timescale/timescaledb/pull/9033 Add rebuild_columnstore procedure * https://github.com/timescale/timescaledb/pull/9038 Change default configuration for compressed continuous aggregates * https://github.com/timescale/timescaledb/pull/9042 Enable batch sorted merge on unordered compressed chunks * https://github.com/timescale/timescaledb/pull/9046 Allow non timescaledb namespace SET option for continuous aggregates * https://github.com/timescale/timescaledb/pull/9059 Allow configuring work_mem for background worker jobs * https://github.com/timescale/timescaledb/pull/9074 Add function to estimate uncompressed size of compressed chunk * https://github.com/timescale/timescaledb/pull/9085 Don't register timescaledb-tune specific GUCs * https://github.com/timescale/timescaledb/pull/9088 Add ColumnarIndexScan custom node * https://github.com/timescale/timescaledb/pull/9090 Support direct batch delete on hypertables with continuous aggregates * https://github.com/timescale/timescaledb/pull/9094 Enable the columnar pipeline for grouping without aggregation to speed up the queries of the form select column from table group by column. * https://github.com/timescale/timescaledb/pull/9103 Support FIRST and LAST in ColumnarIndexScan * https://github.com/timescale/timescaledb/pull/9108 Support multiple aggregates in ColumnarIndexScan * https://github.com/timescale/timescaledb/pull/9111 Allow recompression with orderby/index changes * https://github.com/timescale/timescaledb/pull/9113 Use enable_columnarscan to control columnarscan * https://github.com/timescale/timescaledb/pull/9127 Remove primary dimension constraints from fully covered chunks * https://github.com/timescale/timescaledb/pull/8710 Add SQL function to fetch continuous aggregate grouping columns * https://github.com/timescale/timescaledb/pull/9133 Allow pushing down sort into columnar unordered chunks when it is possible * https://github.com/timescale/timescaledb/pull/8229 Removed time_bucket_ng function * https://github.com/timescale/timescaledb/pull/8859 Remove support for partial continuous aggregate format * https://github.com/timescale/timescaledb/pull/9022 Remove WAL based invalidation * https://github.com/timescale/timescaledb/pull/9016 Remove _timescaledb_debug schema * https://github.com/timescale/timescaledb/pull/9030 Add new chunks to hypertable publication

Bug fixes * https://github.com/timescale/timescaledb/issues/8706 Fix planning performance regression on Postgres 16 and later on some join queries. * https://github.com/timescale/timescaledb/pull/8986 Add pathkey replacement for ColumnarScanPath * https://github.com/timescale/timescaledb/pull/8989 Ensure no XID is assigned during chunk query * https://github.com/timescale/timescaledb/pull/8990 Fix EquivalenceClass index update for RelOptInfo * https://github.com/timescale/timescaledb/pull/9007 Add validation for compression index key limits * https://github.com/timescale/timescaledb/pull/9024 Recompress some chunks on VACUUM FULL * https://github.com/timescale/timescaledb/pull/9045 Fix missing UUID check in compression policy * https://github.com/timescale/timescaledb/pull/9056 Fix split chunk relfrozenxid * https://github.com/timescale/timescaledb/pull/9058 Fix missing chunk column stats bug * https://github.com/timescale/timescaledb/pull/9061 Fix update race with background worker jobs * https://github.com/timescale/timescaledb/pull/9069 Fix applying multikey sort for columnstore when one numeric key is pinned to a Const of different type * https://github.com/timescale/timescaledb/pull/9102 Support retention policies on UUIDv7-partitioned hypertables * https://github.com/timescale/timescaledb/pull/9120 Fix for pre Postgres 17, where a DELETE from a partially compressed chunk may miss records if BitmapHeapScan is being used * https://github.com/timescale/timescaledb/pull/9121 Allow any immutable constant expressions as default values for compressed columns * https://github.com/timescale/timescaledb/pull/9121 Fix a potential "unexpected column type 'bool'" error for compressed bool columns with missing value * https://github.com/timescale/timescaledb/pull/9144 Fix handling implicit constraints in ALTER TABLE * https://github.com/timescale/timescaledb/pull/9155 Fix column generation during compressed chunk insert * https://github.com/timescale/timescaledb/pull/9129 Fix time_bucket with timezone during DST * https://github.com/timescale/timescaledb/pull/9177 Add alias for bgw_job * https://github.com/timescale/timescaledb/pull/9176 Handle NULL values in continuous aggregate invalidation more gracefully * https://github.com/timescale/timescaledb/pull/9175 Do not remove dimension constraints for OSM chunks

GUCs * enable_columnarindexscan: Enable returning results directly from compression metadata without decompression. This feature is experimental and in development towards a GA release. Not for production environments. Default: false * enable_direct_compress_on_cagg_refresh: Enable experimental support for direct compression during Continuous Aggregate refresh. Default: false * enable_qual_filtering: Filter qualifiers on chunks when complete chunk would be included by filter. Default: true

Thanks * @t-aistleitner for reporting the planning performance regression on PG16 and later on some join queries. * @vahnrr for reporting a crash when adding columns and constraints to a hypertable at the same time * @cracksalad and @eyadmba for reporting a bug with timezone handling in time_bucket