Wow. This topic of discussion is a very old one. It is always full of fire and fury to be sure. Also, anyone who has participated in this discussion over the years can tell you that their opinions have changed over time. This is assured. I have been using both for many a moon now as well as Oracle, Interbase(aka.Firebird) and various others as well as many through ODBC. I will try to keep my opinion brief and to the point, because this topic often tends to be more religious than anything else when emotions get stirred up.
Public perception as I see it
MySQL: Faster, easier to set up, easier to maintain, more fun. Good enough, NASA uses it so it must be good enough for me. Getting better all the time, supports transactions now and has become a real contender in the RDBMS world. PhpMyadmin is great, and PHP and MySQL have been holding hands for years. MySQL has availability from tons of hosting companies.
PostgreSQL: Slower, harder to setup and tune. More robust feature set, but most of which you won’t need and can live without. Mature database built with ACIDITY in mind from the beginning.
My opinion on these topics
A few years ago I would have said I liked both, for different reasons. But over the past few years I have watched MySQL lose focus of doing what it does best. That is to say, I have always regarded MySQL as a vastly superior option to using a flat file db. Now they are adding all kinds of features at the expense of speed and being lightweight. PostgreSQL on the other hand over the past few years has continued down its path adding more and more Oracle level features and made major strides in terms of performance. Amazing awesome unbelievable strides.
Aside from MySQLs growth at the expense of performance and being lightweight, I also have always felt like the MySQL developers, much like the PHP developers just don’t get certain things. They knowingly do things that are quite simply, wrong. They understand why, and the implications but simply don’t feel it matters. For instance, MySQL has never really supported the concept of NULL properly, and probably never will. This is a problem. I won’t go in to the details, but its just like PHP not supporting unicode and connection pooling. Until enough people scream for it, they just don’t think its important. The problem is that in the beginning it never is important enough, but in the long run it always is. Then you are left to (sorry for the analogy) polish a turd, and friends, this is why MySQL will NEVER be, what PostgreSQL will be and has become.
A few years ago I worked for an online gaming company that ran there entire backend on MySQL. Imagine some 80 separate databases running independent to each other. I cannot even begin to tell you what a nightmare that is. There is no realistic way to deal with the capacity needed at that level. What is worse, is the trap. Everybody is guilty of it. We’ll just use this now because its fast and simple, and if we need to switch to something better, we’ll do it when the time comes. That single simple thought. It took about 10 seconds to make, but about 2 years to fix during an Oracle migration. How much more time would have been spent had MySQL not been used in the beginning? Now granted, not everybody has aspirations this high, but those who foresee any level of success should certainly think more deeply. At the time, we had several of the MySQL core engineer developers in house trying to help us figure out how to deal with the growing pains. Transactions were the only real solution, and MySQL was not ready. Some time later, they were and we tried them and they failed miserably under heavy load. Maturity matters! Being built with ACIDITY from the beginning matters!
Ok, I’ve gone on longer than I intended. Let me finish by saying just a couple things that summarize my feeling about MySQL vs PG. If you are building a blog, or a brochure site with some dynamic content, by all means use MySQL if you please. If you have ever used PG, you probably know that it isn’t any harder to set up and despite the claims, will run just fine without tuning for applications with low concurrency such as you’d consider using MySQL for. If there is money involved, in anyway beyond the most simplistic, do yourself a favor and use PG.
One of the most useful aspects of a mature ORDBMS is the ability to store as much of the business logic as possible as close to the data as possible. Enforcing business logic at the database level ensures that as little application code as possible need perform complex business logic on data and large chunks of data. Enter transactions, store procedures and views. The more you can use your database like an application, the less complicated your application layer will become, ultimately making your IP more portable to other application consumers or future growth.
In the end, for me personally there are very few times that something is insignificant enough to me that I care to use MySQL. Honestly I think I would be using it more on smaller projects if it hadn’t lost focus of its core strength. Simplicity! The other thing is PostgreSQL is just so damn GOOD now! Free, Fast, scalable, and full featured. As soon as replication is supported natively, then we can begin arguing for years on the topic of PG vs Oracle.
