PostgreSQL 19 is now expected on November 19 after the release team pushed GA back for extra review, and that delay lands right in the window where performance engineers should be doing their homework. On October 18 the pg_hint_plan team shipped pg_hint_plan 1.9.0, the first build for PostgreSQL 19 — and it is not just a recompile. It is the first version that rides the planner’s new path generation strategies instead of fighting the planner from the outside.

If you pin plans with hints, this is the release that decides whether your hint inventory survives the upgrade or quietly turns into dead weight.

What shipped in 1.9.0

pg_hint_plan 1.9.0 supports PostgreSQL 19 only. The same announcement carried maintenance releases for older majors: 1.8.1 (PG18), 1.7.2 (PG17), 1.6.3 (PG16), 1.5.4 (PG15) and 1.4.5 (PG14) — the last release planned for PostgreSQL 14. Beyond PG19 compatibility, 1.9.0 integrates with path generation strategies and fixes a set of genuine correctness bugs:

• Infinite loop in the hint parser with multi-query strings.
• Missing CHECK_FOR_INTERRUPTS() in get_hints_from_comment().
• Unnamed indexes, such as hypothetical indexes, no longer confuse scan hints.
• Server crash when HintStateDump[2] throws an error.
• Query ID restored after SPI execution in get_hints_from_table().
• Schema-qualification of operators corrected for the hint table query.

If you run pg_hint_plan 1.8.x or earlier on PG18 and below, those parser and crash fixes are worth a patch window on their own, upgrade or not.

Why hints were fragile before Postgres 19

Hints have always been a negotiation, not a command. Before PG19, pg_hint_plan got its way by flipping planner GUCs such as enable_seqscan and enable_hashjoin during planning, and by carrying its own copies of planner source files and replanning when the core planner refused to drop a path. That works, but it is fragile at scale: on queries with many relations the extension cannot always target the relation you meant, and applying hints adds planning overhead.

PostgreSQL 19 changes the foundation. A commit adding plugin control over path generation strategies gives extensions a bitmask (pgs_mask) on RelOptInfo, so a plugin can say “do not generate a sequential scan path for this relation” at the earliest stage of planning. Lukas Fittl walked through the mechanism and the pg_hint_plan adaptation; the payoff is that hint application becomes declarative instead of a code-copying workaround, and the adaptation drops roughly 2,500 lines of duplicated planner code. Fewer lines touching planner internals means fewer ways for a minor release to break your pinned plans.

Inventory your hints before you test

You cannot migrate what you cannot list. Hints live in two places: comments embedded in application SQL (/*+ ... */) and rows in hint_plan.hints. Start with the table:

SELECT application_name, count(*) AS hint_rows
FROM hint_plan.hints
GROUP BY 1
ORDER BY 2 DESC;

The table is keyed by query_id, which the backend computes when compute_query_id is on. Treat that number as version-specific: after pg_upgrade, verify each row still resolves to the statement you think it does instead of assuming it survived. A hint attached to the wrong query is worse than no hint at all.

For comment hints, grep your application and migration history, then confirm the planner is actually reading them:

SET pg_hint_plan.debug_print = detailed;
SET pg_hint_plan.message_level = log;
EXPLAIN (VERBOSE, COSTS false)
SELECT ... ;   -- your hinted query

The debug output tells you which hints were parsed and which were ignored. Ignored hints are the dangerous ones: the plan still looks pinned in code review, but nothing is holding it.

Run the unaided planner first

The highest-value test in an upgrade rehearsal is free: run your critical workload with hints disabled and diff the plans.

SET pg_hint_plan.enable_hint = off;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ... ;

Capture JSON before and after the upgrade, then compare node types, row estimates and buffer counts query by query. Any query where the unaided plan is now as good as the hinted plan is a hint you should delete. Hints earn their keep only when they beat the planner consistently, not when they paper over statistics that stopped being true two years ago.

Fix statistics before adding hints

Most “we need a hint here” incidents are estimation failures. Correlated columns are the classic case: a city and a ZIP code are not independent, the planner multiplies their selectivities as if they were, and then picks a nested loop over a few million rows. Extended statistics fix that without pinning a plan:

CREATE STATISTICS orders_city_zip (dependencies, ndistinct)
ON city, zip FROM orders;
ANALYZE orders;

Compare EXPLAIN estimates against actual rows before and after. If the estimate error collapses, the hint becomes unnecessary — and that is a permanent fix, whereas a hint is a promise you have to re-verify on every major upgrade. For wider skew, raise the statistics target on the offending column (ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000) and re-analyze before reaching for Rows().

When a hint is genuinely the answer, keep the blast radius small. Rows in the hint table apply by query_id plus an optional application_name, so you can scope a plan to the reporting service without touching the OLTP path that writes the same tables. Comment hints cannot do that — they travel with the query text everywhere it is executed.

A five-step upgrade rehearsal

1. Export hint_plan.hints and grep comment hints; produce one list with an owner and a reason per hint.
2. Restore a production-shaped copy on PG19 RC1 with pg_hint_plan 1.9.0 and replay real query text, not pgbench-only traffic.
3. Capture EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) for the top 50 statements by total time, hinted and unhinted.
4. Diff the four plan sets (old hinted, old unhinted, new hinted, new unhinted) and delete the hints that no longer change the outcome.
5. Re-key or drop hint table rows whose query IDs no longer match, then run the workload for a full business cycle before cutover.

Houston teams running reporting-heavy Postgres — energy settlement, logistics tracking, claims adjudication — feel plan regressions as p99 spikes on month-end jobs rather than as slow pages, which is exactly why the rehearsal has to use real query text.

Bottom line

pg_hint_plan 1.9.0 makes hints less of a hack and more of a supported planning input, but it also raises the bar: hints that only existed because of copied planner code or a misread plan may stop doing anything useful. The November 19 GA date gives you a month to find out which of yours still matter. If you would rather not discover it in production, we run Postgres 19 upgrade rehearsals and hint-inventory reviews for Houston teams. For the timeline itself, see our notes on PostgreSQL 19 RC 1 and the November GA slip, and if runaway queries are the bigger problem, pg_plan_filter 1.0 is a complementary guardrail.