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

CockroachDB Stale Table Statistics Guard

Audit stale, missing or wrong CockroachDB table statistics, alert Slack, collect fresh statistics per table within a cap and verify they landed.

Categories
DataInfrastructure

CockroachDB's cost based optimizer picks a plan from the statistics it holds about each table. After a bulk ingest, a restore or with automatic collection switched off, those statistics describe a table that no longer exists. The optimizer thinks a table holds a thousand rows when it holds ten million, so it picks a full scan or the wrong join order. Query latency jumps. Nothing errors. No schema changed. The plan is simply built on a number that stopped being true.

This is one of the few database faults with no signal of its own. There is no failed statement, no lock, no alert. The slow query looks like a slow query. The only place the cause is visible is the catalog, in the age of the newest statistic and the row count stored in it.

This blueprint reads system.table_statistics for every table in the connected database and flags three things: a table with no statistics at all, a table whose newest statistic is older than a threshold and a table whose stored row count is out by more than a factor from the rows actually there. It reads the automatic collection settings as well, because a cluster with sql.stats.automatic_collection.enabled set to false makes every table stale in turn. Then it collects fresh statistics per table within a per run cap, re-enables automatic collection, reads the catalog again and fails the run if a target's newest statistic did not move.

This blueprint was created by zkasuran.

Findings

  • The row count stored in a statistic is the one number you cannot use to decide whether a table is worth collecting. A table that grew from 500 rows to 2 million carries a stored count of 500, so any size filter built on the stored count skips exactly the tables that are worst wrong. The size floor here is applied to the larger of the stored count and the measured count, never to the stored count alone.
  • Age alone is a proxy. A statistic collected ten minutes before a bulk load is fresh and wrong. One collected two days ago on a table nobody writes to is old and correct. So the run has two detectors. The cheap one is age, which needs only the catalog. The exact one counts the rows in tables the age check already considered fine, which is the only set where a count can tell you something new.
  • There is no crdb_internal view for the statistics catalog. system.table_statistics is the source, keyed by tableID, with camel case column names that have to be quoted. SHOW STATISTICS FOR TABLE is the readable form of the same rows for one table.
  • crdb_internal.tables is cluster wide but it lists views alongside tables and carries no type column. CREATE STATISTICS on a view fails with is not a table. information_schema.tables has the type but only for the connected database, so the scope query joins the two and keeps BASE TABLE rows. That is also why a run audits one database, the one in the JDBC URL.
  • Automatic collection has its own floor. sql.stats.automatic_collection.min_stale_rows defaults to 500 rows and fraction_stale_rows to 0.2, so even with automatic collection on, a large table needs a fifth of its rows to change before the refresher looks at it. Both are reported every run next to the enabled flag.

Safety

  • Collecting statistics is not a destructive change. CREATE STATISTICS reads a sample of the table and writes a new row to the statistics catalog. No row, no schema, no index and no query is modified. The old statistics are simply superseded. The cost is the sampling scan.
  • That is why this flow has no approval Pause, which is a deliberate difference from the guards in this family that drop a partition, cancel a query or lower a replica count. Asking a human to approve a statistics refresh would train people to click through a prompt that never has a wrong answer. The gate that matters here is cost, not consent, so the controls are the per run cap, the serialised loop and the opt-in row counting.
  • Nothing is collected unless auto_remediate is true and dry_run is false. In reporting mode the run prints the exact statements it would have issued.
  • max_tables_collected bounds one pass, so a cluster with 400 stale tables is worked through over successive runs rather than sampled all at once. The leftover count is logged and alerted.
  • The collection loop runs with concurrencyLimit: 1, so two samplings never contend.
  • The only statements the flow ever writes are CREATE STATISTICS kestra_stats_refresh FROM <table> against names read from the catalog and SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true. It never deletes a statistic, never changes a different setting and never touches user data.
  • The row counting path is off by default because it is a full primary index scan per table.

How it works

  1. settings_before (io.kestra.plugin.jdbc.postgresql.Query, read-only) reads sql.stats.automatic_collection.enabled, min_stale_rows and fraction_stale_rows from crdb_internal.cluster_settings, plus the number of tables in scope.

  2. inventory lists the tables whose newest statistic is inside the age threshold, oldest first, capped by max_tables_measured. It returns nothing at all when measure_live_rows is false.

  3. live_counts runs one UNION ALL of count(*) over those candidates, so the cluster answers one query rather than one per table.

  4. audit classifies every table in scope and returns only the ones that need collecting.

    Verdict Meaning Detector
    NEVER_COLLECTED No statistic exists, so the optimizer is working from defaults Catalog
    STALE_AGE The newest statistic is older than max_statistics_age_hours Catalog
    ROW_COUNT_DRIFT The statistic is recent but its row count is out by more than row_drift_factor Live count
  5. summary logs every flagged table with its newest statistic, its stored row count, the live count and whether the cap covers it this pass.

  6. route (io.kestra.plugin.core.flow.If):

    • clean: logs that every table has a current statistic and automatic collection is on.
    • findings: alert posts the verdicts and the numbers to Slack.
      • remediate_gate runs only when auto_remediate is true and dry_run is false. There is no approval step, because nothing destructive happens here.
      • enable_automatic_collection sets sql.stats.automatic_collection.enabled back to true when it is off and enforce_automatic_collection allows it.
      • remediate_collect (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs one CREATE STATISTICS per table, up to the cap.
      • verify re-reads the newest statistic for every targeted table and compares it with the timestamp the audit saw. verify_result fails the run when any target did not advance or when automatic collection is still off.
  7. errors alerts Slack when the audit itself cannot run.

What you get

  • The tables whose plans are being chosen from numbers that are no longer true, split by why: never collected, too old or recent but wrong.
  • The automatic collection settings beside the per table findings, so a cluster where collection was switched off months ago reads as one cause rather than fifty symptoms.
  • Fresh statistics collected within a cap you set, so a large cluster is worked through over several runs instead of being sampled in one.
  • A before and after timestamp per table from the catalog, so a green run is a measurement rather than an assumption.

Who it's for

  • Teams running CockroachDB who have watched a query get slower with no deploy, no schema change and no error to point at.
  • Anyone who has just finished a bulk ingest, a restore or a backfill, which is when the stored row counts are furthest from the truth.
  • Platform engineers who need to know whether automatic collection is actually on across every cluster they run.

Why orchestrate this with Kestra

Stale statistics are a state that gets worse quietly, so the useful thing is a schedule rather than an alert. Kestra runs the audit every six hours, keeps each run with its numbers, posts the findings to Slack and holds the collection behind auto_remediate and dry_run. The per run cap means the schedule spreads a big backlog across passes instead of hammering the cluster once. The re-read makes the run report success only after the catalog says the statistics actually moved.

Tested end to end

On Kestra 2.0.5 OSS against a CockroachDB v24.1 single node cluster reached with the PostgreSQL plugin, with a local HTTP sink standing in for Slack. The degraded states were built the way they happen in production: collect a baseline, switch automatic collection off, then bulk load so the stored row count is left behind. Two tables were left current to prove the audit does not flag what is fine. Every result was read back with SHOW STATISTICS FOR TABLE rather than from the Kestra log.

Run Cluster state Result
1 3 tables, all with current statistics, automatic collection on Clean. 0 findings
2 collection off, orders bulk loaded to 200,500 rows behind a statistic 3 days old saying 500 1 finding, STALE_AGE on shop.public.orders, collection reported false. The 2 current tables not flagged. Alert sent, no change
3 same, auto_remediate: true, dry_run: true Same finding, both statements printed, newest statistic and the setting unchanged in the SQL client
4 same, auto_remediate: true, dry_run: false CREATE STATISTICS ran, stored row count 500 to 200,500, setting back to true. EXPLAIN estimate for the scan went from 500 to 200,500
5 2 stale tables, max_tables_collected: 1 Oldest collected, the other reported as left for the next pass, which the next run picked up
6 events bulk loaded to 205,000 rows behind a statistic 30 seconds old saying 5,000, measure_live_rows: true ROW_COUNT_DRIFT at 41x on a table the age check passed. The other 2 counted and not flagged
7 same, remediated Stored row count 5,000 to 205,000, verified in the catalog
8 a new table with no statistics at all NEVER_COLLECTED, collected at 1,000 rows, verified
9 collection off, run as a role without MODIFYCLUSTERSETTING SET CLUSTER SETTING rejected, the collection still ran, the re-read FAILED the run on the setting
10 run as a role with statement_timeout at 250ms CREATE STATISTICS cancelled, the re-read found the statistic the audit saw and FAILED the run
11 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 audited database is the one named in jdbc_url.
  • The audit role needs SELECT on the tables in scope, which is also all CREATE STATISTICS asks for. Reading system.table_statistics needs more than that. A plain role is refused outright, so grant the VIEWSYSTEMTABLE system privilege or use a member of admin. Re-enabling the setting needs MODIFYCLUSTERSETTING. A role without it has that one statement rejected while the collection still runs.
  • measure_live_rows scans the primary index of each candidate table, so leave it off on a cluster where that scan is not affordable. Narrow it with include_schemas and max_tables_measured.

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 plus the database to audit
db_user root The role the queries connect as
password {{ secret('COCKROACH_PASSWORD') }} SQL password
include_schemas empty Comma separated schemas, empty means every user schema
max_statistics_age_hours 24 Age at which a statistic counts as stale
measure_live_rows false Count rows on tables the age check passed
max_tables_measured 20 Ceiling on tables counted per run
row_drift_factor 10.0 Factor between stored and live counts that counts as drift
min_drift_rows 1000 Size floor for a drift finding
max_tables_collected 10 Per run ceiling on CREATE STATISTICS
enforce_automatic_collection true Re-enable automatic collection when it is off
auto_remediate false Allow the collection and the setting change
dry_run true Must be false before anything is collected

Quick start

  1. Add the COCKROACH_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow and set jdbc_url to a node, with the database you want audited in the path.
  3. Run it once as it ships. Read the automatic collection line first, since a cluster with collection off needs that fixed before any per table finding means much.
  4. Run it with measure_live_rows: true on a quiet cluster to see which fresh looking statistics are actually wrong.
  5. Collect with auto_remediate: true and dry_run: false. Raise max_tables_collected only as far as the sampling load allows.
  6. Enable the every_6_hours trigger, which always runs in safe mode.

How to extend

  • Loop over several databases or clusters by passing a list of JDBC URLs, since one run audits the database in its own URL.
  • Collect per column with CREATE STATISTICS ... ON (col) FROM table for the columns a slow plan actually filters on, which samples far less than a whole table.
  • Record each run's drift factors in a KV key and alert on a table whose statistics go stale repeatedly, which usually means the automatic refresher's row floor is too high for it.
  • Compare EXPLAIN estimated row counts before and after the collection and attach the pair to the Slack message, which is the plan level proof that the refresh changed a decision.
  • Add a branch that raises sql.stats.automatic_collection.fraction_stale_rows for a specific table with ALTER TABLE ... SET (sql_stats_automatic_collection_fraction_stale_rows = ...) instead of collecting it by hand every pass.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.