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

81–90 of 366 posts

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

#81

Post by dylan604 »

evanelias wrote: post #71
dylan604 wrote: post #51

Earlier quoted context omitted.

Whenever I see MySQL, my brain automatically sees MariaDB. What is this Oracle thing you speak of ;-)

MySQL and MariaDB have diverged quite a bit. I recently wrote a roundup of just the differences in table functionality and DDL, and despite keeping the post focused to that relatively-narrow topic, the list of differences is getting VERY long: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schem...

>MySQL and MariaDB

Again, what is this thing you are trying to compare? I just see MariaDB and MariaDB =)

However, because of my blindness to actual MySQL, I have totally not paid attention to any differences between the two. I guess "drop in replacement" isn't actually true any more. Thanks for the info

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

#82

Post by spudlyo »

This is like asking how you'd choose between Emacs and Vim, Mac and PC, Monoliths and Microservices, Functional and Object Oriented .. you're likely going to elicit a lot of passion and not a ton of objective information.

For most applications, either choice is going to be just fine. Use what your team has the most experience with. If you have no experience, try them both out and go with whatever you're most comfortable with.

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

#83

Post by gmac »

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. But I just got the sense that MySQL was developed by people who didn’t quite know what they were doing, and that people who really did know what they were doing weren’t ever likely to be attracted to that to fix it.

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

#84

Post by Volundr »

dijit wrote: post #78
evanelias wrote: post #76

Earlier quoted context omitted.

Please educate me on how my statement about MySQL upgrades is incorrect, I'd love to hear this. I've been using MySQL for 20 years, and while 2 of those years were at Facebook, 18 were not. I've performed MySQL upgrades in quite a wide range of environments, and what you're saying here about lack of in-place upgrades or eating data is simply not aligned with reality. I haven't made any comments regarding performance…

Because it depends so much on your storage engine and schema, I have never seen it recommended because there are circumstances where you have data which is unrepresentative unless you are very careful or you don’t actually use the expressiveness of the DB. I mean, I’ve also seem my share of “ERROR 1071 (42000) at line xxx: Specified key was too long; max key length is xxx bytes” randomly that basically means the mach…

FWIW while I use Postgres for my own development I've had to administer a number of MySQL servers for other devs. Upgrades have always been updating the MySQL package, restarting MySQL, then running `mysql_upgrade`, and restart the server again. I'm pretty sure the mysql_upgrade has even been missed a number of times and it's worked fine.

I won't say it's impossible you ran into issues doing this, but it is the documented and supported upgrade path.

I love Postgres, but as someone whose maintained both for years, upgrades (at small scale) are the one area where I'd say MySQL has Postgres beat.

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

#85

Post by spudlyo »

trympet wrote: post #46

The Postgres query optimizer is more powerful than the MySQL query optimizers [1]. It generally scales better for OLTP. Also tons of extensions that can accelerate your workload. [1] - https://ieeexplore.ieee.org/document/9537400

It's also more opinionated than the MySQL query optimizer, in that you can't give it hints to prevent it from making a horrible mistake.

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

#86

Post by dijit »

evanelias wrote: post #80
dijit wrote: post #78

Earlier quoted context omitted.

Because it depends so much on your storage engine and schema, I have never seen it recommended because there are circumstances where you have data which is unrepresentative unless you are very careful or you don’t actually use the expressiveness of the DB. I mean, I’ve also seem my share of “ERROR 1071 (42000) at line xxx: Specified key was too long; max key length is xxx bytes” randomly that basically means the mach…

> I’ve also seem my share of “ERROR 1071 (42000) at line xxx: Specified key was too long; max key length is xxx bytes” randomly that basically means the machine needs manual recovery. What? This error has nothing to do with upgrades, nothing to do with manual recovery, and hasn't been a common problem for many many years. In old versions of MySQL, it just meant you needed to configure a few things to increase the Inn…

The error I gave is a similar one to the one I used to get with “major” upgrades that happened when Ubuntu decided it was time to upgrade.

It happens and I seriously never claimed that it was an ultra common problem, merely that upgrades in Postgres are more intentional and not painful except for a little extra work between major versions. The standard upgrade path within major versions; 9.x or 10.x or 11.x or 12.x is working just the same as MySQL, except I have much more experience of MySQL completely fumbling their “automatic unattended” upgrade or even the mysql_upgrade command.

Mostly because in the real world outside of engineering cultures databasen are massively abused, ISAM tables that are constantly updated, InnoDB ibdata1 in the terabytes, poor configs, replicas that have skipped a few queries, column changes inside a transaction that failed but actually modified data, it happens. Usually I am called in to clean the mess.

Major difference here is that Postgres doesn’t leave a mess, so I never have the kind of issues that I am describing in this thread with it, and you don’t because I am guessing that you’re there when they’re installed, someone with knowledge was actively maintaining. or you have a lot of people to help with shortcomings.

I get it though. you’ve got your sunk cost knowledge of MySQL and you’ve been on large support teams for it. Maybe you’re afraid I’m suggesting that this knowledge goes out the window. and it has gotten better, but I wouldn’t give my kids watered down led infused soft drinks just because I had suffered through led poisoning. I remember coming to blows with you in other threads over the years because you think MySQL can be saved or is totally fine, but honestly, just, no.

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

#87

Post by LammyL »

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…

Is there a good way to do case-insensitive accent-insensitive collations yet in postgresql? It’s been a holdup for using that for some use cases like searching for data, like a person’s name, in pgsql when the casing or accents don’t match perfectly.

Mssql has had this for ever, and I’m pretty sure MySQL has it as well.

Post reply on HN