> A query ran for a long time without issue, and then, one day, some minor shift in your statistics happens, and now you have a major performance issue ..
This is the precise problem I’m working on solving. See the pg_plan_guarantee extension.
I like it. This is comparable to the oracle plan stability feature.
I don't like the interface, however. As you have to wrap the query with custom markers $pgpg$, you can't use it on anything that programatically generates the query, like an ORM.
I'd prefer an interface where you have a table that maps the query (hash?) to a plan. Then create a stored procedure e.g. nail_plan('SELECT blah blah blah') that inserts a record in that table. You can then backup and restore plans, easily query what plans are guaranteed, maybe even migrate plans between dev and prod. Table could also mark which plans are now invalid.
This is the precise problem I’m working on solving. See the pg_plan_guarantee extension.
https://github.com/DrPostgres/pg_plan_guarantee