Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: It's 2023, how do you choose between MySQL and Postgres?

news.ycombinator.com

Post reply on HN
Print view View original

191–200 of 366 posts

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#191

Post by mardifoufs »

stouset wrote: post #166
gmac wrote: post #83

Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…

Yep. The real question here is: it's 2023, why would you choose MySQL over PostgreSQL? Not that there aren't reasons. There are some. But for starting out with a new app without a very, very good reason to do something different? PostgreSQL every day of the week.

Ease of updates is a very good reason. Handling connections too.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#192

Post by 2muchcoffeeman »

gmac wrote: post #83

Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…

It’s really unfair because a lot may have changed in 10 years so it might be worth reconsidering.

But I’m like you, MySQL did some nonsense once that took me hours to work out. So now I really can’t be bothered with any potential quirks it may still have. This is not an SNL sketch.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#193

Post by paulddraper »

gmac wrote: post #83

Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…

FWIW, it hasn't changed in ten years.

Here is an 18-year-old bug, that DELETE triggers don't work for foreign key cascades: https://bugs.mysql.com/bug.php?id=11472

That makes the entire feature mostly worthless. Reported in 2005, last updated in 2008.

---

While I would choose PostgreSQL every time, MySQL has the following advantages:

1. Write-performance, due to fundamental design tradeoffs. [1]

2. Per-connection resources, due to single-process design.

3. Related to #1, no vacuum requirement.

[1] https://www.uber.com/blog/postgres-to-mysql-migration/

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#194

Post by irrational »

Easy. Choose Postgres. I worked with Oracle for 15 years before moving to Postgres. In my experience, Postgres has been better documented, more performant, better standards compliant, etc. Now, you are asking about MySQL and not Oracle, but I’ve never heard anyone say MySQL is better than Oracle, while I have heard that about Postgres many times.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#195

Post by adw »

If I were starting from zero knowledge, I'd choose Postgres, but you're not. Unless you require on particular database extensions (full-text search, PostGIS, etc), or you're operating at the kind of scale where all bets are off, it won't matter very much; use the one your team has more expertise with. MySQL sucked twenty years ago, but it's had twenty years of people beating on it, so it largely sucks in precise, well-understood ways now. This is nearly as good as being good.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#196

Post by scosman »

Postgres for almost anything. Robustness, ecosystem, accuracy, all top notch.

One exception: I did migrate two very large tables (15B+ rows) from Postgres to MySQL for performance reasons. InnodB (MySQL storage engine) can arrange the records on page by primary key, and if you have a multi-value PK (user_id, uuid) it means all records from a user are in the same set of pages. Huuuuge improvement over having your data for a user spread out over N different pages. Memory cache way more efficient. Orders of magnitude speed up on cold queries, better cache hit rate, and cost reduction from smaller servers.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#197

Post by scosman »

srcreigh wrote: post #147
dijit wrote: post #36

There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…

Postgres is >50x slower for range queries(example below) and is akin to using array-of-pointers (ie Java) whereas MySQL supports array-of-struct (C). Illustration from Dropbox scaling talk below. Sneak peek photo [1] (from [2]). Just imagine its literally 500-1000x more convoluted per B-tree leaf node. That's every Postgres table unless you CLUSTER periodically. [1]: https://josipmisko.com/img/clustered-vs-noncluster…

I made same comment elsewhere before finding this comment. I can vouch for this speed up in the ratio of records per page. It is very real. Only applies for small records where you can pack many rows into a page, and where you can cleanly partition by user/tenant, but that’s common enough.

I will say: we kept every table we didn’t have to migrate for perf reasons in Postgres, and never regretted it.

Edit: and the index “fix” for Postgres doesn’t work often. Postgres will validate the row on disk, even if it’s in the index, if the page’s visibility map isn’t set. If you data isn’t write once, there’s a decent chance your page is dirty and it will still make the “heap” fetch.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#198

Post by eyelidlessness »

dijit wrote: post #36

There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…

The only reason I’d consider MariaDB, which I’m surprised I don’t see mentioned currently, is its bitemporal features. There are whole worlds of problems solved that are almost universally badly done in schema/business logic instead.

Granted I haven’t had to make a decision like this for several years, I’ve hardly even touched a database except to debug some stuff on localhost that’s outside of my explicit purview. So maybe Postgres solutions have narrowed the gap on this without my knowing it.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#200

Post by viraptor »

pawelduda wrote: post #175
donatj wrote: post #109

Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Orac…

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.

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".

Post reply on HN