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

CockroachDB Zone GC TTL Floor Guard

Audit CockroachDB zones with a gc.ttlseconds below a safe floor that breaks backups and changefeeds, raise them after approval and re-read to verify.

Categories
DataInfrastructure

CockroachDB keeps old versions of every row for gc.ttlseconds before it garbage-collects them. Lower that number and the cluster reclaims disk sooner. It is a tempting knob on a storage-heavy table. The cost does not show up where you set it.

Three things depend on that retained history. All three fail only later. A BACKUP reads the cluster as of a fixed time, so a backup that runs longer than the TTL can have the versions it still needs collected out from under it. A changefeed that falls behind by more than the TTL cannot resume, because the rows it had not emitted yet are gone. An AS OF SYSTEM TIME query reaching further back than the TTL errors outright. None of this is visible on the day the TTL is lowered. It surfaces the first time a backup runs long, a changefeed stalls or someone runs a time-travel query, which is exactly when you need the history that was already thrown away.

This blueprint reads CockroachDB's own zone configurations and flags every zone that sets its own gc.ttlseconds below a floor you choose. With approval, it raises each one to the floor, then reads the zone configurations again and fails the run if any zone in scope is still below. Raising a TTL only keeps more old versions, so it never breaks a running query. The only cost is disk, which is why a human approves it.

This blueprint was created by zkasuran.

Findings

  • A zone is flagged on its own gc.ttlseconds, read from raw_config_sql in crdb_internal.zones, not on the effective value. raw_config_sql lists only the fields a zone sets for itself, so the flag fires at the exact level where someone lowered the TTL. A table that inherits a healthy TTL is not touched. Fixing a low RANGE default or a low DATABASE zone raises the effective TTL of everything that inherits from it, so the fix lands once at the source rather than being stamped onto every child.
  • RANGE default is in scope. It is the base every user object inherits, so a low default is the most dangerous case and the one a per-table scan would miss.
  • CockroachDB's own system zones use short TTLs on purpose. RANGE liveness and RANGE timeseries hold data that is meant to be collected quickly. The system database carries its own low TTLs too. Raising those wastes disk for no gain, so they are left out unless include_internal_zones is on.
  • Zone configurations are cluster wide, so one run covers every database. That is different from a statistics or index audit, which reads only the database named in the connection string.
  • The effective value is read back from full_config_sql after the change, because that is the number a backup or a changefeed actually gets. A green run means full_config_sql reports at or above the floor, not that an ALTER returned without error.

Safety

  • Every read is a query against crdb_internal.zones. Nothing touches user data.
  • A zone is only changed when auto_remediate is true, when dry_run is false and when a human resumes the Pause. An unanswered Pause fails the run after two hours and changes nothing.
  • The only statement the flow ever writes is ALTER <target> CONFIGURE ZONE USING gc.ttlseconds = <floor>, built from crdb_internal.zones.target. It sets a TTL to the floor as audited, so a zone someone raised above the floor during the approval wait would be set back down. It never drops a zone and never touches num_replicas, constraints or lease preferences.
  • CockroachDB internal zones are out of scope by default.
  • max_zones_changed bounds one pass, lowest TTL first, so a cluster with many low zones is worked through over successive runs. The leftover count is logged.
  • The remediation loop runs with concurrencyLimit: 1, so two zone writes never collide on the single system table that holds them.
  • The re-read checks only the zones changed this pass, so a leftover zone held back by the cap does not read as a failure.

How it works

  1. settings_before (io.kestra.plugin.jdbc.postgresql.Query, read-only) reads the RANGE default TTL, the number of configured zones and how many set their own gc.ttlseconds below the floor, in scope and in total.
  2. audit returns one row per zone whose own gc.ttlseconds is below the floor and in scope, with the explicit value, the effective value, the ALTER that would raise it and a rank the per run cap applies to.
  3. summary logs every flagged zone with its own TTL, its effective TTL and whether the cap covers it this pass.
  4. route (io.kestra.plugin.core.flow.If):
    • clean: logs that every configured zone is at or above the floor.
    • findings: alert posts the zones and their TTLs to Slack.
      • remediate_gate runs only when auto_remediate is true and dry_run is false. Otherwise no_change prints the exact statements it would have issued.
      • approval_gate (io.kestra.plugin.core.flow.Pause) holds for up to two hours and records who approved and under which change record. behavior: FAIL means an ignored request fails loudly rather than changing a retention setting on its own.
      • remediate (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one ALTER ... CONFIGURE ZONE at a time, up to the cap.
      • verify reads the zone configurations again. verify_result fails the run through io.kestra.plugin.core.execution.Fail when any changed zone still reads below the floor.
  5. errors alerts Slack when the audit itself cannot run.

What you get

  • The zones that trade away the history a backup, a changefeed or a time-travel read depends on, named at the level where the low value was set.
  • RANGE default checked as well, so a low cluster-wide floor is caught rather than hidden behind every table that inherits it.
  • A TTL raised to a floor you set, behind a human approval, because the change costs disk.
  • A before and after value from full_config_sql, so a green run is a measurement rather than an assumption.

Who it's for

  • Teams running CockroachDB who take backups, run changefeeds or rely on AS OF SYSTEM TIME and want the GC TTL that protects all three watched rather than discovered during an incident.
  • Platform engineers who have lowered a TTL to reclaim disk and need to know whether it is now too low for the longest backup on the cluster.
  • Anyone inheriting a cluster where someone set gc.ttlseconds on a table or the default and nobody wrote down why.

Why orchestrate this with Kestra

A GC TTL that is too low is a standing risk, not an event, so the useful thing is a schedule rather than an alert. Kestra runs the audit every day, keeps each run with its numbers, posts the zones to Slack and holds the change behind auto_remediate, dry_run and a Pause that records the approver and the change record. The per run cap spreads a large backlog across passes. The re-read makes the run report success only after full_config_sql says the TTL actually moved.

Tested end to end

On Kestra 2.0.5 OSS against a CockroachDB v24.1 single node insecure cluster reached with the PostgreSQL plugin, with a local HTTP sink standing in for Slack. One table was dropped to gc.ttlseconds = 600 to stand for a TTL lowered to save disk. A second table was left at a healthy TTL to prove the audit does not flag what is fine. Every result was read back with SHOW ZONE CONFIGURATION rather than from the Kestra log.

Run Cluster state Result
1 every zone at or above the floor Clean. 0 findings
2 shop.orders set to gc.ttlseconds = 600 1 finding on TABLE shop.public.orders at 600s against a 14400s floor, the healthy table not flagged. Alert sent, no change
3 same, auto_remediate: true, dry_run: true Same finding, the ALTER printed, the zone still at 600 when checked with SHOW ZONE CONFIGURATION
4 same, auto_remediate: true, dry_run: false, approval declined Pause held, resumed with approved: false, the zone still at 600
5 same, auto_remediate: true, dry_run: false, approved ALTER TABLE shop.public.orders CONFIGURE ZONE USING gc.ttlseconds = 14400 applied, 600 to 14400, verified against a fresh read and with SHOW ZONE CONFIGURATION
6 RANGE default set to 600 as well, cap 1 The default fixed first, the table reported as left for the next run, which the next run raised
7 one target changed, run as a role without ZONECONFIG The ALTER rejected, the re-read found the zone still at 600 and the run FAILED
8 unreachable host errors alert sent

Prerequisites

  • A CockroachDB cluster reachable over the Postgres wire protocol, which is what the PostgreSQL plugin speaks to it. There is no separate CockroachDB plugin.
  • The audit role needs to read crdb_internal.zones. That is admin scoped, so grant the VIEWCLUSTERMETADATA system privilege or use a member of admin. Raising a TTL needs ZONECONFIG on the target or admin rights.
  • Pick the floor to cover your longest BACKUP, the furthest a changefeed may fall behind and the oldest AS OF SYSTEM TIME read you depend on. The four hour default is a starting point, not a recommendation for every cluster.
  • Local testing: a single node insecure cluster is enough. cockroach start-single-node --insecure with a user table dropped to a low gc.ttlseconds reproduces every path.

Secrets

  • COCKROACH_PASSWORD: the SQL password, read by the password input's default. An insecure local node ignores it.
  • 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:26257/defaultdb?sslmode=require Any node of the cluster
db_user root The role the queries connect as
password {{ secret('COCKROACH_PASSWORD') }} SQL password
min_gc_ttlseconds 14400 The floor, in seconds
include_internal_zones false Also raise CockroachDB's own system zones
max_zones_changed 10 Per run ceiling on ALTER ... CONFIGURE ZONE
auto_remediate false Allow the TTL change
dry_run true Must be false before any zone is changed

Quick start

  1. Add the COCKROACH_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow and set jdbc_url to one of your nodes.
  3. Set min_gc_ttlseconds to a value that clears your longest backup plus a margin. Run it once as it ships and read the findings.
  4. Raise a flagged zone with auto_remediate: true and dry_run: false, then resume the Pause with approved: true and your change record.
  5. Enable the daily trigger, which always runs in safe mode.

How to extend

  • Set the floor per database by running the flow once per database with its own min_gc_ttlseconds, since a reporting warehouse and a changefeed source rarely want the same retention.
  • Read crdb_internal.kv_store_status or the range sizes and lead the Slack message with the zones whose raised TTL will cost the most disk.
  • Cross-check the floor against your scheduled backup cadence by reading SHOW SCHEDULES, so the TTL is always at least the gap between two full backups.
  • Add a branch that lowers a TTL that is far above the floor on a table nobody backs up, to reclaim disk in the other direction behind the same approval.
  • Loop over several clusters by passing a list of JDBC URLs.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.