MySQL has an awful lot of mindshare because sites like Slashdot run on it. If your application does very simple queries and very many more reads than write, perhaps MySQL is suitable. The problem comes when people who don't know much about databases extrapolate from there into inappropriate problem domains. Which in this case is, anything that isn't like Slashdot. Which is a lot of things.
It's unfair to criticize SQLite, tho'. It does exactly what it claims to do (unlike MySQL which has "Enterprise" stickered all over it now). Incidentally MySQL's claims to be the worlds most popular open source database are lies, SQLite has orders of magnitude more installations (hint: you probably have a copy of it running in your pocket or your bag right now).
I debated this MySQL vs. Postgre issue for a little while before deciding it was just better to go with MySQL and get started rather than nitpick over a solution.
It's one thing I've realized pretty quickly in a startup -- don't try solving or predicting architecture problems that don't even exist yet. MySQL does just fine for almost all new startups.
My understanding (1yr old) is that MySQL is frequently preferred to Postgres in startup-land because the replication story is much better. I'd love to hear if this is no longer true.
... you'll see that the MySQL section on replication has a short, consistent, confident story:
MySQL ships with replication support.
... while the PostgreSQL section offers an excuse, a list of choices, an explanation, a caveat, and a promise:
PostgreSQL is modular by design, and replication is not in the core. There are several packages that allow replication in PostgreSQL:
[List of packages here]
It is a common misconception that these "third-party packages" are somehow less well integrated. Slony, for example, was designed and built by Jan Weick, a PostgreSQL core team member...
...However, Slony is considerably slower and uses more resources than MySQL's built-in replication, as it uses SQL and triggers rather than binary log shipping to replicate the data across servers. That may make it less suitable for larger cluster deployments with high performance demands...
Recently, the PostgreSQL core team has announced that basic replication is planned as part of the 8.4 release.
The moral of this story is: Both databases support replication! But their marketing (their "replication story") is different. MySQL has superior FUD-proof marketing while PostgreSQL offers you choices up front -- which is often a marketing mistake, because it's easy for your opponents to cherry-pick one choice and then FUD it to death (see above), and because people who aren't experts generally get paralyzed by an excess of choices (the pros! the cons! Who understands all that stuff?!) and will often leap at the chance to escape paralysis if you offer them a batteries-included option. See Schwartz, Barry: The Paradox of Choice.
Wow. Somehow I missed all this vitriol against MySQL when I was figuring out whether to use MySQL vs Postgres a year ago. Then again I am not a DB expert and MySQL was good enough for what I was doing.
It's unfair to criticize SQLite, tho'. It does exactly what it claims to do (unlike MySQL which has "Enterprise" stickered all over it now). Incidentally MySQL's claims to be the worlds most popular open source database are lies, SQLite has orders of magnitude more installations (hint: you probably have a copy of it running in your pocket or your bag right now).