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:
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!
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.
I've used both and prefer Postgresql. Mysql with MyISAM is "faster" in the same way that bicycling down a hill with no brakes is faster. Mysql 5 with InnoDB is a little bit more serious and is worth considering.
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?
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.
prior to multicore cpus asuming the scaling was achieved horizontally, MySQL has been a good choice. But today, if your systems comprising the clusters are at least dual core x2 cpu, I am lead to the conclusion, as nickb points out, that is better to use PostgreSQL.
We're building on PostgreSQL now. I'm not an expert on either platform but the research I did led me to PostgreSQL due to its better performance on larger systems and in more traditional enterprise software categories. Our software will operate with Oracle sometimes and having a similar database will make a few things easier.
However, it is quite out of date: for example, PostgreSQL has had a native Win32 port for several years, and the "--enable-locale" / "--enable-multibyte" configure options have not been necessary (or supported) for even longer. "Large row" (TOAST) support has been around since 7.1. etc.
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?
I think something is different about databases since then. You can, for free, install a database in part of an afternoon that has excellent library/framework support and tools. That makes a big difference. If you're going to use Ruby on Rails, for example, you will have to justify why you don't want to use database software.
Basically, databases have been well-commoditized, like Unix, HTTP servers, and hosting. If your needs fit their common usage scenarios, you should just go ahead and use them.
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!