id: postgres-query-plan-regression-guard
namespace: company.team
inputs:
- id: setup_demo
type: BOOL
displayName: Create the demo schema
description: Create a shop schema with 300,000 orders and the indexes the
critical queries rely on. Leave false on a real database.
defaults: true
- id: simulate_regression
type: BOOL
displayName: Simulate a regression
description: Drop the index on orders.customer_id, the way a migration or a
reckless cleanup does, before the plans are checked.
defaults: false
- id: max_cost_ratio
type: FLOAT
displayName: Max cost ratio
description: Flag a query whose estimated total cost grew by this factor or more
against the baseline.
defaults: 3.0
- id: accept_new_baseline
type: BOOL
displayName: Accept current plans as baseline
description: Store the current plans as the baseline even if they regressed,
after a reviewed change.
defaults: false
- id: notify_slack
type: BOOL
displayName: Notify Slack
description: Post a failure alert to Slack. Needs the SLACK_WEBHOOK_URL secret.
defaults: false
variables:
baseline_key: pg_plan_baseline_shop
tasks:
- id: demo_schema
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.setup_demo }}"
then:
- id: create_schema
type: io.kestra.plugin.jdbc.postgresql.Queries
description: Demo data and the critical queries to watch. In real use the
critical_queries table holds your own statements.
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
sql: |
CREATE TABLE IF NOT EXISTS orders AS
SELECT g AS id, (g % 20000) + 1 AS customer_id, now() - (g % 365) * interval '1 day' AS created_at,
(array['NEW','PAID','SHIPPED','REFUNDED'])[1 + g % 4] AS status, round((random() * 500)::numeric, 2) AS amount
FROM generate_series(1, 300000) g;
CREATE UNIQUE INDEX IF NOT EXISTS orders_pkey_idx ON orders (id);
CREATE INDEX IF NOT EXISTS orders_customer_idx ON orders (customer_id);
CREATE INDEX IF NOT EXISTS orders_created_idx ON orders (created_at);
ANALYZE orders;
CREATE TABLE IF NOT EXISTS critical_queries (name text PRIMARY KEY, sql text NOT NULL);
INSERT INTO critical_queries VALUES
('customer_order_history', 'SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20'),
('order_by_id', 'SELECT * FROM orders WHERE id = 123456'),
('last_7_days_revenue', 'SELECT sum(amount) FROM orders WHERE created_at > now() - interval ''7 days''')
ON CONFLICT (name) DO UPDATE SET sql = EXCLUDED.sql;
- id: regression
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.simulate_regression }}"
then:
- id: drop_index
type: io.kestra.plugin.jdbc.postgresql.Query
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
sql: DROP INDEX IF EXISTS orders_customer_idx
- id: plan_helper
type: io.kestra.plugin.jdbc.postgresql.Query
description: A small helper that returns the JSON plan of a statement. EXPLAIN
cannot be used inside a SELECT, so a function wraps it.
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
sql: |
CREATE OR REPLACE FUNCTION kestra_plan_of(q text) RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE p jsonb;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || q INTO p;
RETURN p -> 0 -> 'Plan';
END $$
- id: explain
type: io.kestra.plugin.jdbc.postgresql.Query
description: >
EXPLAIN every critical query without running it, then summarize each plan:
root node, estimated total cost, every table read with a sequential scan
and every index used. EXPLAIN without ANALYZE never executes the
statement, so this is safe on production.
url: "{{ secret('POSTGRES_URL') }}"
username: "{{ secret('POSTGRES_USERNAME') }}"
password: "{{ secret('POSTGRES_PASSWORD') }}"
fetchType: FETCH
sql: |
WITH p AS (SELECT name, kestra_plan_of(sql) AS plan FROM critical_queries)
SELECT name,
plan ->> 'Node Type' AS root,
round((plan ->> 'Total Cost')::numeric, 1) AS cost,
coalesce((SELECT string_agg(DISTINCT n ->> 'Relation Name', ',')
FROM jsonb_path_query(plan, 'strict $.**') n
WHERE n ->> 'Node Type' = 'Seq Scan'), '') AS seq_scans,
coalesce((SELECT string_agg(DISTINCT n ->> 'Index Name', ',')
FROM jsonb_path_query(plan, 'strict $.**') n
WHERE n ->> 'Index Name' IS NOT NULL), '') AS indexes
FROM p ORDER BY name
- id: compare
type: io.kestra.plugin.core.output.OutputValues
description: Compare each plan with the baseline. A regression is a cost growth
past max_cost_ratio or a sequential scan the baseline did not have.
values:
current: "{{ outputs.explain.rows | toJson }}"
baseline: "{{ kv(vars.baseline_key, errorOnMissing=false) ?? 'null' }}"
regressions: >-
{{ outputs.explain.rows | jq('
. as $cur | (' ~ ((kv(vars.baseline_key, errorOnMissing=false) ?? null) | toJson) ~ ') as $base
| if $base == null then [] else
[ $cur[] | . as $q | ($base | map(select(.name == $q.name)) | first) as $b
| select($b != null)
| select(($q.cost / ([$b.cost, 0.01] | max)) >= ' ~ inputs.max_cost_ratio ~ '
or (($q.seq_scans | length) > 0 and ($b.seq_scans | length) == 0))
| "\($q.name): \($b.root) cost \($b.cost) to \($q.root) cost \($q.cost)"
+ (if ($q.seq_scans | length) > 0 and ($b.seq_scans | length) == 0 then ", new sequential scan on \($q.seq_scans)" else "" end)
+ (if $b.indexes != $q.indexes then ", indexes [\($b.indexes)] to [\($q.indexes)]" else "" end) ]
end') | first | toJson }}
- id: log_plans
type: io.kestra.plugin.core.log.Log
message: "Plans now: {{ outputs.compare.values.current }}
Regressions: {{ outputs.compare.values.regressions }}"
- id: decide
type: io.kestra.plugin.core.flow.Switch
value: >-
{{ outputs.compare.values.baseline == 'null' or inputs.accept_new_baseline
? 'SAVE_BASELINE'
: (outputs.compare.values.regressions | jq('length') | first > 0 ? 'REGRESSED' : 'OK') }}
cases:
SAVE_BASELINE:
- id: save_baseline
type: io.kestra.plugin.core.kv.Set
key: "{{ vars.baseline_key }}"
kvType: JSON
value: "{{ outputs.compare.values.current }}"
OK:
- id: plans_stable
type: io.kestra.plugin.core.log.Log
message: All critical queries keep their baseline plans.
REGRESSED:
- id: block
type: io.kestra.plugin.core.execution.Fail
description: Fails the run, so a migration pipeline that calls this flow stops
before the slow plan reaches users.
errorMessage: "Query plan regression: {{ outputs.compare.values.regressions |
jq('join(\"; \")') | first }}"
errors:
- id: alert_flow_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
runIf: '{{ inputs.notify_slack }}'
description: A failed run is either a plan regression or a check that could not
run. Neither may pass as a healthy database.
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'Postgres query plan regression guard failed (execution ' ~ execution.id ~ '). Check the execution logs: a critical query plan regressed or the plans could not be read.'} | toJson }}
triggers:
- id: after_migration
type: io.kestra.plugin.core.trigger.Webhook
disabled: true
description: Call right after a schema migration. Replace the key first.
key: replace-with-a-long-random-key
- id: nightly
type: io.kestra.plugin.core.trigger.Schedule
disabled: true
description: Catches plan flips from changing statistics as well.
cron: "0 5 * * *"