We never looked back. PostgreSQL is really powerful and we use it in a clustered and soon sharded setup. Works well for us.
I don't have enough experience with MySQL to be objective. I chose PostgreSQL because it had a feature set relatively on parity with Oracle and the other big boys. So far I haven't been disappointed.
Well, except for tool support. I'm still looking for a good schema diff tool for it (something as brilliant as SQL Compare by red gate) and a good ER tool (something as simple but functional as Enterprise Manager for SQL Server).
Otherwise, I really haven't considered looking at anything else.
There's also Aqua Data Studio. I've never used it, but it looks pretty awesome. A little pricey, though.
Huge fan of Aqua Data Studio though, great tool.
HappyFish (http://www.polderij.nl/happyfish/gallery.html) is elegant, simple, inexpensive and powerful and works with Postgres and MySQL. I have run it under wine on linux.
Here are a few points that tipped things to postgres.
1 - constraints and complex queries. MySql handles simple selects fast but has somewhat unpredictable behavior on complex queries. Including counts. Postgres generally scales in proportion to complexity of the query well.
2 - Postgres 8.3 came out. This was a big deal as it cleans up lots of things that want into 8.x.
3 - BSD-style engineering. Everything from docs to source code to make files is more rigorous with old school BSD projects. FreeBSD and Postgres are clean!!! I don't want to hammer on linux, GNU, GPL stuff too much, but to be honest, this camp is stable because of massive user base and companies put in effort to make it stable. Its a sledgehammer approach. In general I find projects like FreeBSD and Postgres to follow more rigid engineering processes. I like this in my DB ;)
4 - Full Text Search built into postgres 8.3. MySQL has FTS, sort of. Postgres prior to 8.3 had it as an add-on. You can run external Full Text Search engines like lucene or
ferret or maybe even better, sphinx. But this adds one more "engine" to manage. Most real world FTS use cases needs to have its results filtered in context of user prefs and have results filtered and sorted by other DB criteria. This means that for many FTS use cases, it is very desirable to just give the DB one big complex query and have it return things all sorted out. Using an external FTS system, you usually end up taking your FTS results and going back to the DB for further queries to filter and sort the FTS results. This adds to app code complexity. With postgres 8.3, you can do this in ways that MySQL cannot begin to compete with.
Hope these rational help. I am very happy with my decision to switch from MySQL to postgres and the process was not painful.
good luck, Jon
A year ago is would have been MySQL, just because everyone else who is big is them (e.g. paypal, google, amazon etc.) but now I really think PostgreSQL is the better choice and will be going ahead with it until I can think of a reason not to.
It is now just as fast, but much more robust
By default, MyISAM compresses its primary key: http://www.mysqlperformanceblog.com/2006/05/13/to-pack-or-no...
So you get bad performance when the primary key is accessed in descending order: http://bugs.mysql.com/bug.php?id=17847
If this corrupted, you get a different row count when you order by ascending or descending key. I've seen this in the wild. Unfortunately, this possibility has been repeatedly missed by developers: http://bugs.mysql.com/bug.php?id=28542 http://bugs.mysql.com/bug.php?id=28658 http://bugs.mysql.com/bug.php?id=35605
MySQL is a nice bit bucket but I'll continue using Postgres for anything important.
Those sites need to scale an order of magnitude (or several) greater than sites like CD Baby or Macworld.
Just look at: http://www.postgresql.org/about/users
Other than Skype, there aren't as high-use sites as the ones I mentioned that go for mySQL. Postgres isn't bad, but it just hasn't been tested at the same level. Could it do better at that level? It IS possible. I prefer to go for a sure thing.
I think today when building, the choice is not as one sided in favour of MySQL.
One of the only reasons I still use MySQL is that PostgreSQL would break some Drupal sites I deployed. I miss the ease of obtaining relational integrity under Postgres (although pgsql's EXPLAIN can be horrifying compared to MySQL's).
Have scaled up to saturating an OC12 with Postgres (17 web servers) and doing 30 million hits/month with MySQL, so it's not like I care that much, but MySQL has a nasty tendency to piss me off in corner cases, and I'm getting tired of it.
Definite pros and cons to both. I've seen people stick with (and patch) a particular point release ofMySQL (eg. 3.23 for AdWords at one point) just because the errors were consistent and they were no longer doing much to the schema.
Never seen that happen with PostgreSQL.
Jeremy Zawodny's tools (mytop, etc.) are invaluable if you have to get a MySQL shop to scale up (eg. without migrating a metric fuckload of existing SQL), btw.
pgsql's EXPLAIN can be horrifying compared to MySQL's
BTW, there's a "ptop" (pg_top) project for PostgreSQL as well.
a) Once I had to store a large chunk of time series data in MySQL 'TEXT' field. Got errors in the application, could not figure out where they are coming from.
Turned out, MySQL has 1 to 65535 characters limit for TEXT field, and I should have used LONGTEXT type. MySQL has simply bitten the tail off my data and happily stored the rest without any errors or warnings. OK, I should have RTFM but still, the idea of a database corrupting my data without any warning makes me very uncomfortable...
b) Second issue that I've discovered is simply the ability to make 'hot backups' to SQL text files. pg_dump will produce consistent backup from working db server, mysqldump requires you to stop the server or to lock its tables etc. 'Hot backup' of mysql databases can be achieved through other methods/tools but at the cost of greater complexity.
That is just insane. I heard about it a couple months ago and wondered what were those folks smoking when they wrote it like that.
The hot backups thing never crossed my mind, but is very interesting. Of course, it relies on transaction consistency and MySQL has the option (shivers) of not having it, thus the locking demands.
From what I know (I am a PostgreSQL guy too), when you use a transaction-aware data store in MySQL, these behaviours are gone (along with any performance edge it may have over PostgreSQL, but that's another story), so, you may want to give it a try.
Run a slave, stop it, do mysqldump --single-transaction --master-data yourdbname off the slave to get a hot backup, and then restart the slave to get hot copies without shutting down writes. (I am going to assume that you round-robin your data into memcached from the read slaves if you are worried about a read lock on the master)
Not saying that you shouldn't use Postgres, but if you have to work with MySQL, the technique above produces consistent hot backups from 4.0.18 onwards.
MySQL can have fairly unpredictable performance characteristics if you're not an expert in it. It certainly can scale, but it's very easy to kill your performance if you're indexed wrong or have the wrong table type or wrong cache settings or run into table locking issues. (And these aren't theoretical issues; we've run into some of them at past MySQL-based websites.)
I don't have the time to spend debugging cache settings and EXPLAIN SELECTs and locking issues, so I went with Postgres, which supposedly has many fewer pitfalls "out of the box". Been happy with it so far, for development, but haven't needed to scale it yet.
Sometimes we'll think wistfully of more OTS solutions for Oracle and MySQL, in a grass-is-greener sorta way, then we see stuff like this: http://bugs.mysql.com/bug.php?id=11472
I've been watching free-software databases since 1997, when people at MIT were using msql and I thought mysql would be a better choice. PostgreSQL looked like it had the potential to be better than mysql, but it wasn't fast or stable yet.
A few years later I tried PostgreSQL 7 and it was great.
1. license less confusing
2. great mailing list support
The last thing I want my DB to do is corrupt my data silently. There were other braindead "philosophy" problems but I don't remember what they are.
Been a happy user of Postgres ever since.