6 ms·

I agree, I've found through painful experience that the shiny and new, while incredibly fun, tends to cost more than it's worth. With that said, if you're goin

by dpratt 11y ago

I agree, I've found through painful experience that the shiny and new, while incredibly fun, tends to cost more than it's worth. With that said, if you're going to take the prudent route and use an established database platform, why in the name of all that is holy would you pick MySQL over Postgres? Granted, I am rather biased, but I do have extensive experience using both in anger. Every time I have to use MySQL I walk away with the impression that it was built with the same design mentality that PHP was. The whole point of a transactional RDBMS is to keep your data as consistent and accurate as possible. I cannot in good faith use a database that (silently!) will coerce badly formatted input into what it thinks should be in the column.

philliphaydon 11y ago
I don't know why anyone in 2015 would pick mysql when postgres is an option. Live postgres. So nice to work with. It's json support is amazing too
- nchelluri 11y ago
  You know what, about two weeks ago I was starting a new Rails hobby project, and I thought "all I've heard about for the past year is how everyone loves Postgres", and was about to throw it in there, when I saw a link to Google search trends comparing the two, and MySQL is still so far ahead: https://www.google.com/trends/explore#cmpt=q&q=mysql,+postgresql https://www.google.com/trends/explore#cmpt=q&q=mysql,+postgr... It's quite possible this is just due to people supporting existing applications, but I dunno, I am tempted to stick with MySQL for new stuff for the next little while.
  - 0942v8653 11y ago
    It seems weird to me that Postgres and MySQL are both going down. I'd expect interest in Postgres to be rising at the very least—what are people switching to?
    - tracker1 11y ago
      Probably hosted SaaS offernings, some of which may actually be Postgres and MySQL... just there's less need for queries about operations issues. There's also a lot of NoSQL solutions that are being ever more widely used. Most of which have much lower costs for HA early on.
  - steckerbrett 11y ago
    Is google trends really how you should be choosing your stack?
    - UnoriginalGuy 11y ago
      Choose? I'd argue no. But inform your choice? Sure. Looking at trends can help you: - See how easy hiring people will be in the future. - See the likelihood that your chosen framework will lose support. - See the likelihood of third party support/addons/libraries/etc. - How easy it will be to find help/tutorials/guides/etc. But looking at it, to me, seems prudent.
  - clessg 11y ago
    MySQL is popular because it's easy to use (in the same sense as PHP), easy to setup, and has a lot of tutorials written for it. Also speed used to be a pretty big factor, but that was only because MyISAM took shortcuts with data consistency. If you care about your data and would rather not deal with the many problems MySQL inevitably brings, then Postgres is the better option. I can't say I've seen many compelling reasons to use MySQL over Postgres other than "what if we need to replace you with a freelance PHP guy", "I want to pay lower wages", and "I want to use phpmyadmin". (Btw: check out Sequel Pro and Querious!) Popularity isn't a very good reason to choose something. Postgres will remain a juggernaut for a long time. Unless you really think there's a good chance you'll need to hire somebody that only knows MySQL, then I'd go with Postgres.
    - tracker1 11y ago
      Given I haven't done anything with it in nearly 7 years now.. but every time I've touched MySQL for anything, there was always a quirky behavior that bit me in the ass that no other dbms does... Postgres, MS-SQL, Firebird, Oracle, DB2 are all better options in my mind... unless you need a good replication story, without a pricey support contract, then Postgres and Firebird are sadly out of the running imho.
    - spudlyo 11y ago
      That may be why MySQL is popular for small installations, but that's not why it's popular with companies like Facebook, Google, Twitter, and Pinterest who run replicated clusters that number in the thousands. It's popular at scale because unlike Postgres, it has replication that is well understood, is reliable, and isn't a massive pain in the ass to operate.
      - Jweb_Guru 11y ago
        Please point me to the well-understood, reliable replication solution for MySQL (I'll agree about pain in the ass to operate re: Postgres, but you might want to check out aphyr's recent posts).
  - kawera 11y ago
    One of the reasons MySQL is still popular is Wordpress.
- cageface 11y ago
  Agreed. I consider MySQL a red flag and avoid any projects or teams that choose it over Postgres. Postgres is reliable, flexible, fast, and well documented. It's one of the crowning achievements of the open source world.
  - UnoriginalGuy 11y ago
    > I consider MySQL a red flag and avoid any projects or teams that choose it over Postgres. I am in the "pro Postgres" camp, but this attitude is too black and white for me. For many projects Postgres in superior to MySQL, but there are situations and requirements where MySQL makes a lot more sense. For just one example, I can get MySQL as a service much cheaper and easier than I can get Postgres as a service. Which isn't to say Postgres as a service doesn't exist (it does), just to say MySQL is more readily available and there is more competition in that sector. Additional if I had a DBA who was a MySQL expert and didn't know a thing about Postgres, I'd go with MySQL for my project. Since a good DBA is worth their weight in gold, and an expert in a particular DBMS can be a massive asset to a project. But given no restrictions or needs, I'd pick Postgres as my "default" choice. I just don't think it is right to say you'd avoid a team/project just because they picked MySQL, sometimes there are completely legit reasons (or politics).
  - zamalek 11y ago
    What I love about Postgres is how it's the only remaining innovator in the SQL space. It's like Microsoft and Oracle were circa 1999 (the last true innovation out of either was sparse columns in MSSQL). Genetic algorithms, the works. Everything else is mostly stagnant and archaic.
- lqdc13 11y ago
  Depends on your data. Sometimes (particularly when you have only 1-3 tables) MySQL is much faster and you have to go out of your way writing stored procedures to make Postgres compete. Example: select COUNT(1), status FROM some_table GROUP BY status ORDER BY status; -- Postgres 10x slower. Also, mysql CLI is IMO much nicer than the postgres version. Better and faster autocompletes.
  - nimrody 11y ago
    Regarding CLI: mycli and pgcli are both much nicer than the built in alternatives.
  - Jweb_Guru 11y ago
    I'd like to see your full setup for your claim about 10x slower, because I suspect you're doing something wrong.
    - lqdc13 11y ago
      Sure, here's the pastebin of the postgres one: http://pastebin.com/Phc8KnYU http://pastebin.com/Phc8KnYU I thought I was doing something wrong, so I asked on SO: http://stackoverflow.com/questions/32755348/group-by-count-query-takes-a-long-time http://stackoverflow.com/questions/32755348/group-by-count-q... This is a very simple example, but I always run into major performance issues with Postgres. Some of them are my fault, from not knowing that array aggregation is a slow operation. MySQL advantages for me are that there is an actual UPSERT operation (an inferior version of which might be added to Postgres 9.5), that the CLI is miles ahead, and that it's much faster for a small number of tables with a lot of data and with non-complex operations.
      - Jweb_Guru 11y ago
        I'm going to ignore your baseless (and incorrect) assertion about "an inferior version" of UPSERT (MySQL's doesn't work properly except for the primary key). Your problem is that you are not doing an index only scan. The reason you are not doing an index only scan is, most likely, that the visibility bit is not set on the table. This is usually as a result of excessive writing to the table, but sometimes it is due to incorrectly tuned planner settings. Try setting your cpu_tuple_cost higher and/or cpu_index_tuple_cost lower. I have had to do this in the past to get index only scans to work. BTW, a quick way to force the issue is to SET enable_seq_scan TO off in psql. I would add, you should really upgrade to 9.4, as there were some performance improvements to aggregates.
brobinson 11y ago
Good article on the dangers and time-wasters of MySQL: http://grimoire.ca/mysql/choose-something-else http://grimoire.ca/mysql/choose-something-else
tracker1 11y ago
mySQL has more baked in solutions for replication that don't require a support contract or a convoluted mess of choosing between half a dozen half baked options for higher availability? Honestly, it's the one area that I feel that open-source PostgreSQL is really lacking, and that is a good replication/automagic-failover story. I think MS-SQL is probably the best (but almost as expensive as EnterpriseDB support contract) for the supported versions. MySQL is probably the best option for a lot of people. That said, I think RethinkDB is a pretty decent all-around option for most workloads at this point.
- akurilin 11y ago
  As someone who took a stab at automating slony deployment and configuration, I have to agree that the replication situation is not quite there yet. There are maybe 2-3 examples online on how to use some of those tools, and maybe half a dozen people who actually would be able to answer questions about them. Betting your business's scaling future on that kind of tooling is terrifying. Would be great to have both out of the box failover (you can get that in RDS nowadays) and logical replication. I suppose the latter can stave off the need for sharding for a while. I believe there's work happening for UDR/BDR right now that will be in the product by 9.5 or 9.6
  - tracker1 11y ago
    You can go a long way with read-only replication, as long as you have a good failover strategy for writes before you need sharding... The only time you really need sharding is once either your reads or writes require more than a single system in you cluster could keep up with in terms of requests. That said, using something like RethinkDB (not sql) from the start isn't a bad thing... And depending on your needs Cassandra, ElasticSearch and others are viable options... beyond that there's no need to stick with a single database for different types of data. It should be a case by case basis. As to the comments about Postgres replciation/failover, it's a pretty sad state imho for an otherwise very respectible database.
Mimick 11y ago
The most scaled websites are written on PHP/MySQL today. Facebook, Wikipedia, Tumblr, Wordpress, Baidu, Yahoo... I know it doesn't look cool to defend the popular technology, but it's hard to do for now. (Hope Rust will crush on web performance so I can hipster around about it :)
- kibwen 11y ago
  I have a friend at Wikipedia who was looking into writing some their PHP extensions in Rust, so hipster away. :P

by dpratt 11y ago

philliphaydon 11y ago

I don't know why anyone in 2015 would pick mysql when postgres is an option. Live postgres. So nice to work with. It's json support is amazing too

nchelluri 11y ago
You know what, about two weeks ago I was starting a new Rails hobby project, and I thought "all I've heard about for the past year is how everyone loves Postgres", and was about to throw it in there, when I saw a link to Google search trends comparing the two, and MySQL is still so far ahead: https://www.google.com/trends/explore#cmpt=q&q=mysql,+postgresql https://www.google.com/trends/explore#cmpt=q&q=mysql,+postgr... It's quite possible this is just due to people supporting existing applications, but I dunno, I am tempted to stick with MySQL for new stuff for the next little while.
- 0942v8653 11y ago
  It seems weird to me that Postgres and MySQL are both going down. I'd expect interest in Postgres to be rising at the very least—what are people switching to?
  - tracker1 11y ago
    Probably hosted SaaS offernings, some of which may actually be Postgres and MySQL... just there's less need for queries about operations issues. There's also a lot of NoSQL solutions that are being ever more widely used. Most of which have much lower costs for HA early on.
- steckerbrett 11y ago
  Is google trends really how you should be choosing your stack?
  - UnoriginalGuy 11y ago
    Choose? I'd argue no. But inform your choice? Sure. Looking at trends can help you: - See how easy hiring people will be in the future. - See the likelihood that your chosen framework will lose support. - See the likelihood of third party support/addons/libraries/etc. - How easy it will be to find help/tutorials/guides/etc. But looking at it, to me, seems prudent.
- clessg 11y ago
  MySQL is popular because it's easy to use (in the same sense as PHP), easy to setup, and has a lot of tutorials written for it. Also speed used to be a pretty big factor, but that was only because MyISAM took shortcuts with data consistency. If you care about your data and would rather not deal with the many problems MySQL inevitably brings, then Postgres is the better option. I can't say I've seen many compelling reasons to use MySQL over Postgres other than "what if we need to replace you with a freelance PHP guy", "I want to pay lower wages", and "I want to use phpmyadmin". (Btw: check out Sequel Pro and Querious!) Popularity isn't a very good reason to choose something. Postgres will remain a juggernaut for a long time. Unless you really think there's a good chance you'll need to hire somebody that only knows MySQL, then I'd go with Postgres.
  - tracker1 11y ago
    Given I haven't done anything with it in nearly 7 years now.. but every time I've touched MySQL for anything, there was always a quirky behavior that bit me in the ass that no other dbms does... Postgres, MS-SQL, Firebird, Oracle, DB2 are all better options in my mind... unless you need a good replication story, without a pricey support contract, then Postgres and Firebird are sadly out of the running imho.
  - spudlyo 11y ago
    That may be why MySQL is popular for small installations, but that's not why it's popular with companies like Facebook, Google, Twitter, and Pinterest who run replicated clusters that number in the thousands. It's popular at scale because unlike Postgres, it has replication that is well understood, is reliable, and isn't a massive pain in the ass to operate.
    - Jweb_Guru 11y ago
      Please point me to the well-understood, reliable replication solution for MySQL (I'll agree about pain in the ass to operate re: Postgres, but you might want to check out aphyr's recent posts).
- kawera 11y ago
  One of the reasons MySQL is still popular is Wordpress.
cageface 11y ago
Agreed. I consider MySQL a red flag and avoid any projects or teams that choose it over Postgres. Postgres is reliable, flexible, fast, and well documented. It's one of the crowning achievements of the open source world.
- UnoriginalGuy 11y ago
  > I consider MySQL a red flag and avoid any projects or teams that choose it over Postgres. I am in the "pro Postgres" camp, but this attitude is too black and white for me. For many projects Postgres in superior to MySQL, but there are situations and requirements where MySQL makes a lot more sense. For just one example, I can get MySQL as a service much cheaper and easier than I can get Postgres as a service. Which isn't to say Postgres as a service doesn't exist (it does), just to say MySQL is more readily available and there is more competition in that sector. Additional if I had a DBA who was a MySQL expert and didn't know a thing about Postgres, I'd go with MySQL for my project. Since a good DBA is worth their weight in gold, and an expert in a particular DBMS can be a massive asset to a project. But given no restrictions or needs, I'd pick Postgres as my "default" choice. I just don't think it is right to say you'd avoid a team/project just because they picked MySQL, sometimes there are completely legit reasons (or politics).
- zamalek 11y ago
  What I love about Postgres is how it's the only remaining innovator in the SQL space. It's like Microsoft and Oracle were circa 1999 (the last true innovation out of either was sparse columns in MSSQL). Genetic algorithms, the works. Everything else is mostly stagnant and archaic.
lqdc13 11y ago
Depends on your data. Sometimes (particularly when you have only 1-3 tables) MySQL is much faster and you have to go out of your way writing stored procedures to make Postgres compete. Example: select COUNT(1), status FROM some_table GROUP BY status ORDER BY status; -- Postgres 10x slower. Also, mysql CLI is IMO much nicer than the postgres version. Better and faster autocompletes.
- nimrody 11y ago
  Regarding CLI: mycli and pgcli are both much nicer than the built in alternatives.
- Jweb_Guru 11y ago
  I'd like to see your full setup for your claim about 10x slower, because I suspect you're doing something wrong.
  - lqdc13 11y ago
    Sure, here's the pastebin of the postgres one: http://pastebin.com/Phc8KnYU http://pastebin.com/Phc8KnYU I thought I was doing something wrong, so I asked on SO: http://stackoverflow.com/questions/32755348/group-by-count-query-takes-a-long-time http://stackoverflow.com/questions/32755348/group-by-count-q... This is a very simple example, but I always run into major performance issues with Postgres. Some of them are my fault, from not knowing that array aggregation is a slow operation. MySQL advantages for me are that there is an actual UPSERT operation (an inferior version of which might be added to Postgres 9.5), that the CLI is miles ahead, and that it's much faster for a small number of tables with a lot of data and with non-complex operations.
    - Jweb_Guru 11y ago
      I'm going to ignore your baseless (and incorrect) assertion about "an inferior version" of UPSERT (MySQL's doesn't work properly except for the primary key). Your problem is that you are not doing an index only scan. The reason you are not doing an index only scan is, most likely, that the visibility bit is not set on the table. This is usually as a result of excessive writing to the table, but sometimes it is due to incorrectly tuned planner settings. Try setting your cpu_tuple_cost higher and/or cpu_index_tuple_cost lower. I have had to do this in the past to get index only scans to work. BTW, a quick way to force the issue is to SET enable_seq_scan TO off in psql. I would add, you should really upgrade to 9.4, as there were some performance improvements to aggregates.

brobinson 11y ago

Good article on the dangers and time-wasters of MySQL: http://grimoire.ca/mysql/choose-something-else http://grimoire.ca/mysql/choose-something-else

tracker1 11y ago

mySQL has more baked in solutions for replication that don't require a support contract or a convoluted mess of choosing between half a dozen half baked options for higher availability? Honestly, it's the one area that I feel that open-source PostgreSQL is really lacking, and that is a good replication/automagic-failover story. I think MS-SQL is probably the best (but almost as expensive as EnterpriseDB support contract) for the supported versions. MySQL is probably the best option for a lot of people. That said, I think RethinkDB is a pretty decent all-around option for most workloads at this point.

akurilin 11y ago
As someone who took a stab at automating slony deployment and configuration, I have to agree that the replication situation is not quite there yet. There are maybe 2-3 examples online on how to use some of those tools, and maybe half a dozen people who actually would be able to answer questions about them. Betting your business's scaling future on that kind of tooling is terrifying. Would be great to have both out of the box failover (you can get that in RDS nowadays) and logical replication. I suppose the latter can stave off the need for sharding for a while. I believe there's work happening for UDR/BDR right now that will be in the product by 9.5 or 9.6
- tracker1 11y ago
  You can go a long way with read-only replication, as long as you have a good failover strategy for writes before you need sharding... The only time you really need sharding is once either your reads or writes require more than a single system in you cluster could keep up with in terms of requests. That said, using something like RethinkDB (not sql) from the start isn't a bad thing... And depending on your needs Cassandra, ElasticSearch and others are viable options... beyond that there's no need to stick with a single database for different types of data. It should be a case by case basis. As to the comments about Postgres replciation/failover, it's a pretty sad state imho for an otherwise very respectible database.

Mimick 11y ago

The most scaled websites are written on PHP/MySQL today. Facebook, Wikipedia, Tumblr, Wordpress, Baidu, Yahoo... I know it doesn't look cool to defend the popular technology, but it's hard to do for now. (Hope Rust will crush on web performance so I can hipster around about it :)

kibwen 11y ago
I have a friend at Wikipedia who was looking into writing some their PHP extensions in Rust, so hipster away. :P