Who wants to discuss about MySQL vs PostgreSQL?

I am wondering which choice is popular among this startup community? Tests showcase PostgreSQL perfomance and scalability on a multicore to be better than MySQL.

What are your thoughts and the database of your choice?

http://tweakers.net/reviews/657/5 http://tweakers.net/reviews/657/6

I had been reading numerous articles comparing MySQL and PostgreSQL and ran across this one. I wish they had mentioned some information about the configuration of the DBMS itself, such as the size of database buffers and query optimization settings.

Also, for their MySQL test procedures, I could not find any information regarding which storage engine they were using. InnoDB is fully ACID-compliant, whereas MyISAM has reduced overhead since it does support transactions and foreign key constraints, for example. I found a good article, http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ , comparing the two storage engines, and the results were pretty surprising to me.

It ultimately comes down to your individual usage patterns. Using a database abstraction layer will allow you to test your application against different databases so you can see how your application performs first hand.

Our Ruby on Rails app uses PostgreSQL (for now but that will change). We started off with MySQL but after growing quickly, we were forced to look into partitioning of the data. PostgreSQL was made to be easy for advanced stuff like that. We tried to do it with MySQL and failed because we're not MySQL experts and we managed to get it working with Postgres. Postgres has better documentation than MySQL so that might be the factor if you don't have a DBA you can readily ask for help. Here's a write-up by someone who went through similar process:

http://enfranchisedmind.com/blog/archive/2006/11/04/166

Also, if you use Postgres on multi-CPU machines, it's faster than MySQL. But, make sure you read this:

http://www.suite101.com/article.cfm/oracle/115560 (comparison of Oracle/MySQL/PostgreSQL)

One last word of advice if you end up using PostgreSQL, learn and use this:

http://pgreplicator.sourceforge.net/

Finally, we are moving back to MySQL because we found a really fast replacement for the MyISAM storage engine (a lot faster than Postgres) and our part-time DBA knows MySQL and this new storage engine a lot better than Postgres. We are also moving to VPS architecture and Postgres' performance advantage over MySQL on a VPS doesn't really exist.

In the end, go with what your staff/friends know and what your hosting place recommends. If they have an optimized DB stack and provide some kind of a support for it, stick with that DB because you can always go to them for help!

PG writes in the Viaweb FAQ that they (and Yahoo) did not use any database, just files. It makes sense... Is there something fundamentally different about your app, so that a DB is obviously a better choice?

Actually, pgreplicator saw its last release in 2001, and I'm not aware of anyone who uses it in production. The fact that it requires PostgreSQL 7.1 (which is ancient, buggy, and unsupported by the Postgres developers) would be a pretty big clue not to use it. The most popular choice for replication (async master-slave) with Postgres is Slony I. Commandprompt's replicator is another choice, although it is a commercial product ($1000 USD per machine).

When you say that "Postgres' performance advantage over MySQL on a VPS doesn't really exist.", I'm not sure what you mean: DBMS performance is a complicated subject, and significant performance differences exist between MySQL and PostgreSQL on essentially ANY hardware platform. For example, if your app uses complex queries with a lot of joins and subselects, Postgres optimizer will likely give you significantly better performance than MySQL's, regardless of whether it happens to be running on a VPS. Similarly, MyISAM's performance with an update-intensive concurrent workload is going to be pretty bad, regardless of the hardware platform.