Relax lock during CAgg invalidation log processing by kpan2034 · Pull Request #9701 · timescale/timescaledb

kpan2034 · 2026-04-30T17:18:12Z

When processing the continuous aggregate invalidation log, we currently take a ShareUpdateExclusiveLock on the materialized hypertable to prevent logs being cut by concurrent refreshes on the same continuous aggregate. However, this can also block other concurrent operations.

We can instead take a RowExclusiveLock on the catalog table entry for the continuous aggregate. This has the same effect as earlier - serializing cagg log processing, without the need for the heavy lock that was taken previously.

pnthao

LGTM

github-actions · 2026-05-16T00:37:40Z

@akuzm, @natalya-aksman: please review this pull request.

Powered by pull-review

codecov · 2026-05-18T14:38:50Z

Codecov Report

❌ Patch coverage is 78.26087% with 5 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
src/ts_catalog/continuous_agg.c	76.19%	3 Missing and 2 partials ⚠️

📢 Thoughts on this report? Let us know!

gayyappan · 2026-05-28T20:06:04Z

 extern Tablespace *ts_hypertable_get_tablespace_at_offset_from(int32 hypertable_id,
+
 															   Oid tablespace_oid, int16 offset);
+extern TSDLLEXPORT bool ts_lock_hypertable_tuple(int32 htid, ItemPointer tid,


do we need this change? I don't see this being used outside hypertable.c

gayyappan · 2026-05-28T20:48:08Z

 step "Refresh2_sel"	{ select * from continuous_view_2 where bkt = 0 or bkt > 30 order by bkt; }

-#locking the materialized table will block refresh1
+#locking the materialized table will not block refresh1


this will still block in Txn3, right?

When processing the continuous aggregate invalidation log, we currently take a ShareUpdateExclusiveLock on the materialized hypertable to prevent logs being cut by concurrent refreshes on the same continuous aggregate. However, this can also block other concurrent operations. We can instead take a RowExclusiveLock on the catalog table entry for the continuous aggregate. This has the same effect as earlier - serializing cagg log processing, without the need for the heavy lock that was taken previously.

Removed unused I2 step in cagg_multi_iso test. Removed waitpoint in cagg_concurrent_refresh test.

kpan2034 · 2026-06-05T21:27:52Z

The changed test cagg_concurrent_refresh was simplified by removing the wait point, which is why the test still passes on the old code. This is expected.

@Fabian-2596

## 2.28.0 (2026-06-16) This release contains performance improvements and bug fixes since the 2.27.2 release. We recommend that you upgrade at the next available opportunity. **Highlighted features in TimescaleDB v2.28.0** * **Faster `first()` and `last()` queries on compressed data.** TimescaleDB derives `first(value, time)` and `last(value, time)` aggregates straight from the columnstore's batch metadata, skipping batch decompression entirely. For the "latest reading per series" lookups that time-series workloads run constantly, that means meaningfully faster recency queries with no changes to your SQL queries. * **Lighter, less disruptive continuous aggregate refreshes.** `refresh_continuous_aggregate()` can now run incrementally in batches — the same behavior refresh policies already use — enabling breaking large manual refreshes into smaller chunks (tunable via `buckets_per_batch`, `max_batches_per_execution`, and `refresh_newest_first`) instead of one heavy operation. Refreshes also now take a lighter lock while processing the invalidation log, so they no longer block unrelated concurrent operations on the same continuous aggregate, improving behavior for concurrent workloads. * **Vectorized execution now covers `CASE` expressions.** TimescaleDB's columnar executor can now evaluate `CASE ... WHEN` expressions directly on compressed data, so queries using conditional logic stay on the fast vectorized path instead of falling back to slower row-by-row decompression. This speeds up a common pattern — conditional aggregations and computed columns over compressed history — with no query changes needed. * **Add new aggregations to a continuous aggregate without rebuilding it.** You can now run `ALTER MATERIALIZED VIEW <cagg> ADD COLUMN <name> <type> GENERATED ALWAYS AS (<aggregate>) STORED` to add a new computed aggregate to an existing continuous aggregate in place — no more dropping and recreating the whole aggregate just to track one more metric. New data populates the column going forward, letting your rollups evolve alongside your application. (Existing rows start as `NULL`; a forced refresh backfills them when you need historical values.) **Deprecation Notice: PostgreSQL 15 Support** This release marks the final minor version of TimescaleDB that will support PostgreSQL 15. Starting with our next release, version 2.29.0, we will officially drop support for Postgres 15, and only support Postgres 16, 17, and 18; however, all future patch releases within the current 2.28 version cycle will continue to fully support it. We recommend planning your PostgreSQL upgrades accordingly to ensure a smooth transition. **Deprecation Notice: `chunk_constraint` Catalog Table** Please note that the `_timescaledb_catalog.chunk_constraint` table has been dropped and temporarily replaced by a view, which introduces a change to the underlying objects while maintaining current query behavior. However, this compatibility view will be completely removed in a future release. To ensure your queries remain compatible moving forward, we strongly advise transitioning to the stable contracts provided by our [informational views](https://www.tigerdata.com/docs/reference/timescaledb/informational-views). **Backward-Incompatible Changes** * [#9934](#9934) Remove adaptive chunking **Features** * [#4054](#4054) Support `ANALYZE` and `VACUUM` on continuous aggregates by redirecting to the underlying materialization hypertable * [#9125](#9125) Increase the parallelism of `SELECT` queries over compressed hypertables to approximately match the uncompressed data size * [#9410](#9410) Mark `hypertable` and `chunk` as user catalog tables * [#9416](#9416) Support some forms of `CASE` expression in columnar aggregation and grouping * [#9580](#9580) Add `first` / `last` sparse indexes to compression * [#9784](#9784) Use `first` / `last` sparse index for `orderby` metadata on new compressed chunks * [#9668](#9668) Allow database owner to configure hypertables and policies * [#9701](#9701) Relax lock during continuous aggregate invalidation log processing * [#9730](#9730) Add in-memory observability for compressed chunks * [#9735](#9735) Improve `GapFill` row count estimate * [#9821](#9821) Allow subquery results which are exec params as GapFill arguments * [#9825](#9825) Support `ADD COLUMN` on continuous aggregates * [#9842](#9842) Suppress continuous aggregate invalidation tracking during bulk loads * [#9878](#9878) Remove `chunk_constraint` catalog tracking for foreign keys * [#9893](#9893) Remove `chunk_constraint` catalog tracking for non-dimensional constraints * [#9903](#9903) Incremental refresh for `refresh_continuous_aggregate()` * [#9915](#9915) Remove `_timescaledb_catalog.chunk_constraint` table * [#9938](#9938) Add `rebuild_sparse_index` function * [#9964](#9964) Add a function to lock OSM chunk's dimension slice * [#9980](#9980) Support `first/last(value, time)` in `ColumnarIndexScan` **Bugfixes** * [#9708](#9708) Guard time bucket parameter handling against bad input * [#9745](#9745) Check constraints when adding unique constraints to chunks * [#9890](#9890) Fix incremental refresh batch boundaries to align with variable-width buckets and start only where a chunk and an invalidation overlap * [#9914](#9914) Fix use-after-free in segmentwise recompression * [#9929](#9929) Fix background jobs being bumped in the queue forever and never running * [#9955](#9955) Fix wrong results when using Batch Sorted Merge with no first-last index on a non-leading order by column * [#9967](#9967) Block upgrade after downgrade with first/last indexes present * [#9976](#9976) Fix wrong results when comparing a date column to a `timestamptz` value * [#9977](#9977) Fix `COPY WHERE` into a hypertable with dropped columns * [#9981](#9981) Fix set-returning functions in the sort key of `ColumnarScan` * [#9982](#9982) Reject `ALTER TABLE ... INHERIT` when the parent is a hypertable * [#9984](#9984) Fix handling of `NOT VALID NOT NULL` constraint for query optimization * [#9986](#9986) Handle `MERGE WHEN NOT MATCHED BY SOURCE` on hypertables * [#9988](#9988) Fix `time_bucket_gapfill` function detection * [#10003](#10003) Block unsafe updates of unique columns on compressed chunks * [#10024](#10024) Fix `approximate_row_count` handling of Infinity * [#10025](#10025) Fix rename on compressed continuous aggregates * [#10026](#10026) Fix chunk skipping near `PG_INT64_MAX` **New Settings** * `skip_cagg_invalidation`: skip continuous aggregate invalidation tracking for DML and DDL in the current session/transaction. Off by default. * `stats_max_chunks`: set the per-database compressed chunk statistics cache capacity. Defaults to 1024 chunks; set to 0 to disable the feature. **Thanks** * @Fabian-2596 for suggesting more accurate GapFill row count estimate * @otjdiepluong for fixing spelling mistakes in timescaledb source code comments * @scimad and @Nosfistis for suggesting expanding coverage for gapfill arguments

The last permutation released L1 (refreshing cagg_6h, in txn 3) and L2 (refreshing cagg_1d, in txn 1) from the same lock on the materialization invalidation log simultaneously. Whether L2's scan of the hypertable invalidation log saw the cagg_1d invalidation published by L1's final commit was then pure scheduling: usually L2 read first and reported "already up-to-date", but on loaded runners L1 could commit first, making L2 refresh cagg_1d and diverge from the expected output. Synchronize with a lock on cagg_6h's materialization hypertable instead. An initial cagg_6h refresh of Jan 3 first publishes an invalidation so L2 has real work. L1 then blocks on the lock mid-txn 3 -- after deleting its processed log entries but before the commit that publishes the downstream invalidations -- while L2 moves, cuts and collects its log entries concurrently, blocking only when it starts materializing Jan 3 from cagg_6h. The outcome is deterministic: L2's invalidation processing finishes while L1 is uncommitted, and its Jan 3 materialization reads only buckets L1's Jan 1 refresh never touches. A regression of the txn3-vs-txn2 non-blocking property (timescale#9701) would change the processed invalidation set and fail the test. Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

kpan2034 requested review from gayyappan and pnthao April 30, 2026 17:18

kpan2034 self-assigned this Apr 30, 2026

kpan2034 added the Continuous Aggregate label Apr 30, 2026

pnthao approved these changes May 6, 2026

View reviewed changes

kpan2034 changed the title ~~Relax cagg lock~~ Relax lock during CAgg invalidation log processing May 16, 2026

kpan2034 force-pushed the relax-cagg-lock branch from 25995b8 to 5c702b7 Compare May 16, 2026 00:37

kpan2034 marked this pull request as ready for review May 16, 2026 00:37

kpan2034 requested a review from a team May 16, 2026 00:37

github-actions Bot requested review from akuzm and natalya-aksman May 16, 2026 00:37

kpan2034 force-pushed the relax-cagg-lock branch from 5c702b7 to c033098 Compare May 16, 2026 00:37

gayyappan requested a review from pnthao May 18, 2026 03:05

kpan2034 force-pushed the relax-cagg-lock branch from c033098 to 70b78cb Compare May 18, 2026 14:04

kpan2034 force-pushed the relax-cagg-lock branch from 70b78cb to a2660cb Compare May 18, 2026 23:13

gayyappan reviewed May 28, 2026

View reviewed changes

pnthao approved these changes May 29, 2026

View reviewed changes

Comment thread src/hypertable.c Outdated

kpan2034 force-pushed the relax-cagg-lock branch from a2660cb to 177b3e6 Compare June 1, 2026 05:37

vineethapai added this to the v2.28.0 milestone Jun 1, 2026

kpan2034 force-pushed the relax-cagg-lock branch from 177b3e6 to 6c84541 Compare June 2, 2026 22:00

kpan2034 force-pushed the relax-cagg-lock branch from 6c84541 to 9cbb6c2 Compare June 2, 2026 22:00

kpan2034 commented Jun 2, 2026

View reviewed changes

Comment thread tsl/test/isolation/specs/cagg_multi_iso.spec

kpan2034 commented Jun 2, 2026

View reviewed changes

Comment thread tsl/test/isolation/specs/cagg_multi_iso.spec

kpan2034 commented Jun 2, 2026

View reviewed changes

Comment thread tsl/test/isolation/specs/cagg_multi_iso.spec

melihmutlu approved these changes Jun 4, 2026

View reviewed changes

Comment thread tsl/test/isolation/expected/cagg_multi_iso.out Outdated

gayyappan reviewed Jun 4, 2026

View reviewed changes

Comment thread src/hypertable.c Outdated

Clean up isolation tests

d8571ef

Removed unused I2 step in cagg_multi_iso test. Removed waitpoint in cagg_concurrent_refresh test.

kpan2034 force-pushed the relax-cagg-lock branch from 36abfff to d8571ef Compare June 5, 2026 20:13

kpan2034 requested a review from gayyappan June 5, 2026 20:26

gayyappan approved these changes Jun 8, 2026

View reviewed changes

kpan2034 enabled auto-merge (squash) June 8, 2026 14:22

kpan2034 merged commit f3dc8dc into timescale:main Jun 8, 2026
58 of 61 checks passed

surister mentioned this pull request Jun 9, 2026

Release 2.28.0 #10010

Merged

timescale-automation added the released-2.28.0 Released in 2.28.0 label Jun 16, 2026

Uh oh!

Conversation

kpan2034 commented Apr 30, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

pnthao left a comment

Choose a reason for hiding this comment

Uh oh!

github-actions Bot commented May 16, 2026

Uh oh!

codecov Bot commented May 18, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

gayyappan May 28, 2026

Choose a reason for hiding this comment

Uh oh!

gayyappan May 28, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

kpan2034 commented Jun 5, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

kpan2034 commented Apr 30, 2026 •

edited

Loading

codecov Bot commented May 18, 2026 •

edited

Loading