API Contract Smoke Test with Slack Alerts
Validate API status codes and response markers on a schedule, save evidence, and alert only on failures.
Audit stale, missing or wrong CockroachDB table statistics, alert Slack, collect fresh statistics per table within a cap and verify they landed.
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.
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.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.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.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.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.concurrencyLimit: 1, so two samplings never contend.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.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.
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.
live_counts runs one UNION ALL of count(*) over those candidates, so the cluster answers one query rather than one per table.
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 |
summary logs every flagged table with its newest statistic, its stored row count, the live count and whether the cap covers it this pass.
route (io.kestra.plugin.core.flow.If):
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.errors alerts Slack when the audit itself cannot run.
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.
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 |
jdbc_url.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.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.SECRET_.| 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 |
COCKROACH_PASSWORD and SLACK_WEBHOOK_URL secrets.jdbc_url to a node, with the database you want audited in the path.measure_live_rows: true on a quiet cluster to see which fresh looking statistics are actually wrong.auto_remediate: true and dry_run: false. Raise max_tables_collected only as far as the sampling load allows.every_6_hours trigger, which always runs in safe mode.CREATE STATISTICS ... ON (col) FROM table for the columns a slow plan actually filters on, which samples far less than a whole table.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.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.