Contents

• pg_plan_guard
  • Why it matters
  • Usage
  • API
  • Design decisions
  • Requirements
  • Tested on
  • Install
  • Limitations
  • License

pg_plan_guard

Detect when a query plan drifts away from 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 and apply it automatically). Both are deliberate, manual acts: you decide a plan is good, and you pin it.

Neither of them watches. There is no way to ask:

Is the plan for this query still the plan I approved?

pg_plan_guard answers that question.

Why it matters

A plan regression usually does not fail. The query still returns the same rows — it just stops using the index and starts scanning. Nothing errors, nothing logs, nothing alerts.

The case this was built for: a vector similarity search backed by a DiskANN index over ~40,000 embeddings. If the planner stops choosing that index, the results are identical and still correctly ordered. It is simply a sequential scan now. Correct, silent, and slow.

That class of failure is the expensive one precisely because it does not announce itself. You find out weeks later from a latency graph, if at all.

Usage

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 * FROM plan_guard.verify();

--       name       |  state  |        expected_advice        |    actual_advice
-- -----------------+---------+-------------------------------+---------------------
--  semantic_search | drifted | INDEX_SCAN(docs docs_emb_idx) | SEQ_SCAN(docs) ...

Run it from pg_cron, or from whatever already runs your checks:

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';

API

Function	Purpose
plan_guard.capture(name, query_sql [, description])	Approve the current plan as the baseline
plan_guard.verify([name])	Re-plan every baseline and report drift
plan_guard.sync_stash(stash_name)	Push approved advice into a pg_stash_advice stash
plan_guard.advice_for(query_sql)	Plan advice for an arbitrary query
plan_guard.query_id_for(query_sql)	query_id of a query, for stash operations

Relation	Contents
plan_guard.baselines	The approved plan for each query
plan_guard.drift_log	Append-only history of every detected drift
plan_guard.status	Current state, worst first — the view a monitor polls

Design decisions

These are the choices that make it usable rather than annoying, and the reasons behind them:

Advice is compared, not EXPLAIN output. EXPLAIN text changes with row estimates and costs even when the plan shape is identical. Comparing it would make baselines drift constantly and train everyone to ignore the alerts. Advice describes the shape — which scan on which relation, which join order, which method — so it changes only when the planner’s decision changes.

Capture is explicit, never automatic. A baseline that captured itself would happily bless whatever plan happened to be in effect, including the regression you are hunting.

Drift is logged once per transition, not once per check. A baseline that has been drifting for a week should not produce a row per cron run.

A broken baseline does not abort the run. If a query no longer plans (table dropped, column renamed) it is reported as error and the remaining baselines are still checked. A monitor that dies on the first problem stops working exactly when something is wrong.

A baseline is re-planned against the tables its author meant. The query is stored as text, so the names in it are resolved by a search_path. capture() records the capturing session’s, and verify() and sync_stash() re-plan under it with pg_temp moved to the end. So a baseline captured with SET search_path = app plans the same from pg_cron, and a temporary table in the verifying session cannot answer for a watched one: unnamed, PostgreSQL searches pg_temp first, and until 1.1.3 a temporary copy wrote false drifts into drift_log and hid real ones (test/pg_temp.sh). Baselines captured before 1.1.4 have no recorded path and are planned under the caller’s, also with pg_temp last; capture them again to pin it. The recorded path is applied only inside the sealed EXPLAIN described below, so it never reaches the session that called verify() – until 1.1.5 it did, and that session’s next capture() recorded the baseline’s path instead of its own (test/audit.sh).

The table is the source of truth, not shared memory. pg_stash_advice persists across restarts, but if persistence ever fails or the cluster is recreated, the pinning disappears silently — queries keep working, just slowly. That is the same failure mode this extension exists to catch, so the stash is treated as a cache that can always be rebuilt from plan_guard.baselines via sync_stash().

Requirements

• PostgreSQL 19 or later, with pg_plan_advice available.
• sync_stash() additionally requires pg_stash_advice, which needs two things to actually apply advice — and if either is missing, nothing is applied and nothing warns you:
  1. shared_preload_libraries includes pg_plan_advice and pg_stash_advice
  2. pg_stash_advice.stash_name is set (the default is empty)

  Diagnose with: sql SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_stash%';

Baselines are captured with EXPLAIN (PLAN_ADVICE), which does not execute the query – but planning is not nothing: the planner folds an IMMUTABLE function called with constant arguments, so a stored query can run code as whoever plans it. Since 1.1.5 every EXPLAIN of stored text runs sealed, the way pg_living_assertions runs a check: in a subtransaction switched to read-only and always rolled back, under a path pinned to pg_catalog, pg_temp outside it. What planning does is refused if it writes and undone if it does not.

A seal is not enough on its own: COPY ... TO PROGRAM, pg_switch_wal() or a session advisory lock are not writes to the database, and until 1.1.7 a folded function ran them as whoever ran verify() – usually a superuser (external audit, round 4). Since 1.1.7 a baseline is planned as the role that wrote it. baselines.captured_by records that role: a trigger sets it to whoever writes or rewrites the query, and accepts another name only from a role that may SET ROLE to it. Since 1.1.9 the EXPLAIN runs inside a temporary SECURITY DEFINER function owned by the author, created and rolled back inside the seal. Inside such a function PostgreSQL refuses to change role or session_authorization at all, so a stored query can do no more than its author could do directly and cannot become anyone else; anything beyond is that baseline’s error, not an aborted verify(). Session advisory locks taken inside the seal are released. The role that runs verify() must be able to hand that function to each author – a superuser can; otherwise it needs to be able to SET ROLE to the author, and the author needs TEMP on the database. A baseline captured before 1.1.7 has no recorded author and is refused until it is captured again.

In 1.1.7 and 1.1.8 the EXPLAIN ran after SET ROLE to the author instead, and an external audit (round 5) measured why that is not a boundary: a folded function ran RESET ROLE, SET SESSION AUTHORIZATION DEFAULT or set_config('role', ...) and was the runner again, then ran a program. make check-audit (S1) shows each way back refused, against a control that shows it working under SET ROLE.

capture(), verify() and advice_for() work for a role that is not a superuser once pg_plan_advice is in shared_preload_libraries: LOAD needs superuser, and since 1.1.5 a refused LOAD of an already loaded library is not an error. sync_stash() sets compute_query_id, which only a superuser can.

Tested on

PostgreSQL 19 only, and that is not conservatism: the extension reads plan advice through pg_plan_advice, which arrived in 19. Verified on 2026-09-16 by running make installcheck against 10 through 18 as well, each in a container of the official image: every one of them fails at the first capture with

ERROR:  pg_plan_guard requires pg_plan_advice (PostgreSQL 19+)

Install

From PGXN:

pgxn install pg_plan_guard
psql -c 'CREATE EXTENSION pg_plan_guard'

From source:

make install PG_CONFIG=/path/to/pg_config
psql -c 'CREATE EXTENSION pg_plan_guard'

Run the tests against a live server:

make installcheck PG_CONFIG=/path/to/pg_config

And the search_path suite, in a throwaway cluster built from the same binaries:

PG_CONFIG=/path/to/pg_config test/cluster.sh init
PG_CONFIG=/path/to/pg_config test/cluster.sh start
make check-pgtemp PG_CONFIG=/path/to/pg_config

Limitations

• Queries are stored as text and re-planned as written. Parameterized queries must be captured in an executable form (literal values), since EXPLAIN needs a complete statement.
• Drift detection is only as good as the baseline: capturing a bad plan pins a bad plan. Review what capture() returns.
• capture() and verify() run EXPLAIN on stored SQL, so execute rights are not granted to PUBLIC.
• verify() plans with the settings of the session that runs it, not those of the application’s role: a planner setting on that role (enable_indexscan, say) is not seen.
• A query_id ignores constants, so two baselines of one statement with different literals share a stash slot, and the last sync_stash() writes wins.
• A temporary table still answers for a name found nowhere on the recorded path: pg_temp goes last, not away.

License

Apache License 2.0 – see LICENSE. Copyright 2026 Manuel Reyes Bravo.

The name is not licensed with the code: see TRADEMARK.md.