3 ms·

What I haven't seen recently is an explanation of "Reasons I like Postgres Over Mysql." It seems like after a long period of stagnation, postgres suddenly becam

What I haven't seen recently is an explanation of "Reasons I like Postgres Over Mysql." It seems like after a long period of stagnation, postgres suddenly became hugely popular among startups again in spite of some of the things that I consider its core failings not being fixed (mainly: an even more expensive per-connection model than mysql and extremely painful replication, largely offloaded to third party tools). Is it because of HStore? Heroku using it? The Oracle buyout of Inno and then Mysql? Mysql 3.x was quite awful (other than replication), but by 5.x it had started getting good, and now the forks are doing some fantastic work. So it surprises me.

lobster_johnson 14y ago
By "a long period of stagnation" I assume you are referring to public exposure -- because it's not true that Postgres itself has stagnated. Postgres has been developed steadily the last decade, and has been a serious competitor to major RDBMSes since version 8.1 (which added two-phase commit, table partitioning, index bitmap scan and shared row locking, among other things.) I say public exposure because Postgres has clearly been popular among many developers for a long time It's ben beneath the radar for a lot of people, partly due to pervasive myths ("Postgres is slow"), partly due to the fact that, well, a product doesn't sell itself. I think the reason Postgres has shown up on HN a lot is simply that people are finally discovering it and recognizing its technical brilliance, careful engineering and high performance, and it's snowballing. I myself have used Postgres for web apps since 2003 myself, and I have evangelized among colleagues and acquaintances to the best of my abilities, so I am very happy about this progress. I don't see why it should surprise you. While I'm sure MySQL 5.0 and its various forks have gotten better over the years, it's hard to argue that Postgres is not technically superior in pretty much every way. Even if you care solely about performance, which used to be MySQL's main selling point, Postgres has overtaken MySQL a long time ago. > an even more expensive per-connection model than mysql This is not a practical issue for most people, in my experience. At least nobody is really complaining about it. Rails apps happily pool connections, for example. If you can't pool connections in the app, there are several excellent, mature connection-pooling proxies around (including a very cool one, pgPool-II, which can also load-balance and replicate at the same time). > and extremely painful replication Is it possible that you have not been following the developments? If you don't need per-table/per-database replication, then streaming replication is actually incredibly painless. It is by all accounts awesome. And it scales very well. If you do need fine-grained replication, there are several mature third-party projects that are fairly painless to operate, including Bucardo and Londiste. (I would not count Slony as "painless", although it's probably the most comprehensive solution right now. Avoid it unless you are a bank.) There is also a multimaster-replicating fork (PostgreSQL-XC) that is apparently working very well, and implements true distributed ACID replication, as opposed to the kind of "de facto multimaster replication" you set up in MySQL, where you have to make sure primary keys don't overlap between hosts.
eddieroger 14y ago
Heroku integration did it for me, doubly so when they released Postgres.app for Mac. It's incredibly appealing to have the same database on both ends. MySQL isn't hard to install by any means, but Postgres has become dead simple thanks to the work of Heroku.
__david__ 14y ago
My own personal anecdotal evidence is that mysql is fine, until you have complicated queries or a lot of data. And then it suddenly sucks. We had a huge problem where the query planner refused to use the indexes we had unless we manually put index hints in the query (yuck). Rather than actually doing that, we migrated all our data to postgresql. The postgres query planner works much better than the mysql one. Since then we've started using some of the postgres specific features, like windowing, which have made complex queries that we didn't think were going to be possible not only doable, but easy and fast (running). Postgres has some different philosophy about certain things in the devops side of things, but if you don't try to force it to be like mysql it'll be fine. At this point I wouldn't ever use mysql again (for new projects where I have the choice).
rpedela 14y ago
Postgres is primarily designed around data integrity. Therefore it does the "right" thing for certain data types. For example, it stores dates and times in ISO 8601 format and converts to UTC. The arbitrary-precision decimal type has a ridiculously high number of maximum digits and decimal places. It is so high that only a very small number of applications would exceed the precision. This is the main reason we are switching from MySQL to Postgres.
wisty 14y ago
http://www.postgresql.org/about/featurematrix/#performance http://www.postgresql.org/about/featurematrix/#performance IIRC, at 8.3, Postgress got an order of magnitude bump in speed. As MySQL gains credibility as a "real" db, Postgres is making itself fast.
jeffdavis 14y ago
"an even more expensive per-connection model than mysql" Did you actually run into a practical problem here, or is this theoretical? Most people get along fine for a while, and when they have a problem, they use pgbouncer, which seems to solve it. "extremely painful replication, largely offloaded to third party tools" Do you have some more specific/constructive criticism here? A lot of work has gone into postgres replication over the past few years, and it's improved dramatically for some use cases. But there are many use cases for replication, so it's possible that you didn't see improvements for your use cases. FWIW, I started out with MySQL, and then tried out Postgres because of the license (MySQL was not yet GPL). After MySQL was GPLed, I used both for a while, but found the postgres community more welcoming/informative, and found postgres much more pleasant to use. At one point around 2003 I basically threw mysql away completely because I was fed up trying to do simple date manipulation to make simple reports. Postgres made it so easy and it seemed so natural -- it's really hard to use mysql afterward without getting frustrated. I'm sure there are things like that in reverse as well; e.g. mysql has the ON DUPLICATE KEY clause, but to me it still didn't add up to a pleasant/productive experience.
AlisdairO 14y ago
It's because, fundamentally, it's better for most stuff. It has transactional DDL, check constraints, window functions, CDEs, joins other than nested loop, and a better focus on safe-by-default behaviour. It also has a query optimiser that is generally more competent on difficult queries. The per-connection cost isn't much of an issue with a connection pool, and replication has improved a lot lately (and is clearly only going to get better). Aside from ease of getting up and running, IMO there's very little reason to pick MySQL over Postgres nowadays.

lobster_johnson 14y ago

By "a long period of stagnation" I assume you are referring to public exposure -- because it's not true that Postgres itself has stagnated. Postgres has been developed steadily the last decade, and has been a serious competitor to major RDBMSes since version 8.1 (which added two-phase commit, table partitioning, index bitmap scan and shared row locking, among other things.) I say public exposure because Postgres has clearly been popular among many developers for a long time It's ben beneath the radar for a lot of people, partly due to pervasive myths ("Postgres is slow"), partly due to the fact that, well, a product doesn't sell itself. I think the reason Postgres has shown up on HN a lot is simply that people are finally discovering it and recognizing its technical brilliance, careful engineering and high performance, and it's snowballing. I myself have used Postgres for web apps since 2003 myself, and I have evangelized among colleagues and acquaintances to the best of my abilities, so I am very happy about this progress. I don't see why it should surprise you. While I'm sure MySQL 5.0 and its various forks have gotten better over the years, it's hard to argue that Postgres is not technically superior in pretty much every way. Even if you care solely about performance, which used to be MySQL's main selling point, Postgres has overtaken MySQL a long time ago. > an even more expensive per-connection model than mysql This is not a practical issue for most people, in my experience. At least nobody is really complaining about it. Rails apps happily pool connections, for example. If you can't pool connections in the app, there are several excellent, mature connection-pooling proxies around (including a very cool one, pgPool-II, which can also load-balance and replicate at the same time). > and extremely painful replication Is it possible that you have not been following the developments? If you don't need per-table/per-database replication, then streaming replication is actually incredibly painless. It is by all accounts awesome. And it scales very well. If you do need fine-grained replication, there are several mature third-party projects that are fairly painless to operate, including Bucardo and Londiste. (I would not count Slony as "painless", although it's probably the most comprehensive solution right now. Avoid it unless you are a bank.) There is also a multimaster-replicating fork (PostgreSQL-XC) that is apparently working very well, and implements true distributed ACID replication, as opposed to the kind of "de facto multimaster replication" you set up in MySQL, where you have to make sure primary keys don't overlap between hosts.

eddieroger 14y ago

Heroku integration did it for me, doubly so when they released Postgres.app for Mac. It's incredibly appealing to have the same database on both ends. MySQL isn't hard to install by any means, but Postgres has become dead simple thanks to the work of Heroku.

__david__ 14y ago

My own personal anecdotal evidence is that mysql is fine, until you have complicated queries or a lot of data. And then it suddenly sucks. We had a huge problem where the query planner refused to use the indexes we had unless we manually put index hints in the query (yuck). Rather than actually doing that, we migrated all our data to postgresql. The postgres query planner works much better than the mysql one. Since then we've started using some of the postgres specific features, like windowing, which have made complex queries that we didn't think were going to be possible not only doable, but easy and fast (running). Postgres has some different philosophy about certain things in the devops side of things, but if you don't try to force it to be like mysql it'll be fine. At this point I wouldn't ever use mysql again (for new projects where I have the choice).

rpedela 14y ago

Postgres is primarily designed around data integrity. Therefore it does the "right" thing for certain data types. For example, it stores dates and times in ISO 8601 format and converts to UTC. The arbitrary-precision decimal type has a ridiculously high number of maximum digits and decimal places. It is so high that only a very small number of applications would exceed the precision. This is the main reason we are switching from MySQL to Postgres.

wisty 14y ago

http://www.postgresql.org/about/featurematrix/#performance http://www.postgresql.org/about/featurematrix/#performance IIRC, at 8.3, Postgress got an order of magnitude bump in speed. As MySQL gains credibility as a "real" db, Postgres is making itself fast.

jeffdavis 14y ago

"an even more expensive per-connection model than mysql" Did you actually run into a practical problem here, or is this theoretical? Most people get along fine for a while, and when they have a problem, they use pgbouncer, which seems to solve it. "extremely painful replication, largely offloaded to third party tools" Do you have some more specific/constructive criticism here? A lot of work has gone into postgres replication over the past few years, and it's improved dramatically for some use cases. But there are many use cases for replication, so it's possible that you didn't see improvements for your use cases. FWIW, I started out with MySQL, and then tried out Postgres because of the license (MySQL was not yet GPL). After MySQL was GPLed, I used both for a while, but found the postgres community more welcoming/informative, and found postgres much more pleasant to use. At one point around 2003 I basically threw mysql away completely because I was fed up trying to do simple date manipulation to make simple reports. Postgres made it so easy and it seemed so natural -- it's really hard to use mysql afterward without getting frustrated. I'm sure there are things like that in reverse as well; e.g. mysql has the ON DUPLICATE KEY clause, but to me it still didn't add up to a pleasant/productive experience.

AlisdairO 14y ago

It's because, fundamentally, it's better for most stuff. It has transactional DDL, check constraints, window functions, CDEs, joins other than nested loop, and a better focus on safe-by-default behaviour. It also has a query optimiser that is generally more competent on difficult queries. The per-connection cost isn't much of an issue with a connection pool, and replication has improved a lot lately (and is clearly only going to get better). Aside from ease of getting up and running, IMO there's very little reason to pick MySQL over Postgres nowadays.