6 ms·

"Not as robust as MySQL"? Surely you're joking.

sgarland 2y ago
They’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going. To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.
- wbl 2y ago
  Yeah but you don't need to worry about your data existing. MySQL has been known to silently fail the one job of a DB.
  - SavageBeast 2y ago
    I recall this being the case A LOOOONG time ago but I haven't heard of, read about, been warned to look out for or personally seen such a thing in forever. Have you? * I'm running a lot of MySQL stuff and such a topic might be of interest to me
    - o11c 2y ago
      From what I can tell, MySQL is supposed to be safe since 2018 if you have no data from before 2010. The fact that you still can't use DDL in transactions makes life exceedingly painful, but it's technically safe if you write your migration code carefully enough.
      - The_Colonel 2y ago
        Lack of transactional DDL is certainly painful, but not unique for MySQL. Oracle doesn't support it either.
        justinclift 2y ago
        Both databases are owned by the same company. Wonder if it's something inherent to the company which has keep that limitation going?
        tpmoney 2y ago
        Oracle also didn't support Boolean data types for a long time, and had a 20 some odd year public thread arguing that no one needed a Boolean data type (https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:6263249199595 https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_...). They finally added it in Oracle 23 which is nice, but I wouldn't consider it to be in good company to be lacking something Oracle also lacks.
        The_Colonel 2y ago
        Not having a boolean data type is IMHO just an annoyance, not comparable to the lack of transactional DDL. But to the point, people often use this point to claim that MySQL is a toy database, not usable for real world production use. I use Oracle as a counterpoint, which also has a lot of warts but is pretty much an archetype of an enterprise-grade DB engine.
      - p_l 2y ago
        Some places still have columns declared as utf8 instead of utf8mb4, and there's a special place in hell for authors of the MySQL general clusterfuck regarding encodings - it was all nice and great if you didn't care about anything other than latin1 or ASCII - go outside that before utf8 option and it was horror that even experienced operators managed to fuckup (I have a badge from a Google conference in 2017 with nicely visible effect of "we have mixed up one of the three separate encoding settings in MySQL and now you have mojibake in your badge"). And then there's UTF8 not actually being UTF8, which can result in total lockup of a table if someone inputs a character that does not fit in UCS-2 and now you need to recover the database from backup and preferably convert all instances of utf8 to utf8mb4, because fuck you that's why.
        lol768 2y ago
        Yeah, the whole charset/collation design is a complete dumpster fire.
        sgarland 2y ago
        In fairness, reasoning about collations is like peering into the abyss. I get why they’re required to have so many levels of detail, and the Unicode Consortium has done a fantastic job, but to say they’re complicated is putting it mildly.
    - elisbce 2y ago
      Yes, it is messy when you want your MySQL databases to be mission critical in production, e.g. handling a large amount of customer data. Historically MySQL's High Availability architecture has a lot of design and implementation issues because it was an afterthought. Dealing with large amount of critical data means you need it to be performant, reliable and available at the same time, which is hard and requires you to deal with caching, sharding, replication, network issues, zone/resource planning, failovers, leader elections and semi-sync bugs, corrupted logs, manually fixing bad queries that killed the database, data migration, version upgrades, etc. There is a reason why big corps like Google/Meta has dedicated teams of experts (like people who actually wrote the HA features) to maintain their mission critical MySQL deployments.
  - maxk42 2y ago
    Not in around 15 years. You're thinking of when MyISAM was the default storage engine for MySQL. It has been InnoDB for over a decade. InnoDB is very reliable - I've never had a single data loss incident in all that time, and I've managed some very large (PB-scale) and active databases. Postgres is definitely more difficult to administer.
    - Symbiote 2y ago
      People are criticising decade-old releases of PostgreSQL, so it seems fair to compare them to similarly aged MySQL releases.
    - yxhuvud 2y ago
      MySQL used to have horrible and very unsafe defaults for new installations that persisted well after the introduction of InnoDB. Those went unfixed for a very long time.
- lelanthran 2y ago
  My experience of both is that MySQL is easier for developers, PostgreSQL is easier for sysads. That was true in 2012; dunno if it still applies though.
  - sofixa 2y ago
    I doubt it was true in 2012, because sysadmins would be the ones trying to make it run reliably, including things like replication, upgrades, etc. Pretty sure that even in 2012 MySQL had very easy to use replication, which Postgres didn't have well into the late 2010s (does it today? It's been a while since I've ran any databases).
    - lelanthran 2y ago
      > I doubt it was true in 2012, because sysadmins would be the ones trying to make it run reliably, including things like replication, upgrades, etc. Possibly I got it wrong and switched around which was easier on the devs and which was easier on the sysads? In my defence, ISTR, when talking to sysads about MySQL vs PostgreSQL, they preferred the latter due to having less to worry about once deployed (MySQL would apparently magically lose data sometimes).
      - karamanolev 2y ago
        MyISAM in the olden days could/would magically lose data. InnoDB has been the de facto standard for a while and I haven't seen data loss attributed to it.
    - yxhuvud 2y ago
      In 2012 MySQL had several flavors of replications, each with its own very serious pitfalls that could introduce corruption or loss of data. I saw enough of MySQL replication issues in those days that I wouldn't want to use it. But sure, it was easy to get a proof of concept working. But when you tried to break it by turning off network and/or machines, then shit broke down in very broken ways that was not recoverable. I'm guessing most that set up MySQL replication didn't actually verify that it worked well when SHTF.
- pritambarhate 2y ago
  My experience has been exactly opposite. Ability to do Vacuums is good. MySQL doesn’t free up space taken by deleted rows. The only option to free up the space is to mysqldump the db and load it again. Not practical in most of the situations.
  - benoitg 2y ago
    Not really, the innodb_file_per_table variable has been set to 1 for a long time. Running OPTIMIZE TABLE frees up the disk space in this case.
    - sudhirj 2y ago
      Is this process materially different from a vacuum? Does it manage to optimise without a write lock?
      - benoitg 2y ago
        I don't know how VACUUM works, I couldn't tell you about the differences. The OPTIMIZE works almost exclusively with online DDL statements. There's only a brief table lock held during table metadata operations, but I haven't found that to be a problem in practice. (https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#optimize-table-innodb-details https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#...)
      - homebrewer 2y ago
        http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2-vacuum-vs.html http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2...
        sudhirj 2y ago
        That helps a lot thanks. Will summarize it quickly for those who come later: MySQL (InnoDB really) and Postgres both use MVCC, so they write a new row on update. InnoDB however also additionally writes a record marking the old row for deletion. To do a cleanup, InnoDB uses the records it kept to delete old data, while Postgres must do a scan. So InnoDB pays a record-keeping price as part of the update that makes it easier to clear data, while Postgres decides to pay this price of occasional scanning.
erik_seaberg 2y ago
Early MySQL versions made egregious design choices like quietly ignoring missing foreign keys and enum typos, truncating long strings, and randomly choosing rows from groups. https://web.archive.org/web/20230922210124/https://grimoire.ca/mysql/choose-something-else/#data-processing https://web.archive.org/web/20230922210124/https://grimoire....
- sgarland 2y ago
  Yeah, it was bad. What kills me is SQLite has its own absurd set of gotchas [0] yet is seen as amazing and wonderful by devs. PKs can have NULLs? Sure! Strings can have \0 in the middle of them? Why not? FKs aren’t enforced by default? Yeah, who needs referential integrity, anyway? My only conclusion is that the majority of devs don’t actually read documentation, and rely purely on the last blog post they read to influence their infrastructure decisions. [0]: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html
  - hu3 2y ago
    Understanding and memorizing shortcomings and quirks takes time and effort. Most devs just go with whatever the influencer du jour says is good. "Nobody ever got fired for choosing insert_currently_hyped_tech_here"
Propelloni 2y ago
It is hard to live down a reputation ;) MySQL was immortalized as the database in every LAMP stack. And just like PHP it improved considerably since then.
- p_l 2y ago
  Also for licensing reasons for a long time there was surviving contingent of MySQL 3.23 in LAMP hosting.
  - johannes1234321 2y ago
    While that change from LGPL to GPL affected only the client library (server always was GPL(+commercial)) and the MySQL company relatively quickly reacted with a FOSS exception to the GPL and by providing a reimplementation of the client library under PHP license (mysqlnd) to serve that market. (I joined MySQL shortly after that mess, before the Sun acquisition)
    - p_l 2y ago
      Random hosting providers that were major place for having your baby steps on LAMP stack didn't necessarily grok licensing much
      - johannes1234321 2y ago
        They also didn't like updating software - to likely that update to PHP or MySQL or something broke some bad script by a customer, who'd complain to the host.
slotrans 2y ago
It is funny reading the replies here. I am a database specialist and have worn the DBA had for many years. I have run MySQL and Postgres in production, both self-hosted and using managed services. Postgres wins on every single dimension that matters, every time. Yes MySQL is easier to setup for non-experts. That counts for nothing. If you are sticking up for MySQL in this thread... I just don't even know, man.

They’re not wrong. If you’ve ever spent meaningful time administering both, you’ll know that Postgres takes far more hands-on work to keep it going. To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.

wbl 2y ago
Yeah but you don't need to worry about your data existing. MySQL has been known to silently fail the one job of a DB.
- SavageBeast 2y ago
  I recall this being the case A LOOOONG time ago but I haven't heard of, read about, been warned to look out for or personally seen such a thing in forever. Have you? * I'm running a lot of MySQL stuff and such a topic might be of interest to me
  - o11c 2y ago
    From what I can tell, MySQL is supposed to be safe since 2018 if you have no data from before 2010. The fact that you still can't use DDL in transactions makes life exceedingly painful, but it's technically safe if you write your migration code carefully enough.
    - The_Colonel 2y ago
      Lack of transactional DDL is certainly painful, but not unique for MySQL. Oracle doesn't support it either.
      - justinclift 2y ago
        Both databases are owned by the same company. Wonder if it's something inherent to the company which has keep that limitation going?
      - tpmoney 2y ago
        Oracle also didn't support Boolean data types for a long time, and had a 20 some odd year public thread arguing that no one needed a Boolean data type (https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:6263249199595 https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_...). They finally added it in Oracle 23 which is nice, but I wouldn't consider it to be in good company to be lacking something Oracle also lacks.
        The_Colonel 2y ago
        Not having a boolean data type is IMHO just an annoyance, not comparable to the lack of transactional DDL. But to the point, people often use this point to claim that MySQL is a toy database, not usable for real world production use. I use Oracle as a counterpoint, which also has a lot of warts but is pretty much an archetype of an enterprise-grade DB engine.
    - p_l 2y ago
      Some places still have columns declared as utf8 instead of utf8mb4, and there's a special place in hell for authors of the MySQL general clusterfuck regarding encodings - it was all nice and great if you didn't care about anything other than latin1 or ASCII - go outside that before utf8 option and it was horror that even experienced operators managed to fuckup (I have a badge from a Google conference in 2017 with nicely visible effect of "we have mixed up one of the three separate encoding settings in MySQL and now you have mojibake in your badge"). And then there's UTF8 not actually being UTF8, which can result in total lockup of a table if someone inputs a character that does not fit in UCS-2 and now you need to recover the database from backup and preferably convert all instances of utf8 to utf8mb4, because fuck you that's why.
      - lol768 2y ago
        Yeah, the whole charset/collation design is a complete dumpster fire.
        sgarland 2y ago
        In fairness, reasoning about collations is like peering into the abyss. I get why they’re required to have so many levels of detail, and the Unicode Consortium has done a fantastic job, but to say they’re complicated is putting it mildly.
  - elisbce 2y ago
    Yes, it is messy when you want your MySQL databases to be mission critical in production, e.g. handling a large amount of customer data. Historically MySQL's High Availability architecture has a lot of design and implementation issues because it was an afterthought. Dealing with large amount of critical data means you need it to be performant, reliable and available at the same time, which is hard and requires you to deal with caching, sharding, replication, network issues, zone/resource planning, failovers, leader elections and semi-sync bugs, corrupted logs, manually fixing bad queries that killed the database, data migration, version upgrades, etc. There is a reason why big corps like Google/Meta has dedicated teams of experts (like people who actually wrote the HA features) to maintain their mission critical MySQL deployments.
- maxk42 2y ago
  Not in around 15 years. You're thinking of when MyISAM was the default storage engine for MySQL. It has been InnoDB for over a decade. InnoDB is very reliable - I've never had a single data loss incident in all that time, and I've managed some very large (PB-scale) and active databases. Postgres is definitely more difficult to administer.
  - Symbiote 2y ago
    People are criticising decade-old releases of PostgreSQL, so it seems fair to compare them to similarly aged MySQL releases.
  - yxhuvud 2y ago
    MySQL used to have horrible and very unsafe defaults for new installations that persisted well after the introduction of InnoDB. Those went unfixed for a very long time.
lelanthran 2y ago
My experience of both is that MySQL is easier for developers, PostgreSQL is easier for sysads. That was true in 2012; dunno if it still applies though.
- sofixa 2y ago
  I doubt it was true in 2012, because sysadmins would be the ones trying to make it run reliably, including things like replication, upgrades, etc. Pretty sure that even in 2012 MySQL had very easy to use replication, which Postgres didn't have well into the late 2010s (does it today? It's been a while since I've ran any databases).
  - lelanthran 2y ago
    > I doubt it was true in 2012, because sysadmins would be the ones trying to make it run reliably, including things like replication, upgrades, etc. Possibly I got it wrong and switched around which was easier on the devs and which was easier on the sysads? In my defence, ISTR, when talking to sysads about MySQL vs PostgreSQL, they preferred the latter due to having less to worry about once deployed (MySQL would apparently magically lose data sometimes).
    - karamanolev 2y ago
      MyISAM in the olden days could/would magically lose data. InnoDB has been the de facto standard for a while and I haven't seen data loss attributed to it.
  - yxhuvud 2y ago
    In 2012 MySQL had several flavors of replications, each with its own very serious pitfalls that could introduce corruption or loss of data. I saw enough of MySQL replication issues in those days that I wouldn't want to use it. But sure, it was easy to get a proof of concept working. But when you tried to break it by turning off network and/or machines, then shit broke down in very broken ways that was not recoverable. I'm guessing most that set up MySQL replication didn't actually verify that it worked well when SHTF.
pritambarhate 2y ago
My experience has been exactly opposite. Ability to do Vacuums is good. MySQL doesn’t free up space taken by deleted rows. The only option to free up the space is to mysqldump the db and load it again. Not practical in most of the situations.
- benoitg 2y ago
  Not really, the innodb_file_per_table variable has been set to 1 for a long time. Running OPTIMIZE TABLE frees up the disk space in this case.
  - sudhirj 2y ago
    Is this process materially different from a vacuum? Does it manage to optimise without a write lock?
    - benoitg 2y ago
      I don't know how VACUUM works, I couldn't tell you about the differences. The OPTIMIZE works almost exclusively with online DDL statements. There's only a brief table lock held during table metadata operations, but I haven't found that to be a problem in practice. (https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#optimize-table-innodb-details https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#...)
    - homebrewer 2y ago
      http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2-vacuum-vs.html http://rhaas.blogspot.com/2011/02/mysql-vs-postgresql-part-2...
      - sudhirj 2y ago
        That helps a lot thanks. Will summarize it quickly for those who come later: MySQL (InnoDB really) and Postgres both use MVCC, so they write a new row on update. InnoDB however also additionally writes a record marking the old row for deletion. To do a cleanup, InnoDB uses the records it kept to delete old data, while Postgres must do a scan. So InnoDB pays a record-keeping price as part of the update that makes it easier to clear data, while Postgres decides to pay this price of occasional scanning.

erik_seaberg 2y ago

Early MySQL versions made egregious design choices like quietly ignoring missing foreign keys and enum typos, truncating long strings, and randomly choosing rows from groups. https://web.archive.org/web/20230922210124/https://grimoire.ca/mysql/choose-something-else/#data-processing https://web.archive.org/web/20230922210124/https://grimoire....

sgarland 2y ago
Yeah, it was bad. What kills me is SQLite has its own absurd set of gotchas [0] yet is seen as amazing and wonderful by devs. PKs can have NULLs? Sure! Strings can have \0 in the middle of them? Why not? FKs aren’t enforced by default? Yeah, who needs referential integrity, anyway? My only conclusion is that the majority of devs don’t actually read documentation, and rely purely on the last blog post they read to influence their infrastructure decisions. [0]: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html
- hu3 2y ago
  Understanding and memorizing shortcomings and quirks takes time and effort. Most devs just go with whatever the influencer du jour says is good. "Nobody ever got fired for choosing insert_currently_hyped_tech_here"

Propelloni 2y ago

It is hard to live down a reputation ;) MySQL was immortalized as the database in every LAMP stack. And just like PHP it improved considerably since then.

p_l 2y ago
Also for licensing reasons for a long time there was surviving contingent of MySQL 3.23 in LAMP hosting.
- johannes1234321 2y ago
  While that change from LGPL to GPL affected only the client library (server always was GPL(+commercial)) and the MySQL company relatively quickly reacted with a FOSS exception to the GPL and by providing a reimplementation of the client library under PHP license (mysqlnd) to serve that market. (I joined MySQL shortly after that mess, before the Sun acquisition)
  - p_l 2y ago
    Random hosting providers that were major place for having your baby steps on LAMP stack didn't necessarily grok licensing much
    - johannes1234321 2y ago
      They also didn't like updating software - to likely that update to PHP or MySQL or something broke some bad script by a customer, who'd complain to the host.

slotrans 2y ago

It is funny reading the replies here. I am a database specialist and have worn the DBA had for many years. I have run MySQL and Postgres in production, both self-hosted and using managed services. Postgres wins on every single dimension that matters, every time. Yes MySQL is easier to setup for non-experts. That counts for nothing. If you are sticking up for MySQL in this thread... I just don't even know, man.