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.
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 "what are some objective reasons for picking one over another", but "how do you pick between them". People are simply answering the question OP asked, and a lot of people's process is simply to pick PostgreSQL.
No one is holding the literal bits that make up the database executable accountable here, they are indicating they don't trust the devs of MySQL/MariaDB to do a good job. Whether or not that is an accurate view on their part is arguable, but it's pretty clear from context that they don't think that several if/else statements had it out for them.
Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.
This is a common misconception, but this hasn't been the case for over 7 years. MySQL 5.7, released in Oct 2015, changed its defaults to enable strict sql_mode. All prior versions have hit end-of-life for support years ago, so there is no modern version of MySQL with this silent truncation behavior.
The only reason this problem persists is because Amazon RDS (all versions and forms, including Aurora) uses nonstandard default settings which disable strict mode!
That all said, I do believe Postgres is an excellent database, and a great choice for quite a large range of use-cases. But please, let's compare 2023 Postgres with 2023 MySQL, not 2023 Postgres with much older MySQL. It's only fair.
It might be better now. But for me it’s in the same shithole as mongodb, php (old style, so no recovery there even though it’s possible to create proper code) and most JavaScript.
Other things is that people don’t even want to use Oracle’s MySQL but MariaDB. Why the hell would I want to run a fork of something, and still keep calling it something else.
The only reason for MySQL is wordpress
Heh Heh Heh
On a humorous note, the official MySQL page (in the early 2000's) comparing MySQL vs other databases had the same problem.
They'd list the latest and greatest MySQL version, but compare it against archaic versions of the others. Clearly on purpose, because "Marketing" probably.
Asked them (via official @postgresql.org email address) to please update that page to more a recent PostgreSQL, for fairness. And was completely ignored of course.
So it's kind of amusing to see a request for fairness in the opposite direction (which I agree with anyway) ~20 years later. ;)
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 performance edge for many years but that came at a cost of resiliency and reliability. MySQL has greatly improved on these key areas and Postgres has also made significant performance improvements. Systems these days are also so powerful that the database probably isn't your benchmark.
Regardless, I always use Postgres every single time because I am just scarred from years of dealing with MySQL. What even is MySQL is also an open question at this point, there's MySQL and MariaDB and Percona flavors and the founder of Percona was just ousted and I can't be bothered to put in mental energy to untangle all this to even figure out what MySQL I should be developing against.
Compare this to Postgres where the community seems to have an extremely steady hand and constant progress. There's no forks, there's no infighting, there's no drama, there's a great regular release schedule with incremental improvements.
- Transactional DDL statements (schema modifications)
- Better support for UPSERT operations
- Better JSON support (including ability to index into JSON columns)
- the RETURNING statement to return data that was inserted/updated
In general Postgres is a lot more featureful than MySQL.
That issue was filed in 2005 and it still isn't fixed.
Another gripe I have is that MySQL's JSON Path capabilities are much more limited than the Postgres JSON Path capabilities. MySQL doesn't have filter expressions nor any operators/methods but Postgres does. Don't get me wrong, neither is jq, but I hate having to jump through extra hoops to get my JSON in the right format.
Compare
https://www.postgresql.org/docs/current/functions-json.html#...
and
https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-...
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.)