4 ms·

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.

mort96 3y ago
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 "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.
- CaveTech 3y ago
  Hasnt been the case for a few major versions, unless you want to anthropomorphise your db and and hold it accountable for past behaviour.
  - ok_dad 3y ago
    > unless you want to anthropomorphise your db and and hold it accountable for past behaviour 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.
- bakugo 3y ago
  > 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. Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.
  - mort96 3y ago
    Ah, that's good to hear. I haven't looked seriously at databases other than SQLite for a long time, it would be interesting to see a more up to date evaluation.
- evanelias 3y ago
  > When you give MySQL invalid data, its standard behavior is to just silently store garbage 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.
  - jbverschoor 3y ago
    Why do they have these settings? Because shit software used shit MySQL +data corruption. 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
  - justinclift 3y ago
    > But please, let's compare 2023 Postgres with 2023 MySQL, not 2023 Postgres with much older MySQL. It's only fair. 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. ;)
- AdamJacobMuller 3y ago
  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 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.
  - bcrosby95 3y ago
    In MySQLandia, we do not speak of utf8, we only speak of utf8mb4.
et-al 3y ago
If you're doing any sort of spatial logic (e.g. mapping), you'll want to use PostGIS.
nicoburns 3y ago
A few reasons: - 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.
l5ymep 3y ago
Postgres has a worse implementation of MVCC. It results in more bloat being produced, and slightly slower updates in a highly concurrent environment. Most businesses don't operate at the scale where this matters. But on the flip side, the tooling and developer experience is much better.
eqvinox 3y ago
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.)
justin_oaks 3y ago
One thing that bit me on MySQL is that triggers don't execute on cascade deletes/updates: https://bugs.mysql.com/bug.php?id=11472 https://bugs.mysql.com/bug.php?id=11472 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#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/current/functions-json.html#... and https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-...

mort96 3y ago

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

CaveTech 3y ago
Hasnt been the case for a few major versions, unless you want to anthropomorphise your db and and hold it accountable for past behaviour.
- ok_dad 3y ago
  > unless you want to anthropomorphise your db and and hold it accountable for past behaviour 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.
bakugo 3y ago
> 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. Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.
- mort96 3y ago
  Ah, that's good to hear. I haven't looked seriously at databases other than SQLite for a long time, it would be interesting to see a more up to date evaluation.
evanelias 3y ago
> When you give MySQL invalid data, its standard behavior is to just silently store garbage 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.
- jbverschoor 3y ago
  Why do they have these settings? Because shit software used shit MySQL +data corruption. 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
- justinclift 3y ago
  > But please, let's compare 2023 Postgres with 2023 MySQL, not 2023 Postgres with much older MySQL. It's only fair. 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. ;)
AdamJacobMuller 3y ago
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 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.
- bcrosby95 3y ago
  In MySQLandia, we do not speak of utf8, we only speak of utf8mb4.

et-al 3y ago

If you're doing any sort of spatial logic (e.g. mapping), you'll want to use PostGIS.

nicoburns 3y ago

A few reasons: - 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.

l5ymep 3y ago

Postgres has a worse implementation of MVCC. It results in more bloat being produced, and slightly slower updates in a highly concurrent environment. Most businesses don't operate at the scale where this matters. But on the flip side, the tooling and developer experience is much better.

eqvinox 3y ago

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

justin_oaks 3y ago

One thing that bit me on MySQL is that triggers don't execute on cascade deletes/updates: https://bugs.mysql.com/bug.php?id=11472 https://bugs.mysql.com/bug.php?id=11472 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#FUNCTIONS-SQLJSON-PATH https://www.postgresql.org/docs/current/functions-json.html#... and https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-syntax https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-...