search

DrPostgres / pg_plan_guarantee

Postgres Query Optimizer Extension that guarantees your desired plan will not change
PostgreSQL License
92 stars 1 forks source link

Congrats on Kewl new Project #3

Open luss opened 2 years ago

luss commented 2 years ago

Just cuz the Postgres community does not want this project as part of core... That doesn't mean it's controversial or that it isn't a great idea for customers. Especially those migrating from Oracle who have tuned their apps (sometimes for several decades) & value predictability for their enterprise app above all else.

gurjeet commented 2 years ago

Thanks for the encouraging words, Denis/@luss :-)

It's a controversial idea in the Postgres hackers' community. Well, let me rephrase that: it becomes a controversial topic every time anyone discusses it, to such an extent that the community has put Optimizer Hints/Plan Stability on their not-todo list. They have recently started to acknowledge that they are open to the idea, but the constraints they've put up for an implementation (listed on the linked hints discussion page) are almost impossible to meet.

It's pretty obvious to anyone who doesn't have their head buried in sand, that Optimizer Hints (or some other way of ensuring Plan Stability) are of paramount importance in large, production databases.

I wrote a toy implementation of Oracle-compatible Optimizer Hints at EDB on a whim. But when customers moving from Oracle to EDB/Postgres showed reluctance to move because of absence of hints, I had to make it production grade very quickly. The repertoire of hints that EDB supports has only grown larger since then. This goes on to show that saving money, by moving from expensive (Oracle) to low-cost (Postgres/forks), cannot be the sole driver of migration of large databases/customers. The Postgres community, or anyone vying for adoption by large databases, has to provide what's useful to DBAs and operators of those large databases.

This implementation, of using guaranteed (or frozen) plans, will definitely not meet criteria set by the Postgres hackers community; at least not as it stands right now. I need to refine the implementation further, in terms of UX, security, PG version upgrade resiliency, etc. before I can comfortably recommend people use it in production, and perhaps then I can try to get it included in Postgres core/contrib.