I love Postgres, and I usually get grumpy when forced to use Mysql, but ... rather than lots of fluffy features (most of the stuff listed here is not that compelling taken individually), <i>the</i> thing for me about Postgres is that it's solid, reliable, and dependable. It behaves correctly. It has transactions. It has DDL transactions. It can, by and large, be counted on to get things right, rather than take shortcuts. It's a well-crafted tool that you can get a lot of mileage out of if you know what you're doing.
Interesting articles. I read part I and part II.<p>As a long-term mysql user who has dabbled with production Postgres machines I still don't see how Postgres really has any huge advantages that would cause me to want to run it for any new projects and deal with a new and possibly daunting learning curve. Postgres seems to do some things better perhaps, and in general seems to have more flexibility than MySQL. But it also seems to lack focus as a end-to-end db solution.<p>The clustering / replication complexity of Postgres is a huge issue for me, since scaling databases is not a trivial thing in the best of cases. With postgres, there seem to be 10+ different cluster / replication systems (both open-source and commercial?) and it's just a mess.<p>Giving people tons of options is great, but ease of use for developers and an amazing & simple out of box experience is so important. MySQL won the hearts and minds of developers this way... and now MongoDB is doing the same thing by improving on what MySQL what able to accomplish. The Postgres team should consider implementing something similar.
Another cool feature: recursive queries using the WITH common table expressions.<p>Say you have a table fs <id, name, parent_id> representing a hierarchical filesystem, and you want to print the full path of each file, here's how you can do it in a single query:<p><pre><code> WITH RECURSIVE path(id, name, parent_id, path, parent) AS (
SELECT id, name, parent_id, '/', NULL FROM fs WHERE id = 1 -- base case
UNION
SELECT fs.id, fs.name, fs.parent_id, parentpath.path ||
CASE parentpath.path WHEN '/' THEN '' ELSE '/' END ||
fs.name as path, parentpath.path as parent
FROM fs INNER JOIN path AS parentpath ON fs.parent_id = parentpath.id
) SELECT id, name FROM path;</code></pre>
Could someone that's really familiar with Postgres give me the skinny on replication? I like Postgres a lot, but finally moved all of our data storage to MySQL because, according to the documentation I read just a couple months ago, Postgres couldn't really do multi-master replication.<p>This post, dated prior to the documentation I read before the switch, seems to suggest it would do it just fine as long as it was asynchronous. I'm not clear though on just how ugly its async replication might be (<a href="http://www.postgresql.org/docs/8.4/static/high-availability.html" rel="nofollow">http://www.postgresql.org/docs/8.4/static/high-availability....</a>).<p>I'd like to use dbmail to store mail in a database replicated across multiple servers (which would need fairly reliable replication), as well as syslog and other logging facilities to sql (where reliability isn't quite so big of a deal).<p>Is Postgres up to this now, or not?
Original author here, after the original post there was so much great feedback that it was worthwhile to publish a part two - <a href="http://craigkerstiens.com/2012/05/07/why-postgres-part-2/" rel="nofollow">http://craigkerstiens.com/2012/05/07/why-postgres-part-2/</a><p>Additionally there will probably be a third post when Postgres 9.2 releases soon highlighting some of the great new features such as the JSON datatype.
I'd add "business risk" into the discussion. Mysql has an interesting road ahead of it, and there are certain people that just won't put open-source Oracle in their critical path.
A few week ago I discovered Postgres Schemas (worst named feature ever -- nothing to do with SQL schema definitions). Basically, schemas allow you to have multiple databases within a single database. So, you only need to manage (or host/pay for) a single database but each company can have their own schema which gives you complete separation of data across all the tables.<p>These, together with the Apartment gem for Rails, makes building isolated multi-tenant applications really easy. It also makes migrated to a true multi-database/multi-server/sharded setup much simpler.
I don't value the underlying database as much anymore since I have been working with Rails apps. The database is just an API. I want to write my code in such a way that I could unplug POSTgres and plug in SQL Server tomorrow without skipping a beat. Using AREL you should largely be able to do that.
Is Oracle's ownership of MySQL becoming an issue? I realize it's open source but still it seems like things have become to deteriorate slightly. Is that causing any alarm?
One thing I appreciate about using Django and its DRM is that it hardly matters what database I use.<p>If I'm writing SQL by hand I much prefer Postgres to MySQL since it is more standards conforming and has fewer random quirks. But given that Django generates the SQL for me, which database to use is largely a matter of ease of deployment, and on AWS that's MySQL (via RDS) so that's what I use.
Here's a link to the previous discussion <a href="http://news.ycombinator.com/item?id=3910743" rel="nofollow">http://news.ycombinator.com/item?id=3910743</a>
To answer his question, yes virginia, we (people that use MySQL) use replication a lot. Especially at mid-grade levels of scaling where custom sharding and replication middleware hasn't necessitated itself yet but vertical scaling is out of the question.<p>I'd say the bigger issue with Postgres now is that middleware like <a href="http://code.google.com/p/vitess/" rel="nofollow">http://code.google.com/p/vitess/</a> for it hasn't been deployed in the large yet. Most Postgres scaling anecdotes I've heard were:<p>"Well we put 64 gb of ram in the server and installed a RAID array of SSDs and stopped writing dumb unindexed queries."<p>Well that's just dandy, but what if my indices don't even fit in the ram of a single machine?