Postgres 19 lets you control the query planner. It spent 20 years refusing.

PostgreSQL shipped 19 Beta 1 on June 4, and the release notes lead with the things you would expect: parallel autovacuum, an in-core REPACK command, smarter async I/O. Scroll down a little and you hit the entry that actually breaks with two decades of project culture. PostgreSQL 19 adds pg_plan_advice, a sanctioned way to look at how the planner built a query plan, freeze that shape, and replay it. The project that has told users "the planner knows best" since the early 2000s just shipped a mechanism to tell the planner it is wrong.

Twenty years of "the planner knows best"

Every mature database has an opinion about query hints, and Postgres has held the most stubborn one in the industry. Oracle has hints. SQL Server has plan guides and OPTION clauses. MySQL has FORCE INDEX. Postgres, for most of its modern life, has shipped nothing of the kind on purpose.

The community position is written down. The project's OptimizerHintsDiscussion wiki page states it plainly: "We are not interested in implementing hints in the exact ways they are commonly implemented on other databases." The reasoning is not stubbornness for its own sake. Inline hints rot. A hint that pins a nested loop because a table had 200 rows becomes a production incident when that table has 2 million rows and the planner is no longer allowed to switch to a hash join.

The page lists the objections in order: hints embedded in queries force code changes when the data shifts, they survive upgrades that should have improved the plan, they let a DBA paper over a root cause instead of fixing it, and they quietly stop people from filing the planner bugs that would make the optimizer better for everyone. The official answer has always been the indirect controls: fix your statistics, raise default_statistics_target, add an index, rewrite the query, or in an emergency toggle a session GUC like enable_nestloop. Those GUCs are blunt. They flip a join method for the entire statement, not for the one join that went wrong.

So an ecosystem grew around the gap. The pg_hint_plan extension, maintained outside core, lets you write Oracle-style hints in a SQL comment and have them respected. It is widely used, especially by teams migrating off Oracle. It is also a maintenance burden, because it works by copying large amounts of the planner's own source into the extension and toggling internal state from the outside. Every major Postgres release risks breaking it. That tension is the backdrop for what landed in 19: not a capitulation to inline hints, but a different shape of answer.

What pg_plan_advice actually does

The extension comes from Robert Haas, with pg_stash_advice co-authored by Haas and Lukas Fittl. The official documentation describes the module in one careful sentence:

The pg_plan_advice module allows key planner decisions to be described, reproduced, and altered using a special-purpose "plan advice" mini-language. It is intended to allow stabilization of plan choices that the user believes to be good, as well as experimentation with plans that the planner believes to be non-optimal.

Read that twice. The primary verb is not "force." It is "stabilize." The feature exists first to capture a plan you already like and keep getting it, and only second to push the planner toward a plan it would not have picked.

The workflow starts with a new EXPLAIN option that emits advice instead of consuming it:

EXPLAIN (COSTS OFF, PLAN_ADVICE)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

-- advice emitted for this plan:
--   JOIN_ORDER(f d)
--   HASH_JOIN(d)
--   SEQ_SCAN(f d)
--   NO_GATHER(f d)

Those four lines are the plan's shape in the advice mini-language. JOIN_ORDER(f d) records the order tables were joined. HASH_JOIN(d) records the join method. SEQ_SCAN(f d) records the access method per table, and NO_GATHER(f d) records that the plan ran without parallel workers. The advice string is a description of a plan, not a query rewrite.

To apply it, you set it as a parameter and run the query again:

SET pg_plan_advice.advice = 'JOIN_ORDER(f d) HASH_JOIN(d) SEQ_SCAN(f d)';

EXPLAIN (COSTS OFF)
        SELECT * FROM join_fact f JOIN join_dim d ON f.dim_id = d.id;

The advice vocabulary covers the decisions that actually move query performance: scan methods (SEQ_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, BITMAP_HEAP_SCAN), join order, join methods (HASH_JOIN, MERGE_JOIN_PLAIN, the nested-loop variants), partitionwise join, and parallelism. It deliberately stops short of two areas. The docs note it cannot yet control aggregation or set operations like UNION. This is version one, and it knows it.

Constraining, not commanding

The detail that keeps this from being the hint system Postgres spent twenty years rejecting is in how it works under the hood. The docs are explicit about the sleight of hand:

Plan advice is written imperatively; that is, it specifies what should be done. However, at an implementation level, pg_plan_advice works by telling the core planner what should not be done. In other words, it operates by constraining the planner's choices, not by replacing it.

That distinction matters more than it looks. A traditional hint says "use this index." Plan advice instead removes the alternatives the planner would have considered, then lets the planner do its normal cost-based job on what remains. The optimizer is still driving. You have just narrowed the road.

The hard ceiling follows from that design, and the docs state it directly:

The use of plan advice can only affect which plan the planner chooses from among those it believes to be viable. It can never force the choice of a plan which the planner refused to consider in the first place.

If the planner never generated an index-scan path because the index does not exist or cannot answer the query, no amount of advice conjures one. This is the answer to objection number one on that wiki page. Advice cannot pin a plan that has become invalid, because an invalid plan is not a viable plan, so it never enters the set advice is allowed to choose from.

Postgres also refuses to let you forget the risk. The module ships with a warning in its own documentation:

Note that, since the planner often makes good decisions, overriding its judgment can easily backfire. For example, if the distribution of the underlying data changes, the planner normally has the option to adjust the plan in an attempt to preserve good performance. If the plan advice prevents this, a very poor plan may be chosen.

And the feedback loop is built in. EXPLAIN annotates every piece of advice you supply with whether it was matched, partially matched, not matched, inapplicable, or failed. You can see at a glance that the advice you wrote last quarter no longer applies to this quarter's schema. That is the missing piece in every comment-based hint system: the database tells you when your hint stopped meaning anything.

Why this is not just pg_hint_plan in core

The obvious question for anyone who already runs pg_hint_plan is whether this replaces it. The honest answer is that they solve overlapping problems from opposite directions, and the more interesting story is what the new core machinery does for the old extension.

Plan advice ships alongside a quieter change: a clean hook for path generation strategies inside the planner. Lukas Fittl has shown that this hook lets an extension tell the planner, per table, not to generate a sequential-scan path at all, using a bitmask on the planner's internal relation state. With that hook in core, pg_hint_plan can drop roughly 2,500 lines of copied planner source, the exact code that makes it fragile across upgrades. The feature that looks like a competitor to the extension is also the thing that makes the extension safer to maintain.

The two approaches differ on the axis that matters for production:

For plan stability, the new pg_stash_advice companion is the piece teams will actually deploy. It stores advice keyed by query identifier and applies it automatically, so you are not editing application SQL to pin a plan. You capture the good plan once, stash it against the query id, and the database keeps producing that plan even as autovacuum churns statistics underneath it. That is the Oracle SQL Plan Management workflow, rebuilt on Postgres terms.

The rest of 19 is about operator control too

Plan advice is the philosophical headline, but it fits a pattern that runs through the whole release. PostgreSQL 19 is unusually focused on handing operators levers that used to require an extension, a maintenance window, or a prayer.

Autovacuum can finally use parallel workers. Daniil Davydov's change adds autovacuum_max_parallel_workers and a per-table autovacuum_parallel_workers storage parameter, plus a scoring system that prioritizes which tables to vacuum first. On large, heavily-indexed tables this is the difference between autovacuum keeping up and autovacuum falling permanently behind.

Table bloat gets a first-class answer. The new REPACK command pulls into core what teams have done for years with the external pg_repack tool, and its CONCURRENTLY option rebuilds a table without holding an exclusive lock for the duration. Built on logical decoding from Antonin Houska's pg_squeeze work, it copies the table and replays concurrent writes, taking the heavy lock only for a brief final swap. The catch is the one every logical-decoding feature carries: a concurrent repack consumes a replication slot for the length of the operation, so a stuck transaction holds WAL. If you already run near your slot budget for logical replication or the sync engines that feed on change data capture, you have to plan for it.

Data checksums can now be turned on and off online. Previously you set checksums at initdb time or took the cluster offline with pg_checksums. In 19 you flip them on a running cluster, which removes one of the last reasons large shops shipped without checksums in the first place.

Two default changes are worth knowing before you upgrade. TOAST compression now defaults to lz4 instead of pglz, which is faster to compress and decompress for the large values that get toasted. And just-in-time compilation is now off by default. The reason in the release notes is pointed: the cost model that decided when to JIT "has been determined to be unreliable." Analytical shops that benefit from JIT now have to turn it back on deliberately.

That JIT note is the same idea as plan advice from the other side. The project is admitting the cost model is not always right. Once you admit that, giving operators a way to override the cost model stops looking like heresy and starts looking like the responsible move. The async I/O work continues here too: the worker I/O method, introduced in Postgres 18, now scales its worker pool automatically between io_min_workers and io_max_workers rather than asking you to guess a fixed count.

Notable defaults and removals in 19

RADIUS support is gone entirely. The release note does not hedge: Postgres only ever supported RADIUS over UDP, "which is unfixably insecure." If you authenticate against RADIUS, 19 is a blocker until you move to LDAP, SCRAM, or an external proxy. That is the kind of removal worth catching in beta rather than in production.

When to reach for plan advice

The temptation with a tool like this is to spray it across every slow query. Resist that. The whole argument against hints was that they become technical debt, and plan advice can become debt just as easily if you treat it as a first resort.

The legitimate cases are narrow and real. The strongest is plan stability for a query that is correct but occasionally flips to a catastrophic plan when statistics drift, the classic regression where 99 percent of executions are fine and 1 percent take the server down. Capturing the good plan with pg_stash_advice and pinning it removes the tail risk without touching application code. The second case is investigation: emit advice for a plan, edit one decision, and re-run to understand why the planner chose what it did. That is a debugging tool more than a production control.

A short checklist before you pin anything:

• Exhaust the indirect controls first. A missing index or a stale ANALYZE is the cause far more often than a planner mistake.
• Pin the smallest possible advice. Constraining one join method is safer than freezing an entire plan shape, because it leaves the planner room to adapt everything else.
• Watch the EXPLAIN feedback over time. Advice that drifts to not matched is advice you should delete, not advice you should fight.
• Treat every pinned plan as a dated decision. Write down why you pinned it, because the next engineer will not be able to reconstruct your reasoning from a query id.

If you have spent years tuning queries through statistics targets, extended statistics, and the occasional session GUC, none of that goes away. Plan advice is a sharper instrument for the cases those tools could not reach: a single join in a single query, controlled without a global flag and without rewriting the SQL your ORM generates. Teams who let an ORM generate their queries will appreciate that last part, since you often cannot edit the emitted SQL to add a comment hint even if you wanted to.

A culture shift, carefully fenced

It would be easy to read pg_plan_advice as Postgres losing an argument it held for twenty years. It is closer to the opposite. The project did not adopt inline hints. It built a mechanism that respects every objection on that old wiki page: advice cannot resurrect an invalid plan, it tells you when it has gone stale, it constrains rather than replaces, and it lives outside the query text. The stance was never "control is bad." It was "the common implementations of control are bad." Years of refusing the bad implementations bought time to design a better one.

Beta 1 is the moment to test this, not trust it. The final release is expected around September or October, the advice vocabulary still has gaps around aggregation and set operations, and there are no public numbers yet on what carrying advice through planning costs. Run your worst query through EXPLAIN (PLAN_ADVICE) on a 19 beta and see whether the shape it reports matches what you thought your database was doing. The interesting question is not whether you will pin plans. It is whether a database built around "trust the planner" can absorb a feature built around "sometimes you shouldn't" without the culture quietly tilting toward the second one.