I'm a weirdo here on hacker news because I've mainly worked with Microsoft SQL databases my entire career, but it absolutely blows my mind when I remember that postgres can't lock in a query plan.
It's so nice being able to sleep at night and not have to that an ultra important won't randomly shit the bed.
One of my buddies that works at a PostgreSQL powered place once bitched about how they had a maintenance job that truncated a big table, then another job refreshed statistics, then the table rapidly filled up again and ask the query plans were awful because the table is usually massive.
I get the religion behind the open source stuff: with SQL, you shouldn't have to tell the thing how to build the query plan, you should just tell it what you want and let it figure out how to do it.
But if humans are expected to perform maintenance on the database, setting up scheduled jobs, refreshing statistics, identifying missing indexes, etc., then those same humans should be able to specify a query plan and/or specific indexes to use in a query.
I worked at a company with a big, complex Postgres database behind the back end, probably half of all the incidents we had were related to the query planner suddenly deciding to change its approach.
Eventually you get a vague idea of how to coax Postgres to make the plans you want it to, but the fact that you can't at least lock it in and the plan might change with any number of factors at any time... That's just bad design.
It's so nice being able to sleep at night and not have to that an ultra important won't randomly shit the bed.
One of my buddies that works at a PostgreSQL powered place once bitched about how they had a maintenance job that truncated a big table, then another job refreshed statistics, then the table rapidly filled up again and ask the query plans were awful because the table is usually massive.
I get the religion behind the open source stuff: with SQL, you shouldn't have to tell the thing how to build the query plan, you should just tell it what you want and let it figure out how to do it.
But if humans are expected to perform maintenance on the database, setting up scheduled jobs, refreshing statistics, identifying missing indexes, etc., then those same humans should be able to specify a query plan and/or specific indexes to use in a query.