5 ms·

And PostgreSQL is a better MySQL so... all is settled?

by rantanplan 10y ago

stplsd 10y ago
Is it? MySQL improved a lot since 5.1 days, you know.
- matthewmacleod 10y ago
  That's true, and I don't doubt that many developers' opinion of MySQL is tainted by some of the issues in earlier versions. It's still difficult to see what newer versions offer over Postgres though - and Postgres has a lot of bonus features too (like the JSON storage types, which are sublime)
  - wheaties 10y ago
    I'm a big believer in PostgreSQL but it does have one large and glaringly bad area which needs addressing: distribution. MySQL does Master-Master well. PostgreSQL has been a "chose your own adventure" for a long, long time. With the changes they're planning to make to allow better replication, that will change. Right now, MySQL has that aspect beat.
    - takeda 10y ago
      The thing is that generally in Postgres is conservative and avoids features that would endanger data, unless they have a good solution for it.
  - danudey 10y ago
    Based on my experience: MySQL is significantly easier to set up, and it's much easier to admin. PG still seems to do the thing where it assumes your system user is your database user unless you do a bunch of things, and the weird blurred lines between the operating system and the database can get really confusing at the start. On top of that, MySQL is well-documented and lots of people use it for lots of different things, which makes the most common (and most of the uncommon) problems eminently google-able. Anecdotally, I've avoided Postgres also in part because it had the same problem that Perl had when I got into Linux: the community was pretty toxic and unwelcoming. I asked about replication once, and I got a bunch of answers that fell into a few categories: 'Why on earth do you need replication?', 'You don't need replication, Postgres is fast enough', 'set up a script to rsync your data directories over and over again', 'mysql replication is unreliable so you shouldn't use it so it doesn't matter'. That sort of attitude drove me (and, I'd wager, a lot of other people) away from Postgres and towards the vastly more welcoming MySQL community, and I'd wager that a lot of people just kind of stuck there because why switch? Postgres has added a lot of pretty great features lately, and it seems good? But I'm left wondering why I should use Postgres for my RDBMS, my JSON data store, and my KV store, when history has shown that one piece of software that expands to do everything rarely does any of it well. (Postgres has since gained replication abilities)
    - ngrilly 10y ago
      > On top of that, MySQL is well-documented I seriously disagree with that. Documentation is one of MySQL's weakness, especially compared to PostgreSQL which is known to have one of the best documentation in the open source world.
      - juped 10y ago
        I think the comment you're replying to might be using "documentation" to mean Stack Overflow. I really love the PostgreSQL manual.
    - TylerE 10y ago
      By 'a bunch of things' you mean uncomment like 2 lines in the config, right?
      - odonnellryan 10y ago
        Well, he's right about it, kinda. I don't have problems with Postgres, but I do wish it had some defaults that made a bit more sense.
        TylerE 10y ago
        I think the defaults make a fair bit of sense. Do you want it listening on a network interface by default?
  - eulor 10y ago
    If you knew what newer versions of MySQL offered, you would be aware that it also supports JSON.
    - matthewmacleod 10y ago
      Sorry for not having comprehensive knowledge. I see that MySQL added support for a JSON datatype in the past year – I just knew it wasn't in 5.7 last time I looked, and it seems like an unusual feature to add in a 'patch' version.
- TylerE 10y ago
  Latest postgres's have improved tons too. Seems like we're seeing 10% speedups with every point release. Some of the new parrellization stuff in 9.6 is really sexy.
- Buttons840 10y ago
  Has MySQL added support for Common Table Expressions (CTE) yet? CTE was added to the SQL standard in 1999. I ask because I always miss this feature when querying MySQL.
  - mianos 10y ago
    Trolling along, what about working correlated subqueries that don't degrade into a cartesian join of all rows?
- deleted 10y ago
  [deleted]
- spacewhale 10y ago
  Can it yet run queries that refer to a temporary table multiple times, in the same query?
Thaxll 10y ago
MySQL is faster than pg, you should ask why Facebook is running the largest MySQL shop.
- deleted 10y ago
  [deleted]
- pmontra 10y ago
  Maybe because they started with a tiny LAMP system in 2004 and got stuck there to the point they invested considerable resources to write their own PHP interpreter and optimize MySQL. From https://www.percona.com/blog/2014/03/27/a-conversation-with-5-facebook-mysql-gurus/ https://www.percona.com/blog/2014/03/27/a-conversation-with-... "we had the MySQL engineering talent we needed to work with the Oracle team to get 5.6 ready for production at our scale." "We all worked hard to adapt 5.6 to our scale and ensure that it would be production-ready. We found some issues after production deployment, but in many cases we could fix the problem and deployed new MySQL binary within one or two days" "Performance regression of the CPU intensive replication was a main blocker for some of our applications" followed by a description of how they addressed that. So it's not vanilla MySQL vs vanilla PostgreSQL. They tailored MySQL to their needs and keep honing it. What they do has little resemblance with what the other 99.9999% of companies do, and I'm probably missing a few 9s. Another excerpt from that post highlights the differences: "For example, typical MySQL DBA at small companies may not encounter master instance failure during employment, because recent mysqld and H/W are stable enough. At Facebook, master failure is a norm and something the system can accommodate." My take: if they started with and stuck to PostgreSQL they'd have to work on it as they did on MySQL.
  - hodgesrm 10y ago
    Facebook guys as I understand it did look carefully at PostgreSQL; at least some of the engineers are very familiar with its internals. They found MySQL satisfactory for a variety of reasons including ability to engineer better performance but also replication which was much better in MySQL for a long time. In fact, for an interesting perspective on the "Facebook stuck on MySQL" topic see [1]. Interestingly much Facebook database work has been outside the relational orbit on systems like Cassandra and HBase, as well as RocksDB. The choice for most web properties today is not MySQL vs. PostgreSQL but RDBMS vs. "other" systems. [1] https://dom.as/2011/07/08/stonebraker-trapped/ https://dom.as/2011/07/08/stonebraker-trapped/

And PostgreSQL is a better MySQL so... all is settled?

stplsd 10y ago

Is it? MySQL improved a lot since 5.1 days, you know.

matthewmacleod 10y ago
That's true, and I don't doubt that many developers' opinion of MySQL is tainted by some of the issues in earlier versions. It's still difficult to see what newer versions offer over Postgres though - and Postgres has a lot of bonus features too (like the JSON storage types, which are sublime)
- wheaties 10y ago
  I'm a big believer in PostgreSQL but it does have one large and glaringly bad area which needs addressing: distribution. MySQL does Master-Master well. PostgreSQL has been a "chose your own adventure" for a long, long time. With the changes they're planning to make to allow better replication, that will change. Right now, MySQL has that aspect beat.
  - takeda 10y ago
    The thing is that generally in Postgres is conservative and avoids features that would endanger data, unless they have a good solution for it.
- danudey 10y ago
  Based on my experience: MySQL is significantly easier to set up, and it's much easier to admin. PG still seems to do the thing where it assumes your system user is your database user unless you do a bunch of things, and the weird blurred lines between the operating system and the database can get really confusing at the start. On top of that, MySQL is well-documented and lots of people use it for lots of different things, which makes the most common (and most of the uncommon) problems eminently google-able. Anecdotally, I've avoided Postgres also in part because it had the same problem that Perl had when I got into Linux: the community was pretty toxic and unwelcoming. I asked about replication once, and I got a bunch of answers that fell into a few categories: 'Why on earth do you need replication?', 'You don't need replication, Postgres is fast enough', 'set up a script to rsync your data directories over and over again', 'mysql replication is unreliable so you shouldn't use it so it doesn't matter'. That sort of attitude drove me (and, I'd wager, a lot of other people) away from Postgres and towards the vastly more welcoming MySQL community, and I'd wager that a lot of people just kind of stuck there because why switch? Postgres has added a lot of pretty great features lately, and it seems good? But I'm left wondering why I should use Postgres for my RDBMS, my JSON data store, and my KV store, when history has shown that one piece of software that expands to do everything rarely does any of it well. (Postgres has since gained replication abilities)
  - ngrilly 10y ago
    > On top of that, MySQL is well-documented I seriously disagree with that. Documentation is one of MySQL's weakness, especially compared to PostgreSQL which is known to have one of the best documentation in the open source world.
    - juped 10y ago
      I think the comment you're replying to might be using "documentation" to mean Stack Overflow. I really love the PostgreSQL manual.
  - TylerE 10y ago
    By 'a bunch of things' you mean uncomment like 2 lines in the config, right?
    - odonnellryan 10y ago
      Well, he's right about it, kinda. I don't have problems with Postgres, but I do wish it had some defaults that made a bit more sense.
      - TylerE 10y ago
        I think the defaults make a fair bit of sense. Do you want it listening on a network interface by default?
- eulor 10y ago
  If you knew what newer versions of MySQL offered, you would be aware that it also supports JSON.
  - matthewmacleod 10y ago
    Sorry for not having comprehensive knowledge. I see that MySQL added support for a JSON datatype in the past year – I just knew it wasn't in 5.7 last time I looked, and it seems like an unusual feature to add in a 'patch' version.
TylerE 10y ago
Latest postgres's have improved tons too. Seems like we're seeing 10% speedups with every point release. Some of the new parrellization stuff in 9.6 is really sexy.
Buttons840 10y ago
Has MySQL added support for Common Table Expressions (CTE) yet? CTE was added to the SQL standard in 1999. I ask because I always miss this feature when querying MySQL.
- mianos 10y ago
  Trolling along, what about working correlated subqueries that don't degrade into a cartesian join of all rows?
deleted 10y ago
[deleted]
spacewhale 10y ago
Can it yet run queries that refer to a temporary table multiple times, in the same query?

Thaxll 10y ago

MySQL is faster than pg, you should ask why Facebook is running the largest MySQL shop.

deleted 10y ago
[deleted]
pmontra 10y ago
Maybe because they started with a tiny LAMP system in 2004 and got stuck there to the point they invested considerable resources to write their own PHP interpreter and optimize MySQL. From https://www.percona.com/blog/2014/03/27/a-conversation-with-5-facebook-mysql-gurus/ https://www.percona.com/blog/2014/03/27/a-conversation-with-... "we had the MySQL engineering talent we needed to work with the Oracle team to get 5.6 ready for production at our scale." "We all worked hard to adapt 5.6 to our scale and ensure that it would be production-ready. We found some issues after production deployment, but in many cases we could fix the problem and deployed new MySQL binary within one or two days" "Performance regression of the CPU intensive replication was a main blocker for some of our applications" followed by a description of how they addressed that. So it's not vanilla MySQL vs vanilla PostgreSQL. They tailored MySQL to their needs and keep honing it. What they do has little resemblance with what the other 99.9999% of companies do, and I'm probably missing a few 9s. Another excerpt from that post highlights the differences: "For example, typical MySQL DBA at small companies may not encounter master instance failure during employment, because recent mysqld and H/W are stable enough. At Facebook, master failure is a norm and something the system can accommodate." My take: if they started with and stuck to PostgreSQL they'd have to work on it as they did on MySQL.
- hodgesrm 10y ago
  Facebook guys as I understand it did look carefully at PostgreSQL; at least some of the engineers are very familiar with its internals. They found MySQL satisfactory for a variety of reasons including ability to engineer better performance but also replication which was much better in MySQL for a long time. In fact, for an interesting perspective on the "Facebook stuck on MySQL" topic see [1]. Interestingly much Facebook database work has been outside the relational orbit on systems like Cassandra and HBase, as well as RocksDB. The choice for most web properties today is not MySQL vs. PostgreSQL but RDBMS vs. "other" systems. [1] https://dom.as/2011/07/08/stonebraker-trapped/ https://dom.as/2011/07/08/stonebraker-trapped/