Lotus Reader
pawelduda
2 years ago
0
8
In reply toAsk HN: It's 2023, how do you choose between MySQL and Postgres?by debo_
What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1.

And that one case I remember was perfectly solvable the regular way, with a little more time.

viraptor2 years ago
The problem is that when you you need to tell the planner what to do, you can do it in MySQL, but not postgres. Imagine you've got a production database with lots of traffic which suddenly can't handle anything because it inserted an extra row which tipped the balance and now takes seconds to process a common query.

Do you know how to fix the table statistics quickly? Do you know how to change that query to force the execution plan you want? Do you know how long the solution will last until the stats change again?

MySQL is a bit more predictable for this case and if things go really bad for some unexpected reason, one comment can fix it.

I'm looking at it from ops perspective. The ratio during development doesn't matter that much - all issues are solvable at that stage. For me it's rather "which situation would I rather be in at 3am".

ants_aviraptor2 years ago
There is the pg_hint_plan extension that gives most of what you would want in a hinting system.

I think a better fix lies in the direction of making queries more of a first class object with options to nail down plans, add custom logic to pick plans dependent on parameter values, etc.

avinasshants_a2 years ago
I am on GCP/AWS, its not possible to use extensions. They allow only a few whitelisted extensions. If it is built into the DB, then I can use without any hassles.
paweldudaviraptor2 years ago
I googled a bit and had no idea how many questions about Postgres query planner going nuts are out there. I just imagined this is a problem that creeps over time (giving you time to notice and act in advance, assuming you have monitoring/alerts set up) rather than suddenly tipping the scale - though it probably can happen suddenly after large data import.

Personally never ran into this with Postgres nor had anyone I know worry about it - the query planner was reliable for me in 99.99% of cases but yeah, I admit that it's a black box for me that I expect to take care of internals - hopefully it continues to do so, but I got to give it to MySQL for allowing to override it then.

jitl2 years ago
Postgres query planner suddenly deciding to do something silly in the middle of the night has taken Notion down a few times. It's quite annoying, and it's very frustrating to have no recourse.
jhas78asdjitl2 years ago
Any posts on this? Are there bulk data loads that make table stats more stale and affect plans? I’m wondering what would suddenly make a plan selection change a lot that might be a contributing factor.
natmakajhas78asd2 years ago
> bulk data loads that make table stats more stale

This is the usual culprit (cure: "ANALYZE ((tablename))").

Collecting more samples (ALTER TABLE SET STATISTICS...) may be useful.

"Extended Statistics" covers most(?) other cases: https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED https://www.postgresql.org/docs/current/multivariate-statistics-examples.html

paweldudajitl2 years ago
Interesting - how do you approach it when it happens and you're under time pressure to bring it back online - assuming you can't just fix query plan? I'd probably start by tweaking stats options and resetting them for problematic tables but don't have further ideas from the top of my head.