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

111–120 of 366 posts

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

#111

Post by 12907835202 »

test6554 wrote: post #58

I've been using MariaDB (MySQL) as a hobbyist for years. I just set up a couple myqsql servers with phpmyadmin on Raspberry PIs and use them for local development. Basic crud apps, etc. I've always assumed that PostgreSQL is a step up, but never really bothered to look into what I get for the effort. Do I really get anything if I'm not trying to make apps at scale?

Same position.

There's so many things I want to learn I'm not sure postgres is such a step up from MySQL that it's worth being at the top of the list.

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

#112

Post by bcrosby95 »

AdamJacobMuller wrote: post #79
mort96 wrote: post #16

Earlier quoted context omitted.

Ok, here's one: When you give MySQL invalid data, its standard behavior is to just silently store garbage in your database in many cases where PostgreSQL would've told you that your data is invalid. MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions. People aren't providing strong reasons because the question wasn't…

A lot of the MySQL issues historically have been fixed. UTF-8 is better now, invalid data handling is better (by default even! though your distros default config probably puts you back in permissive mode!) but regardless I'm still using Postgres every single time. The fact is that MySQL historically was terrible for complex schemas with complex types while postgres was a pleasure to work with. MySQL had a huge perfor…

In MySQLandia, we do not speak of utf8, we only speak of utf8mb4.

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

#113

Post by eqvinox »

bratao wrote: post #62

One big factor that keep us on MySQL is the MyRocks engine. We have huge databases with billions of rows. The MyRocks enable the use of it with heavy compression, that PostgreSQL can´t handle it, as it is much slower and uses 30x more disk usage, even with heavy TOAST tuning and/or ZFS compression.

To be fair, at the scale of your use case there I really hope you have a proper DBA who understands multiple database systems and their details, and is able to make the best choice for your setup. (At some point the commercial and/or oddball SQL servers become an option too…)

For everyone else who's in most cases not even stuffing a million rows into their database… just stick with Postgres :)

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

#114

Post by evanelias »

dijit wrote: post #86
evanelias wrote: post #80

Earlier quoted context omitted.

> 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 i…

I'm primarily a software engineer, not a member of "large support teams". I've also worked for many years as an independent consultant, brought in when things go wrong, certainly not when they were first "installed". I'm not "afraid" of anything concerning my knowledge going "out the window". If MySQL suddenly disappeared worldwide, I could happily pivot to some other area of software engineering, or I could simply retire. Please stop make assumptions about other people who you know nothing about.

I'm responding to you because you're repeatedly posting factually incorrect items, for years. For example you and I have directly discussed the "MySQL doesn't use SemVer" thing before on HN, and yet here you are again in this thread, claiming 5.6 to 5.7 should be a "minor" upgrade.

Anyway, to the topic at hand, as others have also mentioned in this thread: historically the difficulty with Postgres upgrades has been the lack of cross-version replication, due to Postgres WAL replication being a low-level physical replication system. This made it difficult to perform an upgrade while keeping your site fully online. Perhaps the newer logical replication support makes this easier these days. I hope to learn more about it someday. If you can share your process for upgrading a Postgres cluster while keeping it online, that would be helpful and informative.

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

#116

Post by endgame »

https://web.archive.org/web/20211206040804/https://blog.sess...

From a former MySQL developer:

> let me point out something that I've been saying both internally and externally for the last five years (although never on a stage—which explains why I've been staying away from stages talking about MySQL): MySQL is a pretty poor database, and you should strongly consider using Postgres instead.

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

#118

Post by eqvinox »

gardenhedge wrote: post #10

If Postgres was that much better than MySQL then you would expect to see exact reasons on why to pick it. Every comment so far has not listed any reason.

Array data type.

This has saved my ass a bunch of times. Not even as a column type, just in complex queries that otherwise became unwieldy monsters. The usual "started out simple but now it's a frankenbase" and you're stuck with a shitty schema. (The one thing worse than refactoring code: refactoring databases!)

In one case I was able to replace a 15-minute process with thousands of queries with one single query that aggregated all the data into a bunch of arrays. It completed in a few seconds. (Doing it without arrays would have been possible, but duplicated a lot of data in the result set.)

Post reply on HN