TL;DR
Open a free Amazon Business account
Business pricing, bulk buying and tax-exempt orders.
As an affiliate, we earn on qualifying purchases.
Postgres 19 will add query hints through new modules, marking a significant shift after decades of community debate. These modules provide external plan advice, allowing for planner guidance without embedding hints in queries.
Postgres 19’s upcoming release will include two new contrib modules, pg_plan_advice and pg_stash_advice, that enable query hints without altering SQL syntax, marking a major change after years of community debate.
The Postgres community has historically opposed query hints embedded within SQL queries, citing issues like maintenance complexity and upgrade difficulties. However, the new modules introduce a different approach: external plan advice that constrains the query planner’s search space without replacing its judgment. These modules allow users to set guidance via GUC variables or separate storage, ensuring plans remain within the planner’s consideration. The advice is designed to be safe, gracefully degrading if outdated or incorrect, by marking affected nodes as disabled and falling back to the best available plan.
Robert Haas, a key figure behind these modules, emphasized that the advice is external and non-intrusive, addressing long-standing community concerns. The modules do not embed hints directly into SQL queries, avoiding the pitfalls traditionally associated with hints, such as breaking on upgrades or complicating maintenance. This approach seeks to balance the need for plan guidance with the core philosophy of Postgres’s optimizer.
Implications of External Query Hints in Postgres 19
The introduction of external query hints in Postgres 19 signals a significant shift in the database’s approach to query optimization. It offers DBAs and developers a new tool to influence query execution plans without compromising the system’s stability or upgrade path. This development could improve performance tuning, especially for complex or edge-case queries, while maintaining Postgres’s reputation for robustness and simplicity. However, it also raises questions about how widely this feature will be adopted and how it might influence future optimizer development.
PostgreSQL query optimization tools
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Decades of Debate on Query Hints in Postgres
For over a decade, the Postgres community has largely opposed query hints embedded within SQL, citing maintenance, upgrade, and scalability issues. The debate intensified around 2010, with prominent figures like Robert Haas and Tom Lane discussing the need for a controlled way to influence the planner. Third-party extensions like pg_hint_plan emerged as workarounds, but the core community remained cautious. The recent development, led by Haas, aims to address these longstanding concerns by providing external, safe guidance options that do not embed hints directly into queries.
“The advice is set via a GUC or stored separately, keeping queries unencumbered by hints and ensuring safety.”
— Robert Haas
Unanswered Questions About Hint Implementation
It remains unclear how widely adopted these modules will be, how they will perform in diverse real-world scenarios, and whether future versions will expand or restrict their capabilities. The community has yet to see detailed documentation or user feedback post-release, so the long-term impact is still uncertain.
Next Steps for Postgres Hint Modules and Community Adoption
Following the release of Postgres 19, developers and DBAs will begin testing the new modules in production environments. Feedback from early adopters will influence further refinement, and PostgreSQL core developers may consider additional features or constraints based on user experiences. Monitoring community discussions and official documentation updates will be essential to understanding the full impact of this change.
Key Questions
How do the new modules differ from traditional hints?
The modules provide external advice via GUC variables or stored data, avoiding embedding hints directly into SQL queries, thus preventing common issues associated with traditional hints.
Will using these modules affect database stability?
According to the developers, the advice is designed to be safe, with fallback mechanisms that degrade gracefully if advice becomes outdated or incorrect.
Are these modules officially part of Postgres 19?
Yes, they are included as part of the feature freeze and are expected to be available in the upcoming release.
Can I use these modules to override the query planner for all queries?
The modules are intended for targeted, external guidance rather than broad overrides, allowing fine-tuned control without risking overall system stability.
Will this change the community’s stance on hints?
It introduces a new approach that addresses previous concerns, but whether it shifts the core community’s overall philosophy remains to be seen based on adoption and feedback.
Source: Hacker News
Fall Picks
fall essentials
As an affiliate, we earn on qualifying purchases.
