Query plans, watched
pg_plan_guard v1.1.11
Is the plan for this query still the plan you approved?
PostgreSQL 19 added pg_plan_advice (generate advice for a plan, then force it) and pg_stash_advice (store advice per query_id). Both are deliberate, manual acts: you decide a plan is good, and you pin it. Neither watches. pg_plan_guard answers the missing question.
Install
CREATE EXTENSION pg_plan_guard; In plain words
It watches that the important queries in your system stay fast, and warns you the day they stop being fast.
- The situation
Your store's product search answers in milliseconds because it uses an index.
- Without this piece
An internal change makes the same search take fifty times longer. Nothing fails, nothing warns: it just gets slow. You find out weeks later, from customers.
- With this piece
Your team approved how that search should be resolved. A periodic check warns the day it changes, before a customer notices.
Why it matters if you decide
- Performance watched, not assumed: a warning when it changes, not a complaint when it hurts
- A history of what changed and when, to understand it afterwards
- Requires PostgreSQL 19 today: it is in the public image, not yet in the Enterprise edition
The problem
A plan regression does not fail: it gets slow in silence
The query still returns the same rows; it just stopped using the index and started scanning. Nothing errors, nothing logs, nothing alerts.
The case it was built for: a vector similarity search backed by a DiskANN index over ~40,000 embeddings. If the planner stops choosing it, the results are identical and still correctly ordered. It is simply a sequential scan now. Correct, silent, and slow; you find out weeks later from a latency graph, if at all.
How it works
Capture, verify, point the monitor at one view
- 01
capture(name, query_sql, description)
Approve today's plan as the baseline. Explicit, never automatic: a baseline that captured itself would bless whatever plan happened to be in effect, including the regression you are hunting.
- 02
verify([name])
Re-plan every baseline and report drift. Advice is compared (the shape: which scan on which relation, which join order), not EXPLAIN text, which changes with estimates and costs even when the shape is identical.
- 03
status and drift_log
One view for the monitor, worst first; and an append-only history of every drift, logged once per transition, not once per check. A broken baseline (table dropped) is reported as error and the rest are still checked.
- 04
sync_stash(stash)
Push approved advice into a pg_stash_advice stash. The table is the source of truth: the stash is a cache that can always be rebuilt.
SQL
Usage
capture() records the capturing session's search_path; verify() and sync_stash() re-plan under it with pg_temp moved to the end, so a temporary table in the verifying session cannot answer for a watched one (test/pg_temp.sh).
CREATE EXTENSION pg_plan_guard;
-- Approve today's plan for a critical query.
SELECT plan_guard.capture(
'semantic_search',
'SELECT id FROM docs ORDER BY embedding <=> ''[...]'' LIMIT 10',
'must use the diskann index');
-- Later, on a schedule: has anything drifted?
SELECT cron.schedule('plan-guard', '37 */6 * * *',
$$SELECT * FROM plan_guard.verify()$$);
-- And point your monitoring at one view.
SELECT * FROM plan_guard.status WHERE state <> 'ok'; What it guarantees
Design decisions
- Advice is compared, not EXPLAIN: it changes only when the planner's decision changes.
- Capture is explicit. Drift is logged once per transition. A broken baseline does not abort the run.
- A baseline is re-planned against the tables its author meant (recorded search_path, pg_temp last).
- The table is the source of truth, not shared memory: if stash persistence ever fails, the pinning disappears silently, the same failure mode this extension exists to catch.
- Planning is not nothing: the planner folds an IMMUTABLE function called with constant arguments, so a stored query can run code. Since 1.1.5 every EXPLAIN of stored text runs sealed: in a read-only subtransaction that is always rolled back.
- Since 1.1.7 a baseline is planned as the role that wrote it, and since 1.1.9 the EXPLAIN runs inside a temporary SECURITY DEFINER function owned by its author: a stored query can do no more than its author could do directly.
What it does not do
Limitations
- Queries are stored as text and re-planned as written: parameterized queries must be captured in an executable form (literal values).
- Drift detection is only as good as the baseline: capturing a bad plan pins a bad plan. Review what capture() returns.
- verify() plans with the settings of the session that runs it, not those of the application's role.
- A query_id ignores constants: two baselines of one statement with different literals share a stash slot, and the last sync_stash() wins.
- Requires PostgreSQL 19 (pg_plan_advice). Verified 2026-09-16: 10 through 18 fail at the first capture with "requires pg_plan_advice". That is why it is NOT in the Enterprise edition today (PostgreSQL 18); it is in the public image.
For the enterprise
For the enterprise
A performance sentinel for the queries that pay the bills: one approved baseline per query and a monitor that warns the day the planner changes its mind, not the day a user complains.
- One view (plan_guard.status) to plug into existing monitoring
- A drift history for post-mortems
- Available once your database is on PostgreSQL 19
This piece is not in the Enterprise 0.3.1 image, which ships pg_agent_gate, pg_living_assertions, pg_promise_guard and pg_grammar_guard. Install it separately from PGXN.
Spec sheet
- Version measured
- 1.1.11 (public image 0.3.6, 2026-11-13)
- PostgreSQL
- 19 or later, with pg_plan_advice; sync_stash() also needs pg_stash_advice preloaded and a stash_name
- License
- Apache-2.0
- Enterprise edition
- Not yet (the edition runs on PostgreSQL 18)
The other pieces of the ecosystem
- pg_agent_gate AI agents on PostgreSQL
- pg_phasevec · preview 0.x Vector memory with provenance
- pg_living_assertions Guarantees with a date
- pg_recall_guard Vector index recall
- pg_promise_guard Schema guarantees
- pg_grammar_guard Structured output that cannot invent names
- ygg-agent v0.2.0 · PyPI The agent loop on pg_agent_gate, with no MCP: the model writes SQL and the database decides.