Looking Forward to Postgres 19: Query Hints

Postgres 19’s feature freeze includes the addition of two new contrib modules—pg_plan_advice and pg_stash_advice—that enable query hints, marking a historic shift in the database’s approach to query optimization.

For years, the Postgres community has resisted implementing query hints, citing concerns over maintenance, upgrade issues, and potential performance degradation. However, with the upcoming Postgres 19 release, developers have introduced modules that allow hints to be set outside of SQL queries, addressing long-standing debates.

The modules, pg_plan_advice and pg_stash_advice, enable users to provide plan guidance without embedding hints directly into SQL, thereby avoiding many traditional pitfalls. Advice is stored separately and influences the planner by constraining its search space rather than overriding its judgment. The system is designed to degrade gracefully if advice is outdated or incorrect, falling back to the planner’s default behavior.

Implications of Query Hints in Postgres 19

This development is significant because it challenges the longstanding community stance against hints, potentially offering DBAs and developers more control over query plans. It may influence future optimizer improvements and change how users troubleshoot performance issues, offering a new tool for fine-tuning database behavior.

Historical Resistance to Query Hints in Postgres

Historically, the Postgres community has opposed hints, citing their tendency to cause maintenance nightmares, upgrade failures, and to hinder optimizer development. Despite this, debates have persisted, with some experts advocating for hints as a necessary tool for edge cases. Third-party extensions like pg_hint_plan filled gaps but were not part of core Postgres.

In recent years, key figures like Robert Haas have been working on modules that provide hints externally, addressing community concerns by constraining rather than overriding the planner, and ensuring safe fallback mechanisms.

“The advice can only produce plans the core planner considers viable, and degrades gracefully if advice is outdated or incorrect.”

— Robert Haas

“Hints embedded directly in queries create maintenance headaches; these modules avoid that by keeping advice outside SQL.”

— Josh Berkus

Remaining Questions About Hint Implementation

It is not yet clear how widely adopted these modules will be, how they will perform in complex, real-world scenarios, or how they might influence future optimizer development. Community feedback and user experiences are still emerging.

Next Steps for Postgres Hint Modules

Postgres developers will likely monitor early adoption and gather feedback on the modules’ effectiveness and stability. Further enhancements or adjustments may follow based on user experiences, and the community will debate whether this approach will influence future core optimizer features.

Key Questions

What are pg_plan_advice and pg_stash_advice?

They are new contrib modules in Postgres 19 that enable users to provide query plan hints externally, influencing the query planner without embedding hints directly in SQL statements.

Why has Postgres historically opposed hints?

Community concerns include maintenance complexity, upgrade failures, discouragement of optimizer improvements, and potential for degraded performance if hints are misused.

Will this change how I troubleshoot query performance?

Yes, these modules could give DBAs more control over query plans, potentially simplifying performance tuning, especially in complex or edge cases.

Are hints safe to use in production now?

While the modules are designed with safety features, their real-world effectiveness and stability are still being evaluated; caution and testing are advised.

Could this lead to more hints being embedded in SQL?

The design specifically avoids embedding hints directly in queries, favoring external advice to mitigate traditional issues associated with hints.

Source: Hacker News