If icon
Queries icon
Query icon
OutputValues icon
Log icon
Switch icon
Set icon
Fail icon
SlackIncomingWebhook icon
Webhook icon
Schedule icon

Catch Postgres query plan regressions after a migration

Detect Postgres query plan regressions in Kestra. EXPLAIN critical queries after a migration and block new sequential scans or cost blowups.

Categories
DataInfrastructure

A migration that drops or renames an index, a type change that makes an index unusable, or fresh statistics that flip the planner: the query still returns the right rows, so every test passes, and it now reads the whole table. Users notice first. This flow watches the plans of the queries that matter:

  • It runs EXPLAIN (FORMAT JSON) on each critical query. Without ANALYZE, the statement is never executed, so this is safe on production.
  • It summarizes each plan: root node, estimated total cost, tables read with a sequential scan, indexes used.
  • It compares the summaries with a baseline in the KV store. A regression is a cost growth of max_cost_ratio or more, or a sequential scan the baseline did not have.

How it works

  1. demo_schema (io.kestra.plugin.core.flow.If) creates 300,000 orders, three indexes and a critical_queries table with three statements when setup_demo is on.
  2. regression drops orders_customer_idx when simulate_regression is on.
  3. plan_helper (io.kestra.plugin.jdbc.postgresql.Query) creates kestra_plan_of(text), a function that returns a statement's JSON plan. EXPLAIN cannot be used inside a SELECT, so the function wraps it.
  4. explain (io.kestra.plugin.jdbc.postgresql.Query) summarizes every plan with jsonb_path_query.
  5. compare (io.kestra.plugin.core.output.OutputValues) diffs the plans with the baseline in jq. log_plans prints both.
  6. decide (io.kestra.plugin.core.flow.Switch):
    • SAVE_BASELINE on the first run or with accept_new_baseline.
    • OK logs.
    • REGRESSED fails with the before and after plan of each regressed query.
  7. after_migration (Webhook) and nightly (Schedule) triggers. The nightly run catches plans that flip because statistics changed.
  8. errors posts to Slack when the run fails and notify_slack is true.

Quick start

Run Input Result
1 defaults Baseline stored: customer_order_history Limit cost 61.4 on orders_customer_idx, last_7_days_revenue cost 2,453.1 on orders_created_idx, order_by_id cost 8.4 on orders_pkey_idx
2 defaults All plans match the baseline
3 simulate_regression: true Blocked: customer_order_history cost 61.4 to 5,447.1 (88 times), new sequential scan on orders, index orders_customer_idx gone
4 accept_new_baseline: true Baseline refreshed after a reviewed change

Inputs

  • setup_demo (BOOL, default true): set it to false on a real database.
  • simulate_regression (BOOL, default false), max_cost_ratio (FLOAT, default 3.0), accept_new_baseline (BOOL, default false).

Prerequisites

  • A Postgres database and a user that can read the queried tables, read critical_queries and create the kestra_plan_of function.

Secrets

  • POSTGRES_URL (for example jdbc:postgresql://db:5432/shop), POSTGRES_USERNAME, POSTGRES_PASSWORD.
  • SLACK_WEBHOOK_URL: used by the errors block when notify_slack is true.

Things to know

  • The JDBC driver treats ? as a bind parameter, so the jsonb ? key operator cannot be used. The flow checks keys with ->> ... IS NOT NULL instead.
  • Run it on a database whose statistics resemble production. Plans on a near-empty staging database tell you little.
  • Statements with parameters must be stored with representative literal values.

Expected outputs

  • outputs.explain.rows: name, root, cost, seq_scans, indexes per query.
  • outputs.compare.values.regressions: one line per regressed query.
  • KV pg_plan_baseline_shop.

Links

This blueprint was created by zkasuran.

See How

New to Kestra?

Use blueprints to kickstart your first workflows.