Skip to content
← Ecosystem

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.

  1. The situation

    Your store's product search answers in milliseconds because it uses an index.

  2. 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.

  3. 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
For the technical team

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

  1. 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.

  2. 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.

  3. 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.

  4. 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).

psql · pg_plan_guardsql
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)
Try it Talk