7 ms·

MySQL vs. PostgreSQL comparison by Anchor (github's migration team)

44 points by adriand 17y ago

JustAGeek 17y ago
That is a very, very superficial comparison. Half of the contras for MySql (the ones referring to MyIsam) don't really apply, since you simply use InnoDB if you need those features. So it's no real help in deciding which DB to pick...
- jbellis 17y ago
  But then a lot of the pro- points go away, too. It's almost like it's two different pieces of software.
  - vidarh 17y ago
    True to a point, but MySQL lets you pick and match on a per-table basis. I've used MySQL extensively, and use Postgres for most things now, and I prefer MySQL for many things. Not least replication, which is so much simpler with MySQL (I'd love to be proved wrong on this - please someone point me to a replication solution for Postgres that is as simple as for MySQL). But the differences aren't great enough for most typical use that I'd worry too much about which one to use. Currently I use Postgres because that is what my team is familiar with.
amix 17y ago
It's a pretty crappy comparison. If you need transactions and ACID properties then you pick InnoDB and not MyISAM. Now what are the cons of PostgreSQL? 1) It's hard to install and manage, which could explain why Postgre's adoption is fairly low. 2) MySQL is used and proven to scale on very large installments (Yahoo, Facebook, Google - just to name a few). Postgre's usage is much lower (Skype and Reddit from those I know of) 3) Due to the adoption of MySQL it's very easy to get help and to buy help. For example: http://mysqlperformanceblog.com http://mysqlperformanceblog.com offers great advice on everything MySQL. Due to Postgre's smaller user base it's harder to get or buy help. 4) The development of "MySQL and MySQL tools" is very rapid. Check out Drizzle, Xtradb, xtrabackup, innodb plugin, rethinkdb.com (this is just a few, there are tons of other engines and tools under development). It's unclear to me how rapid Postgre's development is (for example, they still lack a decent replication), but I doubt Postgre's development is as rapid as MySQL's. 5) The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I.e. Postgres has a lot of non-essential features, while it lacks some essential features (such as decent replication). The bottom line is that MySQL is proven, easy to use and install, is under heavy development, is backed up by billion dollar companies and has a very large user base. MySQL is also built mostly for web-applications and offers _great_ performance, great tools and great replication.
- davidw 17y ago
  > The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I am not so sure. Many people just start with Mysql (with MyIsam) and realize that they have a pile of transactionless FAIL on their hands only at some later date, without really going through a selection process. Regarding the performance, has anyone actually done an apples-to-apples benchmark lately? Say PG 8.3 vs Mysql with InnoDB? In any case, this particular comparison is not really worthwhile... it's full of hyperbole: "blazing fast", "rocks", "aeons" and so on. Doesn't sound all that professional.
  - jrockway 17y ago
    Indeed. I have seen many "transactional" web applications that lose data because of MySQL's policy of handling errors by ignoring them, among other problems. (0000-00-00 as a date is just the tip of the iceberg.) I even know of self-proclaimed "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. (MySQL has the same problem, of course, except you can't even fix MySQL with a config setting.)
    - davidw 17y ago
      > "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. Explain further. I'm not a Postgres 'expert', just a happy user, and I'd love to actually learn something from a silly X vs Y thread.
      - jrockway 17y ago
        Postgres defaults to "read committed" isolation, rather than repeatable read or serializable isolation. I have mostly noticed the difference when dealing with updating HTTP session objects in a transaction; with "read commited" isolation, concurrent requests can overwrite newly-written data. With serializable isolation, the transaction that tries to overwrite new data with older data (read at the beginning of the request) is aborted. (The fix for this is difficult, but I basically don't use the session for much transient data anymore, so this became less of an issue. Before setting up the database to be strict, I never even thought of this as a problem. But obviously it is.)
dylanz 17y ago
MySQL Con: Owned by Oracle
- dylanz 17y ago
  If you need more background, Oracle "still" hasn't made any press releases as to the fate of MySQL. This should be a very big "con", with a huge question mark next to it. Many core MySQL developers aren't being too optimistic about the future.
  - davidw 17y ago
    Well, it is open source, but there's definitely a question mark as to how many people will be paid to work on it, and what direction they'll be taking it.
  - JulianMorrison 17y ago
    Oracle can't do much except at worst, drag a stifling cloud over its future for as long as possible. If they actually stop or stall it, some fork will grab its momentum and run.
  - spudlyo 17y ago
    Oracle has owned the important parts of MySQL (InnoDB/InnoBASE) for a few years now, and that hasn't stopped them from fixing bugs and adding features in InnoDB.
    - ams6110 17y ago
      They've owned BerkeleyDB for a while also, and AFAIK they have been good stewards.
pilif 17y ago
The absolutely worst thing about MySQL is its lack of strictness. Maybe this has changed, but I was furious when I, by accident, inserted latin1 data into an UTF-8 table. Instead of complaining about invalid input data, MySQL accepted the data and truncated it at the first invalid byte. Without error or warning. Even if this was due to a misconfiguration on my part, behavior like this is completely unacceptable. http://www.gnegg.ch/2008/02/failing-silently-is-bad/ http://www.gnegg.ch/2008/02/failing-silently-is-bad/ and don't get me started on other comparable issues like truncating overlong texts added to varchar fields, or accepting invalid dates and silently converting them to 0000-00-00 shudder
- deleted 17y ago
  [deleted]
- ilyak 17y ago
  http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-faq.html http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-fa... Most of questions here are lack-of-strictness-related.
tzury 17y ago
Well, let us put it straight! GitHub uses MySQL. In fact, these guys maybe helped install the new GitHub at Rackspace's data center, but their choices are irrelevant to GitHub choice of platform (the title is bit misleading though). AS for the subject itself, I guess, if you are more comfort with either one of them, this one would be the best choice for you. If you never used anyone of them and wish to start a new project which requires an RDBMS, you may search for other comparisons at Google (as well as look at here: http://news.ycombinator.com/item?id=184328 http://news.ycombinator.com/item?id=184328) Most important, no matter what language you are using, make sure to use a database abstraction library, so to make it easy to switch between them in case you are not satisfied with your first choice.
rburhum 17y ago
How about spatial support? Only PostgreSQL has support for spatial types (as opposed to those GIS API skeletons that MySQL has).
technomancy 17y ago
I didn't realize MVCC was only implemented in Postgres. That's a huge advantage if you're doing anything other than a mostly-read DB.
- CrLf 17y ago
  If I'm not mistaken (I'm more of a DB2 guy), Oracle implements MVCC too.
  - masklinn 17y ago
    It does, since version 7. MySQL also does under InnoDB or Falcon. Other DB with MVCC are BDB, Firebird, InterBase, SQL Server, Sybase, ZODB, CouchDB or ObjectStore.
sam1976 17y ago
I was happy with MySQL except Master-slave configuration. Now that Oravle has aquired MySQL I am looking for another open source DB and Postgres seems a perfect fit.

44 points by adriand 17y ago

That is a very, very superficial comparison. Half of the contras for MySql (the ones referring to MyIsam) don't really apply, since you simply use InnoDB if you need those features. So it's no real help in deciding which DB to pick...

jbellis 17y ago
But then a lot of the pro- points go away, too. It's almost like it's two different pieces of software.
- vidarh 17y ago
  True to a point, but MySQL lets you pick and match on a per-table basis. I've used MySQL extensively, and use Postgres for most things now, and I prefer MySQL for many things. Not least replication, which is so much simpler with MySQL (I'd love to be proved wrong on this - please someone point me to a replication solution for Postgres that is as simple as for MySQL). But the differences aren't great enough for most typical use that I'd worry too much about which one to use. Currently I use Postgres because that is what my team is familiar with.

amix 17y ago

It's a pretty crappy comparison. If you need transactions and ACID properties then you pick InnoDB and not MyISAM. Now what are the cons of PostgreSQL? 1) It's hard to install and manage, which could explain why Postgre's adoption is fairly low. 2) MySQL is used and proven to scale on very large installments (Yahoo, Facebook, Google - just to name a few). Postgre's usage is much lower (Skype and Reddit from those I know of) 3) Due to the adoption of MySQL it's very easy to get help and to buy help. For example: http://mysqlperformanceblog.com http://mysqlperformanceblog.com offers great advice on everything MySQL. Due to Postgre's smaller user base it's harder to get or buy help. 4) The development of "MySQL and MySQL tools" is very rapid. Check out Drizzle, Xtradb, xtrabackup, innodb plugin, rethinkdb.com (this is just a few, there are tons of other engines and tools under development). It's unclear to me how rapid Postgre's development is (for example, they still lack a decent replication), but I doubt Postgre's development is as rapid as MySQL's. 5) The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I.e. Postgres has a lot of non-essential features, while it lacks some essential features (such as decent replication). The bottom line is that MySQL is proven, easy to use and install, is under heavy development, is backed up by billion dollar companies and has a very large user base. MySQL is also built mostly for web-applications and offers _great_ performance, great tools and great replication.

davidw 17y ago
> The advances features of Postgres are not that needed in most web applications. If they were that needed, more people would have used Postgres. I am not so sure. Many people just start with Mysql (with MyIsam) and realize that they have a pile of transactionless FAIL on their hands only at some later date, without really going through a selection process. Regarding the performance, has anyone actually done an apples-to-apples benchmark lately? Say PG 8.3 vs Mysql with InnoDB? In any case, this particular comparison is not really worthwhile... it's full of hyperbole: "blazing fast", "rocks", "aeons" and so on. Doesn't sound all that professional.
- jrockway 17y ago
  Indeed. I have seen many "transactional" web applications that lose data because of MySQL's policy of handling errors by ignoring them, among other problems. (0000-00-00 as a date is just the tip of the iceberg.) I even know of self-proclaimed "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. (MySQL has the same problem, of course, except you can't even fix MySQL with a config setting.)
  - davidw 17y ago
    > "Postgres experts" whose applications lose data with Postgres due to its very "liberal" default isolation level. Explain further. I'm not a Postgres 'expert', just a happy user, and I'd love to actually learn something from a silly X vs Y thread.
    - jrockway 17y ago
      Postgres defaults to "read committed" isolation, rather than repeatable read or serializable isolation. I have mostly noticed the difference when dealing with updating HTTP session objects in a transaction; with "read commited" isolation, concurrent requests can overwrite newly-written data. With serializable isolation, the transaction that tries to overwrite new data with older data (read at the beginning of the request) is aborted. (The fix for this is difficult, but I basically don't use the session for much transient data anymore, so this became less of an issue. Before setting up the database to be strict, I never even thought of this as a problem. But obviously it is.)

dylanz 17y ago

MySQL Con: Owned by Oracle

dylanz 17y ago
If you need more background, Oracle "still" hasn't made any press releases as to the fate of MySQL. This should be a very big "con", with a huge question mark next to it. Many core MySQL developers aren't being too optimistic about the future.
- davidw 17y ago
  Well, it is open source, but there's definitely a question mark as to how many people will be paid to work on it, and what direction they'll be taking it.
- JulianMorrison 17y ago
  Oracle can't do much except at worst, drag a stifling cloud over its future for as long as possible. If they actually stop or stall it, some fork will grab its momentum and run.
- spudlyo 17y ago
  Oracle has owned the important parts of MySQL (InnoDB/InnoBASE) for a few years now, and that hasn't stopped them from fixing bugs and adding features in InnoDB.
  - ams6110 17y ago
    They've owned BerkeleyDB for a while also, and AFAIK they have been good stewards.

pilif 17y ago

The absolutely worst thing about MySQL is its lack of strictness. Maybe this has changed, but I was furious when I, by accident, inserted latin1 data into an UTF-8 table. Instead of complaining about invalid input data, MySQL accepted the data and truncated it at the first invalid byte. Without error or warning. Even if this was due to a misconfiguration on my part, behavior like this is completely unacceptable. http://www.gnegg.ch/2008/02/failing-silently-is-bad/ http://www.gnegg.ch/2008/02/failing-silently-is-bad/ and don't get me started on other comparable issues like truncating overlong texts added to varchar fields, or accepting invalid dates and silently converting them to 0000-00-00 shudder

deleted 17y ago
[deleted]
ilyak 17y ago
http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-faq.html http://sqlanywhere.blogspot.com/2008/03/unpublished-mysql-fa... Most of questions here are lack-of-strictness-related.

tzury 17y ago

Well, let us put it straight! GitHub uses MySQL. In fact, these guys maybe helped install the new GitHub at Rackspace's data center, but their choices are irrelevant to GitHub choice of platform (the title is bit misleading though). AS for the subject itself, I guess, if you are more comfort with either one of them, this one would be the best choice for you. If you never used anyone of them and wish to start a new project which requires an RDBMS, you may search for other comparisons at Google (as well as look at here: http://news.ycombinator.com/item?id=184328 http://news.ycombinator.com/item?id=184328) Most important, no matter what language you are using, make sure to use a database abstraction library, so to make it easy to switch between them in case you are not satisfied with your first choice.

rburhum 17y ago

How about spatial support? Only PostgreSQL has support for spatial types (as opposed to those GIS API skeletons that MySQL has).

technomancy 17y ago

I didn't realize MVCC was only implemented in Postgres. That's a huge advantage if you're doing anything other than a mostly-read DB.

CrLf 17y ago
If I'm not mistaken (I'm more of a DB2 guy), Oracle implements MVCC too.
- masklinn 17y ago
  It does, since version 7. MySQL also does under InnoDB or Falcon. Other DB with MVCC are BDB, Firebird, InterBase, SQL Server, Sybase, ZODB, CouchDB or ObjectStore.

sam1976 17y ago

I was happy with MySQL except Master-slave configuration. Now that Oravle has aquired MySQL I am looking for another open source DB and Postgres seems a perfect fit.