TL;DR
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.
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 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
“I haven’t seen a hinting scheme that didn’t suck… I don’t say that there can’t be one.”
— Tom Lane
“Any hint which gets coded into the actual queries becomes a massive maintenance and upgrade headache thereafter.”
— Josh Berkus
Postgres 19 query hints modules
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
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.
QUERIES, CHAOS & CLARITY: SQL and NoSQL Database Software Architecture Performance Analysis and Assessments
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
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.
PostgreSQL query plan advice extension
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
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
