TL;DR
Postgres 19 will include new contrib modules, pg_plan_advice and pg_stash_advice, allowing query hints outside SQL queries. This marks a significant shift in Postgres development, addressing long-standing debates.
Postgres 19’s feature freeze confirms the inclusion of two new contrib modules, pg_plan_advice and pg_stash_advice, which enable query hints outside of SQL queries. This development marks a significant departure from Postgres’s traditional stance against hints, addressing a long-standing debate within the community.
The modules, built by Robert Haas, allow database administrators to influence query planning without embedding hints directly into SQL statements. Instead, advice is set via GUC parameters or stored separately, ensuring queries remain unaltered. The system constrains the planner’s search space, guiding it toward preferred plans while still allowing fallback options if advice is outdated or incorrect.
Historically, the Postgres community has opposed query hints, citing issues like maintenance complexity, upgrade fragility, and negative impacts on planner development. Despite this, the debate persisted, with some community members advocating for hints as a necessary tool in edge cases. The new modules aim to address these concerns by providing a controlled, graceful way to influence planning decisions without compromising the core optimizer’s integrity.
Implications of Query Hints in Postgres 19
This development could reshape how database administrators optimize complex queries, especially in cases where the planner’s decisions are suboptimal. It offers a middle ground, enabling hints without the drawbacks traditionally associated with them. The move signals a potential shift in Postgres’s approach to query optimization, balancing automation with manual intervention where necessary.
For the broader database community, this raises questions about the future of hints in other systems and whether Postgres’s stance might soften further. It also emphasizes the importance of community-driven innovation in addressing long-standing technical debates.
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.
Historical Debate Over Query Hints in Postgres
For over a decade, the Postgres community has largely opposed query hints, citing reasons like increased maintenance difficulty, upgrade issues, and impediments to optimizer improvements. Despite this, informal hints, such as toggling enable_seqscan, have been used by experienced users to influence plans. The 2010 pgsql-performance mailing list thread highlighted ongoing frustrations, with notable figures like Robert Haas and Tom Lane expressing openness to hints if implemented carefully.
Third-party extensions like pg_hint_plan emerged to fill the gap, borrowing syntax from Oracle, but the core community remained cautious. The recent development with pg_plan_advice and pg_stash_advice reflects a reconsideration, driven by the desire for more control over query execution in complex scenarios.
“The advice can only produce plans the core planner considers viable, and degrades gracefully when advice is outdated or incorrect.”
— Robert Haas
“Any hint coded into the actual query becomes a maintenance headache. These modules avoid that by externalizing advice.”
— Josh Berkus
Postgres 19 database administration extensions
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Unanswered Questions About Module Implementation
Details about how widely these modules will be adopted, their exact impact on existing workflows, and how they will be integrated into future releases remain unclear. The community is awaiting further documentation and real-world testing to assess stability and effectiveness.
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.
Next Steps for Adoption and Community Feedback
Postgres developers will likely release detailed documentation alongside the final version of Postgres 19. Community feedback from early adopters and testing phases will shape future enhancements. Monitoring how these modules perform in production environments will be critical to understanding their long-term viability.
PostgreSQL performance tuning modules
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Key Questions
Will query hints be officially supported in Postgres 19?
Yes, through the new modules pg_plan_advice and pg_stash_advice, which enable hints outside SQL queries.
How do these modules differ from traditional hints?
They do not embed hints directly into SQL. Instead, advice is set externally via configuration parameters or stored separately, preserving query simplicity.
Will this change the community’s stance on hints in the future?
It’s uncertain. This development suggests a pragmatic shift, but the core philosophy may still resist embedded hints.
Are there risks associated with using these new modules?
Potential risks include reliance on advice that may become outdated, and the possibility of plans becoming suboptimal if advice is misused. Proper management and testing are recommended.
Source: Hacker News
