TL;DR
Postgres 19 will include new modules, pg_plan_advice and pg_stash_advice, allowing users to provide query hints externally. This marks a significant shift, breaking decades of community opposition to hints.
Postgres 19’s feature freeze includes the introduction of two new contrib modules, pg_plan_advice and pg_stash_advice, providing a way for users to influence query planning through hints—an unprecedented move after decades of community opposition.
The new modules, developed by Robert Haas, enable users to set query hints outside the SQL text, either via GUC parameters or stored advice, avoiding modifications to query syntax. These hints constrain the query planner’s search space rather than replacing its judgment, aiming to address edge cases where the optimizer may underperform.
Historically, the Postgres community has been firmly opposed to hints, citing issues like maintenance complexity, upgrade failures, and impeding planner improvements. Despite this, the debate persisted, with notable figures like Robert Haas and Tom Lane expressing openness to hints that work within specific constraints.
The modules are designed to provide advice that the core planner considers viable, with fallback mechanisms to maintain plan quality even when advice is outdated or incorrect. This approach aims to balance user control with the database’s autonomous optimization capabilities.
Implications of Introducing Query Hints in Postgres 19
The inclusion of query hints in Postgres 19 could influence database performance tuning, offering DBAs additional options for influencing query execution plans. It represents a departure from the traditional stance of the Postgres community, which has generally been cautious about hints. The change may lead to discussions about the appropriate use of hints and their impact on system stability and future development.
PostgreSQL with AI: A Complete Beginner's Guide to PostgreSQL Databases, Query Optimization, Analytics, and AI-Powered Data Systems
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
History of Hints Debate in Postgres Development
For many years, the Postgres community has expressed reservations about hints, emphasizing the optimizer’s capabilities and concerns about maintenance challenges. The official position has been that hints can create scalability issues and complicate upgrades. Nonetheless, user requests for hints to address specific edge cases have been documented, and third-party extensions like pg_hint_plan have provided alternative solutions. The recent development by Robert Haas, who has been involved in the hints discussion, indicates a potential shift towards more flexible guidance mechanisms managed externally.
“Advice only nudges the planner toward plans it already considered viable, and degrades gracefully when outdated or wrong.”
— Robert Haas
“I’m open to hints that work within specific constraints, but they shouldn’t replace the optimizer’s judgment.”
— Tom Lane
Postgres 19 performance tuning modules
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Unanswered Questions About Postgres 19 Query Hints
It remains to be seen how widely adopted the new modules will be upon release, and whether they will address community concerns regarding maintenance and upgrade stability. The long-term effects on the core optimizer and compatibility with existing extensions like pg_hint_plan are also uncertain.
database query hints extension
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Next Steps for Postgres Hint Functionality Development
The Postgres community will observe the implementation and adoption of pg_plan_advice and pg_stash_advice following the release of Postgres 19. Feedback from users and developers will inform future updates or restrictions. The community may also revisit discussions about hints based on early experiences with the new modules.
Learn PostgreSQL: Use, manage, and build secure and scalable databases with PostgreSQL 16
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Key Questions
Will query hints be enabled by default in Postgres 19?
No, the modules are optional and need to be explicitly enabled and configured by users.
How do the new modules differ from third-party extensions like pg_hint_plan?
The new modules manage hints externally via GUC or stored advice, avoiding modifications to query syntax, unlike pg_hint_plan which embeds hints within SQL comments.
Could the introduction of hints lead to regressions or plan instability?
The modules are designed to constrain the planner’s choices without replacing its judgment, aiming to minimize risks. However, improper use could still cause plan issues.
Will this change the community’s stance on hints long-term?
It remains to be seen how the community responds post-release, and whether this leads to broader acceptance or renewed caution regarding hints.
Source: Hacker News
