9 ms·

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

by 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.)
      - neilc 17y ago
        I wouldn't say it is "obviously" a problem: READ COMMITTED are perfectly reasonable semantics, and are preferable to many applications (e.g. many apps aren't written to retry transactions that abort due to serialization conflicts). Which isolation level ought to be the default is a matter of debate, but I don't think using RC by default is "obviously" wrong -- if an application depends on a particular isolation level for correctness, it should set it explicitly in any case.
pilif 17y ago
1) hard to install? What about `apt-get install postgresql` or the usual ./configure-stuff is hard? Also, EnterpriseDB provides pre-built one-click installers for Windows and Mac OS X. Upgrading between major releases could be painful as it requires a dump/restore, but lately, we got pg_migrator which takes care of that (and pg 8.4's parallel restore helps in cases where pg_migrator fails - not that I know of any) 2) the .org domain is hosted with PostgreSQL. Scalability-wise, that looks good enough for me. 3) Kind of agreed, but then again, the documentation of PostgreSQL really rocks. In my 8 years of (near-fanatic) use of PostgreSQL, I never came across a problem the docs could not solve for me. 4) One major release per year might actually be quicker than what MySQL does. I don't know about related tools, because I don't generally have need for any. There's PGAdmin III which, AFAIK is updated as often as PostgeSQL itself. 5) You don't need the advanced features, until you use them and then you can't live without them. It's like addiction to drugs :-) - I moved from MySQL to Postgres back in the days (end of 2001) for a large project after spending hours and hours of hair-pulling due to the lack of subqueries and views in MySQL and since then, I never wanted to go back. We're using PostgreSQL as the backend database for multiple large e-commerce applications. Some tables are over 20G in size (1.5 billion rows in one of them). PostgreSQL is easily handling ~60 queries per second on a quite dated dual core box with 4 GB of RAM. You might have had a lot of success with MySQL. I on the other hand had a lot of it with PostgreSQL. I don't know about the later versions of MySQL but when I moved to PostgreSQL, it was years ahead of MySQL feature-wise and as I never had any performance or scalability issues, I never felt the need to go back or even see if MySQL has caught up already. Just wanted to add my two cents here.
- sandGorgon 17y ago
  Just wanted to point out a few differences to people hitting PGSQL after getting their feet wet with MySQL. 1. While installing PGSQL get your configuration variables correct, or it wont work out-of-the-box (kernel.shmmax, autovacuum, shared_buffers) 2. learn about authentication. PGSQL depends on authentication of the OS ("sameuser" on pg_hba.conf). MySQL behaves by default as the "password" authentication method of PGSQL. 3. Learn about template databases. Your first database is a template database. 4. PGSQL has "pg_" databases and slash commands (\dt, \connect, \q) for system administration.
jherdman 17y ago
> 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) You might be interested to learn that Yahoo runs a modified version of Postgres that is column-oriented http://glinden.blogspot.com/2008/05/yahoo-builds-two-petabyte-postgresql.html http://glinden.blogspot.com/2008/05/yahoo-builds-two-petabyt...
- xzilla 17y ago
  Facebook also uses Postgres and one of the nosql variants (hadoop iirc). Also MySpace uses Aster Data's stuff, which is all built on top of Postgres. shrug
  - easp 17y ago
    I hadn't heard that Facebook had any major Postgres deployment. MySQL seems to form the core data storage for the appplication. Cassandra looks like something they will be using more and more of. They use their own Hive project to allow use of SQL to analyze data in Hadoop. As for other big users of Postgres, Hi5 is a decent sized social net that uses Postgres. NTT uses postgres, and I think the coming simple replication and hot-standby features are based on code they developed for in-house use.
russss 17y ago
> 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) We use Postgres (as well as MySQL) extensively at Last.fm, including on our terabyte+ main database handling many thousands of transactions per second. Except for the lack of decent replication, I have nothing bad to say about it. At a large scale, it's as easy as MySQL to tune (if not easier).
- mahmud 17y ago
  How do you overcome the lack of replication?
  - russss 17y ago
    We use Slony to replicate read-only slaves for portions of our database (I wouldn't recommend it, Londiste is probably better), and Postgres warm standby for HA.
Nelson69 17y ago
Isn't Greenplum based upon Postgresql? They claim a fairly impressive customer list. In my experience, it's a major type pain once you realize MySQL doesn't do stuff that you need. The other differences are pretty marginal (save for replication maturity) but when you need some more complicated queries and don't have them, you're in a bind and you're double screwed if you've actually embraced some of the MySQL niceties. (It's not the end of the world but it can be a deceptively large task to migrate and your customers don't see anything for it) On the down side, what I'm surprised nobody as mentioned is Postgresql's ever changing on disk format and the process of actually backing it up and restoring it. Just backing up the on disk files without a dump can often result in trouble. Dumping can be a heavy task too. It should be a rare thing to upgrade your database in production but Postgresql does add performance and some nice features on a regular basis.
joevandyk 17y ago
How is postgresql hard to install and manage? Postgresql is used in some absolutely huge places. I know of some that are terabytes in size. There's tons of support options for postgresql, not sure what you are talking about. I'm guessing you don't really know much about postgresql.
mmt 17y ago
Having dealt with many MySQL installations and, finally, a nice, internet-scale Postgres site, I can quite comfortably challenge the notion that MySQL has "great" replication. It's certainly tough to beat for trivial topology or trivial database size, but, beyond that, MySQL statement-based replication is remarkably fragile. What's worse is, if a slave is far enough behind, it's possible that it will never catch up (hint: think about I/O contention with replication and whether it's possible to separate logs adequately). Once that happens, it's full dump time. Anything remotely fancy, like filtering out certain schemas (which MySQL calls "database," but it's really just a namespace), and a single developer discovering cross-schema UPDATEs, and That's All She Wrote. With Slony, after learning the admittedly bizarre administrative interface, everything was a dream. Swapping master and slave is fast and easy, even if there are other slaves out there. Full dump? Never heard of it! Certainly, slony does do the equivalent when bringing up a new slave, but the locking requirement is nowhere near as onerous, nor is the overall performance hit. Replication happens at the table level, not the whole database. The amount of time I've spent fixing broken MySQL replication easily dwarfs the amount of time I've spent learning and experimenting and fixing (after breaking on purpose, usually) Slony.

by amix 17y ago

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.)
    - neilc 17y ago
      I wouldn't say it is "obviously" a problem: READ COMMITTED are perfectly reasonable semantics, and are preferable to many applications (e.g. many apps aren't written to retry transactions that abort due to serialization conflicts). Which isolation level ought to be the default is a matter of debate, but I don't think using RC by default is "obviously" wrong -- if an application depends on a particular isolation level for correctness, it should set it explicitly in any case.

pilif 17y ago

1) hard to install? What about `apt-get install postgresql` or the usual ./configure-stuff is hard? Also, EnterpriseDB provides pre-built one-click installers for Windows and Mac OS X. Upgrading between major releases could be painful as it requires a dump/restore, but lately, we got pg_migrator which takes care of that (and pg 8.4's parallel restore helps in cases where pg_migrator fails - not that I know of any) 2) the .org domain is hosted with PostgreSQL. Scalability-wise, that looks good enough for me. 3) Kind of agreed, but then again, the documentation of PostgreSQL really rocks. In my 8 years of (near-fanatic) use of PostgreSQL, I never came across a problem the docs could not solve for me. 4) One major release per year might actually be quicker than what MySQL does. I don't know about related tools, because I don't generally have need for any. There's PGAdmin III which, AFAIK is updated as often as PostgeSQL itself. 5) You don't need the advanced features, until you use them and then you can't live without them. It's like addiction to drugs :-) - I moved from MySQL to Postgres back in the days (end of 2001) for a large project after spending hours and hours of hair-pulling due to the lack of subqueries and views in MySQL and since then, I never wanted to go back. We're using PostgreSQL as the backend database for multiple large e-commerce applications. Some tables are over 20G in size (1.5 billion rows in one of them). PostgreSQL is easily handling ~60 queries per second on a quite dated dual core box with 4 GB of RAM. You might have had a lot of success with MySQL. I on the other hand had a lot of it with PostgreSQL. I don't know about the later versions of MySQL but when I moved to PostgreSQL, it was years ahead of MySQL feature-wise and as I never had any performance or scalability issues, I never felt the need to go back or even see if MySQL has caught up already. Just wanted to add my two cents here.

sandGorgon 17y ago
Just wanted to point out a few differences to people hitting PGSQL after getting their feet wet with MySQL. 1. While installing PGSQL get your configuration variables correct, or it wont work out-of-the-box (kernel.shmmax, autovacuum, shared_buffers) 2. learn about authentication. PGSQL depends on authentication of the OS ("sameuser" on pg_hba.conf). MySQL behaves by default as the "password" authentication method of PGSQL. 3. Learn about template databases. Your first database is a template database. 4. PGSQL has "pg_" databases and slash commands (\dt, \connect, \q) for system administration.

jherdman 17y ago

> 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) You might be interested to learn that Yahoo runs a modified version of Postgres that is column-oriented http://glinden.blogspot.com/2008/05/yahoo-builds-two-petabyte-postgresql.html http://glinden.blogspot.com/2008/05/yahoo-builds-two-petabyt...

xzilla 17y ago
Facebook also uses Postgres and one of the nosql variants (hadoop iirc). Also MySpace uses Aster Data's stuff, which is all built on top of Postgres. shrug
- easp 17y ago
  I hadn't heard that Facebook had any major Postgres deployment. MySQL seems to form the core data storage for the appplication. Cassandra looks like something they will be using more and more of. They use their own Hive project to allow use of SQL to analyze data in Hadoop. As for other big users of Postgres, Hi5 is a decent sized social net that uses Postgres. NTT uses postgres, and I think the coming simple replication and hot-standby features are based on code they developed for in-house use.

russss 17y ago

> 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) We use Postgres (as well as MySQL) extensively at Last.fm, including on our terabyte+ main database handling many thousands of transactions per second. Except for the lack of decent replication, I have nothing bad to say about it. At a large scale, it's as easy as MySQL to tune (if not easier).

mahmud 17y ago
How do you overcome the lack of replication?
- russss 17y ago
  We use Slony to replicate read-only slaves for portions of our database (I wouldn't recommend it, Londiste is probably better), and Postgres warm standby for HA.

Nelson69 17y ago

Isn't Greenplum based upon Postgresql? They claim a fairly impressive customer list. In my experience, it's a major type pain once you realize MySQL doesn't do stuff that you need. The other differences are pretty marginal (save for replication maturity) but when you need some more complicated queries and don't have them, you're in a bind and you're double screwed if you've actually embraced some of the MySQL niceties. (It's not the end of the world but it can be a deceptively large task to migrate and your customers don't see anything for it) On the down side, what I'm surprised nobody as mentioned is Postgresql's ever changing on disk format and the process of actually backing it up and restoring it. Just backing up the on disk files without a dump can often result in trouble. Dumping can be a heavy task too. It should be a rare thing to upgrade your database in production but Postgresql does add performance and some nice features on a regular basis.

joevandyk 17y ago

How is postgresql hard to install and manage? Postgresql is used in some absolutely huge places. I know of some that are terabytes in size. There's tons of support options for postgresql, not sure what you are talking about. I'm guessing you don't really know much about postgresql.

mmt 17y ago

Having dealt with many MySQL installations and, finally, a nice, internet-scale Postgres site, I can quite comfortably challenge the notion that MySQL has "great" replication. It's certainly tough to beat for trivial topology or trivial database size, but, beyond that, MySQL statement-based replication is remarkably fragile. What's worse is, if a slave is far enough behind, it's possible that it will never catch up (hint: think about I/O contention with replication and whether it's possible to separate logs adequately). Once that happens, it's full dump time. Anything remotely fancy, like filtering out certain schemas (which MySQL calls "database," but it's really just a namespace), and a single developer discovering cross-schema UPDATEs, and That's All She Wrote. With Slony, after learning the admittedly bizarre administrative interface, everything was a dream. Swapping master and slave is fast and easy, even if there are other slaves out there. Full dump? Never heard of it! Certainly, slony does do the equivalent when bringing up a new slave, but the locking requirement is nowhere near as onerous, nor is the overall performance hit. Replication happens at the table level, not the whole database. The amount of time I've spent fixing broken MySQL replication easily dwarfs the amount of time I've spent learning and experimenting and fixing (after breaking on purpose, usually) Slony.