TL;DR
Postgres 19 will include two new modules, pg_plan_advice and pg_stash_advice, allowing users to apply query hints without embedding them directly in SQL. This marks a significant shift in Postgres development. The feature is in the final stages of development and is expected to be included in the upcoming release.
Postgres 19’s feature freeze now includes two new contrib modules—pg_plan_advice and pg_stash_advice—that enable query hints, marking a major shift in the database’s approach to query optimization.
These modules provide a way for database administrators and developers to influence query planning without embedding hints directly into SQL queries. Unlike traditional hints used in other databases, these modules keep advice external to the query text, set via configuration or stored in a separate stash, thus avoiding many of the pitfalls historically associated with hints.
The development was led by Robert Haas, who has long been involved in the hints debate. The modules constrain the query planner’s search space rather than override it outright, allowing the planner to consider only plans deemed viable by the provided advice. If advice is outdated or incorrect, the planner gracefully degrades performance by falling back to its default behavior, marking affected nodes as disabled.
The inclusion of these modules signals a potential paradigm shift in Postgres development, challenging the longstanding community stance against hints, which was rooted in concerns over maintenance, upgrades, and planner integrity.
Implications of Query Hints in Postgres 19
The introduction of query hints via these new modules could significantly alter how Postgres users optimize queries. It provides an escape hatch for edge cases where the optimizer’s decisions are suboptimal, potentially improving performance in complex workloads. However, it also raises questions about maintaining the integrity of the optimizer’s decisions and the risk of encouraging manual tuning that could hinder future planner improvements.
This development may influence broader community attitudes towards hints and could set a precedent for more flexible query optimization strategies in future PostgreSQL releases.
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 Resistance and Recent Shifts in Hint Support
For years, the Postgres community has maintained a firm stance against query hints, citing issues like maintenance complexity, upgrade difficulties, and the risk of undermining optimizer improvements. The official stance was that hints should not be embedded in SQL, and external solutions like pg_hint_plan were used as workarounds.
Debates on this topic date back to 2010, with prominent figures like Robert Haas and Tom Lane expressing skepticism about hints. Despite this, the community acknowledged that hints could be useful in rare edge cases, leading to the development of third-party extensions. The current move to include hint-like functionality officially in Postgres marks a notable departure from this long-held position.
“He led the development of these modules, emphasizing that advice only constrains the planner’s search space, rather than replacing its judgment.”
— Robert Haas
“Hints are problematic because they create maintenance nightmares, break on upgrades, and impede planner improvements.”
— Postgres community official wiki
Learning PostgreSQL 18: The Complete Guide to Database Performance and Scaling
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Remaining Questions About Implementation and Adoption
It is not yet clear how widely adopted these modules will become or how the community will respond to their inclusion. Details about their integration into the core system, potential limitations, and best practices are still emerging. There is also uncertainty about how future releases will balance hint support with the ongoing development of the optimizer.
PostgreSQL external query hints modules
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Next Steps for Postgres Hint Modules and Community Adoption
Developers and users should monitor the upcoming Postgres release notes for official inclusion and documentation of these modules. Testing and feedback from early adopters will shape how these features evolve. The community may also revisit the broader debate on hints, potentially leading to more flexible query tuning options in future versions.
PostgreSQL pg_plan_advice extension
As an affiliate, we earn on qualifying purchases.
As an affiliate, we earn on qualifying purchases.
Key Questions
Will query hints be officially part of Postgres in version 19?
Yes, the new modules pg_plan_advice and pg_stash_advice are included in the feature freeze of Postgres 19, enabling query hints outside of SQL text.
How do these modules differ from traditional hints in other databases?
They keep advice external to SQL queries, set via configuration or stored separately, and constrain rather than override the planner’s decisions, reducing many traditional issues associated with hints.
Will using hints undermine the optimizer’s improvements?
The modules are designed to limit the planner’s search space without replacing its judgment, aiming to preserve ongoing optimizer enhancements.
Could this change the community’s long-standing stance on hints?
Potentially, as official support may lead to more nuanced use cases and a reevaluation of the previous outright opposition to hints.
When is Postgres 19 expected to be released?
The release is anticipated in late 2024, with the modules likely to be included in the final version.
Source: Hacker News
