TL;DR
Get travel and outdoor gear delivered free with Prime
- Fast, free delivery on millions of items
- Prime Video, Amazon Music and more included
- Member-only deals all year
Postgres 19 introduces query hints through two new modules, marking a significant shift in the database’s approach. This development addresses longstanding community debates about hints and their role in query optimization.
Postgres 19 has officially incorporated query hints through two new contrib modules, pg_plan_advice and pg_stash_advice, marking a historic shift in the database’s approach to query optimization and community debates.
The feature, part of the Postgres 19 release, introduces two modules that enable users to influence query planning without embedding hints directly into SQL queries. These modules allow setting advice externally, either via GUC parameters or stored in a separate stash keyed by query ID, thus avoiding modifications to query text.
Historically, the Postgres community has opposed hints, citing concerns over maintenance, upgrade issues, and impeding optimizer improvements. Despite this, longstanding debates have persisted, with notable figures like Robert Haas advocating for hints as an escape hatch for edge cases. The new modules aim to address objections by constraining the planner’s search space rather than overriding it entirely, and they degrade gracefully when advice is outdated or incorrect.
The development was led by Robert Haas, who has been involved in the hints debate since 2010, and represents a significant change in Postgres’s query optimization philosophy.
Implications of Query Hints in Postgres 19
This development matters because it introduces a controlled way for users to influence query plans, potentially improving performance for complex or edge-case queries. It also signifies a shift in Postgres’s stance on hints, historically opposed, now offering a formal mechanism that respects the core optimizer’s judgment while providing flexibility.
For database administrators and developers, this could mean more predictable performance tuning without risking the instability associated with traditional hints. However, it also raises questions about future impacts on the optimizer’s evolution and community consensus.
Products Worth Considering
DJI Osmo Mobile 6 Smartphone Gimbal Stabilizer with Extension Rod, Slate Gray
Away The Large 29-Inch Hardside Checked Suitcase
Samsonite Element Hardside 28-Inch Expandable Checked Suitcase
As an affiliate, we earn on qualifying purchases.
Historical Debate Over Query Hints in Postgres
For over a decade, the Postgres community has largely rejected query hints, citing issues like maintenance nightmares and upgrade difficulties. The debate intensified around 2010, with prominent figures advocating for hints as a necessary tool for edge cases, despite the official stance. Third-party extensions like pg_hint_plan emerged to fill the gap, but the core development team remained cautious.
Haas’s recent work on pg_plan_advice and pg_stash_advice reflects a long-term effort to reconcile user needs with community principles, offering a compromise that constrains rather than overrides the planner’s decisions.
“We should be willing to provide a way for those people to not get fired when they hit the 0.1% of queries that can’t be fixed using existing methods.”
— Robert Haas
Remaining Questions About Hint Implementation
It is not yet clear how widely adopted these modules will be in practice, or how they will influence future optimizer development. The community’s long-standing opposition raises questions about acceptance and long-term stability. Additionally, the effectiveness of these modules in real-world scenarios remains to be seen, as detailed performance impacts are still emerging.
Next Steps for Postgres Query Hint Features
Postgres developers will monitor the adoption and feedback from early users of pg_plan_advice and pg_stash_advice. Future updates may refine these modules based on community input. Meanwhile, users are encouraged to experiment with the new tools to assess their impact on query performance and stability, and discussions within the community will likely shape the evolution of hint support in subsequent releases.
Key Questions
How do the new modules implement query hints in Postgres 19?
The modules allow setting advice outside of SQL queries via GUC parameters or a separate stash, constraining the planner’s search space without embedding hints directly into query text.
Will this change the core philosophy of Postgres regarding hints?
It represents a shift, offering a controlled, external mechanism for hints rather than direct query annotations, aligning with the community’s cautious approach while addressing user needs.
Are these hint modules ready for production use?
They are included in the Postgres 19 release, but users should evaluate their effectiveness in testing environments before deploying in critical systems.
Could this lead to more widespread use of hints in Postgres?
Potentially, especially for complex queries where planner behavior needs fine-tuning. However, acceptance will depend on community feedback and demonstrated stability.
What are the risks associated with using these hint modules?
Risks include potential over-reliance on hints, which may mask underlying issues or hinder optimizer improvements if misused. Proper understanding and cautious implementation are advised.
Source: Hacker News
