Query icon
Log icon
If icon
SlackIncomingWebhook icon
Pause icon
Loop icon
Fail icon
Schedule icon

TimescaleDB Chunk Time Interval Tuning Guard

Find TimescaleDB hypertables with mis-sized chunks, compute a corrected chunk_time_interval, set it behind an approval and verify the catalog.

Categories
DataInfrastructure

A hypertable's chunk_time_interval is chosen once, at create time, against a guess about how much data will arrive. Ingest rates change over a table's life and almost nobody revisits the setting. Get it wrong in one direction and each chunk is enormous, so queries and compression chew memory while a single chunk drop removes far more range than intended. Get it wrong the other way and the table turns into thousands of tiny chunks, so planning time climbs because the planner considers every chunk and the catalog carries a row set for each one.

This blueprint measures the real size of recent closed chunks with chunks_detailed_size, divides by the time span those chunks cover to get bytes per unit of time. It reports every hypertable whose average chunk falls outside a target size band. It computes the interval that would hold about the target bytes, then behind a dry run and an approval Pause calls set_chunk_time_interval, re-reads timescaledb_information.dimensions and fails the run if the stored interval did not change.

This blueprint was created by zkasuran.

What this fixes and what it cannot fix

set_chunk_time_interval changes the interval used for chunks created from that point on. It does not re-chunk data that is already stored. merge_chunks and split_chunk (2.18 and later) can rewrite stored chunks, but this flow calls neither.

So the verification step in this flow proves one thing precisely: the setting landed, read back from timescaledb_information.dimensions. It does not prove the table was repaired, because the existing chunks keep their original ranges and their original sizes. The re-audit after a change is expected to still report the hypertable outside the band, because it is still measuring those old chunks. That is correct behaviour, not a failure.

The flow also reads any chunk created during the run and reports the span it covers, which is the one piece of direct evidence that the new interval is in force. On a quiet table the honest answer is zero new chunks.

One detail to expect when the first new chunk does arrive: it is usually shorter than the new interval. TimescaleDB aligns chunk windows to the interval, then clips a new chunk where it meets a chunk that already exists, so the first one fills the gap and the second is full width. Measured on the fixture below, 25200 seconds then 154800 seconds against a new interval of 154800.

To move the stored data as well, copy the table through a fresh hypertable created with the new interval. You can also let a retention policy age the old chunks out while new ones arrive at the corrected size. Neither is done here, because both move data and belong behind their own review.

Findings

Status What the measurement shows Why it matters
TOO_SMALL Average sampled chunk is below the target times the lower band factor Every query plan considers all chunks, so planning time grows with the chunk count. The catalog also carries a row set per chunk
TOO_LARGE Average sampled chunk is above the target times the upper band factor Chunk indexes and working sets no longer fit comfortably in memory. Dropping one chunk also removes far more range than intended
OK Average sampled chunk sits inside the band The interval still matches the ingest rate
SKIPPED Fewer closed chunks than min_sample_chunks There is not enough history to measure an ingest rate, so the table is reported and never resized

The sample is the most recent closed chunks only, so an ingest rate from a year ago cannot drive today's proposal. A chunk whose window has not closed yet is excluded, because a half filled chunk understates the real size. Compressed chunks are counted at their compressed size through chunks_detailed_size, which is what the instance actually holds. The count of compressed chunks in the sample is reported beside the verdict.

TimescaleDB guidance is that the indexes and working set of a chunk fit comfortably in memory, roughly a quarter of the RAM on the instance. That is what target_chunk_bytes represents. Set it from your own instance rather than taking the 256 MiB default.

Safety

  • Read-only unless auto_remediate is true and dry_run is false. The audit and the plan are two SELECT queries.
  • The write also needs a human to approve the Pause. An unanswered resume reads as declined, because the gate is approved ?? false.
  • The plan prints every statement before anything runs, so the change is reviewable from a dry run log.
  • min_new_interval and max_new_interval clamp the computed value, so no measurement can push a table to minute sized chunks or to a range nobody can drop usefully.
  • min_change_factor stops the flow rewriting the setting for a small correction.
  • No data is moved, copied or deleted. The only write is one catalog setting per hypertable.
  • Tables that are not hypertables cannot appear, because the scan reads timescaledb_information.hypertables. Continuous aggregate materialization hypertables are excluded as well, since their sizing follows the aggregate definition.

How it works

  1. audit (io.kestra.plugin.jdbc.postgresql.Query, read-only) joins timescaledb_information.hypertables, the first time dimension from timescaledb_information.dimensions, the closed chunks from timescaledb_information.chunks and their real sizes from chunks_detailed_size. It takes the most recent max_sample_chunks closed chunks per hypertable, divides total bytes by total span, then classifies each table TOO_SMALL, TOO_LARGE, OK or SKIPPED.
  2. plan returns one set_chunk_time_interval call per hypertable that needs correcting, with the current interval beside the computed one.
  3. summary logs every hypertable, its chunk count, its average chunk size, the verdict and the plan.
  4. route (io.kestra.plugin.core.flow.If):
    • inside the band: logs it.
    • outside the band: alert posts the findings to Slack.
      • remediate_gate opens only when auto_remediate is true and dry_run is false.
      • approval_gate (io.kestra.plugin.core.flow.Pause) waits for a human.
      • remediate_intervals (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one set_chunk_time_interval per iteration.
      • recheck reads timescaledb_information.dimensions again and counts how many targeted hypertables still report the old interval.
      • new_chunks reports any chunk created during the run and its span.
      • reaudit re-scans everything for the log.
      • verify_result fails the run through io.kestra.plugin.core.execution.Fail when the stored interval did not change. Otherwise it logs what landed, plus the plain statement that existing chunks were not re-chunked.
  5. errors alerts Slack when the database cannot be reached or a step fails.

What you get

  • A verdict per hypertable built from measured bytes, not from a rule of thumb about the interval.
  • The corrected interval computed from the observed bytes per day, clamped by a floor and a ceiling you set.
  • The chunk count, the average, the smallest and the largest sampled chunk, so a table with uneven chunks is visible rather than averaged away.
  • A write that one human approves, applied one statement at a time.
  • A verification that reads the catalog back and fails the run if the setting did not change, with an honest statement of what that does and does not prove.
  • Hypertables inside the band and tables that are not hypertables left untouched.

Who it's for

  • Teams running TimescaleDB where the hypertables were created years ago and the traffic has moved since.
  • Platform teams who inherited hypertables from several services, each with a different interval chosen by a different person.
  • Anyone whose query planning time grew without the data growing. Also for anyone who found that dropping one chunk removed a quarter of the history.

Why orchestrate this with Kestra

Chunk sizing drifts quietly. Nothing errors, nothing alerts, the numbers stay correct, the queries just get slower and the chunk drops get blunter. A cron script could read the catalog, but the useful version of this needs the measurement, a computed proposal, a human who sees both intervals before agreeing, then a read back that fails loudly if the write did not take. Kestra gives it the schedule, the dry_run and auto_remediate gates, the approval Pause and the Slack alert in one flow, with the whole decision visible in the run log afterwards.

Tested end to end

On Kestra 2.0.5 OSS against TimescaleDB 2.30.2 on PostgreSQL 16, with a local HTTP sink standing in for Slack. Runs used target_chunk_bytes: 1048576 so the fixtures stay small, with the shipped band factors 0.25 and 4.0, giving a band of 256 KiB to 4 MiB.

Four hypertables and one plain table were seeded, measured with chunks_detailed_size before any run:

Table Interval before Closed chunks Average chunk Expected verdict
public.sensor_fine 1 hour 239 24 kB TOO_SMALL
public.sensor_coarse 60 days 4 6202 kB TOO_LARGE
public.sensor_ok 1 day 6 1191 kB OK, untouched
tuned.metrics_new 1 day 2 24 kB SKIPPED, too few chunks to judge
public.plain_counts not a hypertable n/a n/a never appears
Run Execution Inputs Result
1 4yL4VNrY8XJlNDkAI2E4zp defaults plus the small target 3 hypertables scanned, 2 outside the band. Plan: sensor_coarse 60 days to 9 days 22:00:00, sensor_fine 1 hour to 1 day 19:00:00. Alert sent. psql confirmed all three intervals unchanged afterwards
2 54ymNa654xf3yayJC7aLoN auto_remediate: true, dry_run: false, resumed with no answer Paused, then took the declined branch. psql: every interval unchanged. An unanswered resume does not read as approval
3 3GLc49t4sEOzTJzHVu2Iqx auto_remediate: true, dry_run: false, approved 2 calls ran. recheck read dimensions back as 154800s and 856800s, 0 still wrong. psql: sensor_fine 1 hour to 1 day 19:00:00, sensor_coarse 60 days to 9 days 22:00:00, sensor_ok still 1 day. new_chunks honestly reported 0 chunks created during the run. The re-audit still reported both tables outside the band, measured from the old chunks, which is the documented behaviour
4 14UM9wC7ks2itAO6Vpjf3s schemas: tuned 2 scanned, 0 outside the band. metrics_good OK at 1191 kB, metrics_new SKIPPED on 2 closed chunks even though its chunks are 24 kB. Clean branch, no alert, no plan
5 3tzdardMQkte79WTQTB702 auto_remediate: true, dry_run: false, straight after run 3 Both tables still report outside the band, the plan is empty because the computed interval now equals the stored one, so the remediation gate never opened. The flow does not churn the setting
6 1XgLtEcpS3ZTaJuLUEwUT4 as a role that is not the hypertable owner, after resetting sensor_fine to 1 hour set_chunk_time_interval was refused with "must be owner of hypertable". recheck reported 1 still wrong, wanted 154800s, catalog reports 3600s, verify_result failed the run and the errors block posted to Slack. psql: still 3600s
7 2oUhBzTrv6n7XamodS5Iz6 unreachable host audit failed on the connection, errors posted to Slack, run FAILED

Proof the new interval takes effect, from psql after run 3. A row was inserted past the newest chunk boundary on sensor_fine, where every existing chunk spans 3600 seconds:

Chunk Range Span
_hyper_1_239_chunk 2026-10-14 00:00 to 01:00 3600s, an old chunk
_hyper_1_251_chunk 2026-10-14 01:00 to 08:00 25200s, the first new chunk, clipped where it meets the old one
_hyper_1_252_chunk 2026-10-14 08:00 to 2026-10-16 03:00 154800s, the full new interval

So new chunks really are cut at the new interval. The first one after a change is shorter, because its aligned window overlaps the last chunk that already exists and TimescaleDB clips it to the gap. sensor_coarse behaved the same way, with its first new chunk at 475200s inside an 856800s window.

Runs 4 then 1 are the before and after of the detector itself: zero findings on well sized hypertables, then exactly the two mis-sized ones on the degraded schema, with the well sized table and the plain table left out. That is the proof the check can fail.

Prerequisites

  • TimescaleDB 2.x. The flow reads timescaledb_information.hypertables, .dimensions, .chunks and chunks_detailed_size. It calls set_chunk_time_interval to make the change.
  • Hypertables whose first dimension is timestamp with time zone, the common case. A hypertable partitioned on a plain timestamp, a date or an integer is not scanned, because its interval is a bigint in a unit only the application knows.
  • The audit needs a role that can read those views. set_chunk_time_interval needs the owner of the hypertable.
  • Local testing:
    • docker run -d --name ts -p 5432:5432 -e POSTGRES_PASSWORD=ts -e POSTGRES_DB=metrics timescale/timescaledb:latest-pg16;
    • create a hypertable with chunk_time_interval => INTERVAL '1 hour', insert a few days of sparse rows, then run with a small target_chunk_bytes.

Secrets

  • TIMESCALE_PASSWORD: the database password, read by the db_password input default.
  • SLACK_WEBHOOK_URL: used by alert and the errors block.
  • In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input Default Purpose
jdbc_url jdbc:postgresql://localhost:5432/metrics TimescaleDB database
db_user postgres Database user
db_password secret('TIMESCALE_PASSWORD') Database password
schemas public Comma separated schemas to scan
target_chunk_bytes 268435456 Size each new chunk should aim at, 256 MiB
band_low_factor 0.25 Below target times this factor is TOO_SMALL
band_high_factor 4.0 Above target times this factor is TOO_LARGE
min_sample_chunks 3 Fewer closed chunks than this is SKIPPED
max_sample_chunks 10 Most recent closed chunks used to measure
min_new_interval 1 hour Floor on the computed interval
max_new_interval 30 days Ceiling on the computed interval
min_change_factor 1.5 Minimum ratio between old and new before proposing
auto_remediate false Allow the plan to run
dry_run true Must be false for anything to run

Quick start

  1. Add the TIMESCALE_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow, set jdbc_url and schemas.
  3. Set target_chunk_bytes to about a quarter of the instance memory divided by the number of hypertables you query together.
  4. Run with the defaults and read the plan in the summary log.
  5. Agree the proposed intervals with the table owners, then run with auto_remediate: true and dry_run: false and approve the Pause.
  6. Enable the daily trigger, which always runs in safe mode.

How to extend

  • Add the copy through a new hypertable as a second, separately approved flow, so stored data moves to the new sizing as well.
  • Weight the sample by row count instead of bytes when a table's rows vary a lot in width.
  • Record each run's measured bytes per day into a table and chart how the ingest rate moves, which turns one proposal into a trend.
  • Scan several databases from one run by looping over a list of JDBC URLs.
  • Raise min_sample_chunks on a fleet where tables are created often, so a new table is never tuned early.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.