PostgreSQL Said No to Query Hints for 20 Years. Postgres 19 Ships Them.

PostgreSQL 19 ships pg_plan_advice, a contrib module that describes, captures, and enforces planner decisions from outside the query text. It is the first in-core mechanism for plan stabilization in the project's history.

The problem it addresses is statistical, not syntactic. ANALYZE estimates column distributions from a sample rather than a full scan. When the sample misrepresents the distribution, the planner costs the query wrong and can flip to a plan orders of magnitude slower, with no change to the query, the schema, or the data volume.

Clerk documented an instance on February 19, 2026: a column that was 99.9996% NULL, an automatic ANALYZE whose sample contained only NULLs, and a resulting plan that assumed zero non-null rows where there were over 17,000. The query ran frequently enough to saturate the database. Recovery took roughly 90 minutes, and the remediation was re-running ANALYZE until the sample came back different.

Until now the supported answer to that class of failure was to fix the statistics. PostgreSQL 19 adds a second one.

Why Postgres said no for so long

The community position on query hints was never stubbornness, and it is worth stating fairly because it was mostly right.

Hints embedded in SQL comments, Oracle-style, rot. They get written during an incident, committed, and never revisited. They are invisible to code review because they look like comments. They break silently across major version upgrades when the planner improves and the hint keeps forcing the old, now-worse choice. And every hint someone writes is an optimizer bug report that never gets filed.

None of that is me editorializing. Optimizer hints sit under "Features We Do Not Want" on the project TODO, and the wiki page behind it lists exactly those objections: poor maintainability, interference with upgrades, encouraging DBAs to slap on a hint instead of finding the real issue, hints that are correct on a small table and wrong once it grows, and people who use hints rarely reporting the planner problem upstream.

So the tooling lived outside core. pg_hint_plan for hints, SET enable_hashjoin = off sprinkled through application code, various third-party plan-locking extensions. Each with its own semantics, its own release cadence, its own maintenance burden.

But read the stance carefully and it was never an absolute no. The wording is that the project is not interested in hints implemented the way other databases implement them, and that an idea avoiding the observed problems could lead to valuable discussion. That door has been open the whole time. PostgreSQL 19 is what walking through it looks like, and the design answers those objections one by one.

What actually ships

Three contrib modules, authored by Robert Haas:

• pg_plan_advice is the core mechanism. It describes plan choices in a small dedicated language, and applies them.
• pg_collect_advice is a worked example of extending advice collection.
• pg_stash_advice is a worked example of extending advice application, matching stored advice to queries by query identifier.

The critical design decision: advice never lives in your query text. It is a GUC. Your SQL stays clean, your ORM stays untouched, and the advice is a piece of database configuration you can audit, version, and remove.

First you load the module, via shared_preload_libraries, session_preload_libraries, or LOAD in a single session. That gives EXPLAIN a new PLAN_ADVICE option, which asks the planner to write down what it just decided:

EXPLAIN (COSTS OFF, PLAN_ADVICE)
  SELECT e.*, t.name
  FROM events e
  JOIN tenants t ON e.tenant_id = t.id
  WHERE e.status = 'pending';

                 QUERY PLAN
--------------------------------------------
 Hash Join
   Hash Cond: (e.tenant_id = t.id)
   ->  Seq Scan on events e
   ->  Hash
         ->  Seq Scan on tenants t
 Generated Plan Advice:
   JOIN_ORDER(e t)
   HASH_JOIN(t)
   SEQ_SCAN(e t)
   NO_GATHER(e t)

That block at the bottom is the plan, serialized. JOIN_ORDER(e t) means e drives and joins to t first. HASH_JOIN(t) means t sits on the inner side of a hash join. SEQ_SCAN(e t) means both get sequential scans. NO_GATHER(e t) means neither ends up under a Gather node.

Now you take that string, keep only the part you actually care about, and hand it back:

SET pg_plan_advice.advice = 'JOIN_ORDER(e t)';

Every query planned in this session that matches those targets is now constrained to that join order. Scan methods and parallelism stay free, because you did not pin them.

This generate-then-edit workflow is the whole ergonomic story. You are not writing hints from scratch against a mini-language you half remember. You are capturing a plan that worked and deleting the parts you do not need to freeze.

The feedback loop is the good part

The failure mode of every hint system ever built is the silently ignored hint. You write it, you deploy it, you assume it worked, and six months later you discover it never matched anything.

EXPLAIN annotates every piece of supplied advice with what happened to it:

SET pg_plan_advice.advice =
  'HASH_JOIN(t) INDEX_SCAN(e no_such_index) SEQ_SCAN(nonexistent)';

EXPLAIN (COSTS OFF) SELECT ...;

 Supplied Plan Advice:
   HASH_JOIN(t) /* matched */
   INDEX_SCAN(e no_such_index) /* matched, inapplicable, failed */
   SEQ_SCAN(nonexistent) /* not matched */

The vocabulary is worth learning, because it is how you debug this:

Feedback	Meaning
matched	Targets were all seen together, advice was enforceable
partially matched	Some targets seen, or all seen but not together
not matched	None of the targets appear in this query at all
inapplicable	Tag cannot apply here, e.g. the index does not exist
conflicting	Two pieces of advice want incompatible things
failed	Final plan does not comply

Set pg_plan_advice.feedback_warnings = true and you get a warning whenever advice fails to be enforced, instead of silence. It defaults to off. Turn it on.

Making it survive a disconnect

A session GUC dies with the session, which is useless for a connection-pooled application. That is what pg_stash_advice is for.

It maps query identifiers to advice strings in dynamic shared memory, and applies them automatically at planning time. EXPLAIN (VERBOSE) gives you the query ID, EXPLAIN (PLAN_ADVICE) gives you the string, and you stash the pair. compute_query_id has to be enabled, though loading the module will turn it on for you if it is set to auto.

One write, effective for every session that plans a query with that ID. That is plan stability without shipping a single line of application code, which during an incident at 3 AM is a meaningfully different proposition from a deploy.

The parts the announcement posts skip

1. Advice constrains the planner. It does not command it.

Internally, advice works by telling the planner what not to consider. You will only ever get a plan the core planner already considered viable. If you try to force something it rejected on correctness grounds, or discarded before costing, no advice string will save you. This is stated plainly in the docs and it is the single most misunderstood thing about the feature.

2. Wrong advice gives you a worse plan, not an error.

Planning is not allowed to fail. If your advice is impossible, the planner disables paths trying to comply, then picks something from what is left. The documented example is instructive: advise a join order involving a table that is not in the query, and you can end up with a Nested Loop flagged Disabled: true where you previously had a perfectly good Hash Join. You asked for stability and got a regression. Always verify with EXPLAIN after applying advice.

3. It costs something even when it changes nothing.

Applying advice has measurable planning overhead whether or not the plan differs. This is a scalpel for a handful of known-fragile queries, not a blanket you throw over a workload.

4. There are real gaps.

No control over aggregation strategy, so you cannot pin hash versus sort aggregation, eager aggregation, or partitionwise aggregation. No control over set operations like UNION and INTERSECT. If your problem query is an aggregation regression, this release does not help you.

5. Freezing a plan freezes it against the future too.

The planner adapting to changing data distribution is a feature. Pin a plan today and you have opted out of that adaptation permanently, for that query, until someone remembers to remove the advice. Which nobody will. Put an expiry date in the ticket.

Would this have prevented the Clerk outage?

Not on its own. Pinning that plan in advance requires knowing beforehand that the query sits on an unstable statistic, and that is not knowable until it fails.

Clerk's remediations were the conventional ones: raise the statistics target on the affected table so ANALYZE samples more rows, then refactor the query so the planner has less room to be creative. They are also auditing their remaining queries for the same fragility.

What pg_plan_advice changes is the shape of the recovery. The plan flip was identified at 17:25 UTC and remediated at 17:27 by re-running ANALYZE and getting a better sample. That is a coin flip as an incident response. A stashed advice string is a deterministic lever instead: pin the known-good plan, stop the bleeding, fix the statistics when nobody is paged.

Recovery-time control is the gap being filled here. Treating the feature as a prevention mechanism sets people up to misuse it.

When to reach for it

Use it when you have a small, identified set of queries with a documented planner regression on production data, and you need determinism while you fix the underlying cause. Use it to reproduce a plan from staging in production for debugging. Use it to test whether the plan you think is better actually is.

Do not use it as a substitute for ANALYZE tuning, a missing index, or a query that needs rewriting. Do not apply it broadly. Do not leave it in place without a review date.

The thing nobody is talking about

The headline is hints. The actually interesting part is the hook.

pg_plan_advice exposes an interface for other modules to inject advice at planning time, and pg_collect_advice and pg_stash_advice exist mostly to demonstrate it. That is the substrate for a real SQL Plan Management system: automatic baseline capture, plan evolution, regression detection. The things Oracle SPM and SQL Server's Query Store users have had for years.

PostgreSQL 19 does not ship SPM. It ships the foundation that lets someone build one without forking the planner. That distinction is the whole point, and it is a much bigger deal than the hint syntax.

Beta 2 landed on July 16. GA is targeted for September or October 2026. If you have a query that has ever flipped on you, this is the release to test against.