MySQL vs PostgreSQL for Web Applications [closed]

I am working on a web application using Python (Django) and would like to know whether MySQL or PostgreSQL would be more suitable when deploying for production.

In one podcast Joel said that he had some problems with MySQL and the data wasn't consistent.

I would like to know whether someone had any such problems. Also when it comes to performance which can be easily tweaked?

Solution 1:

A note to future readers: The text below was last edited in August 2008. That's nearly 11 years ago as of this edit. Software can change rapidly from version to version, so before you go choosing a DBMS based on the advice below, do some research to see if it's still accurate. Check for newer answers below.

Better?

MySQL is much more commonly provided by web hosts.

PostgreSQL is a much more mature product.

There's this discussion addressing your "better" question

Apparently, according to this web page, MySQL is fast when concurrent access levels are low, and when there are many more reads than writes. On the other hand, it exhibits low scalability with increasing loads and write/read ratios. PostgreSQL is relatively slow at low concurrency levels, but scales well with increasing load levels, while providing enough isolation between concurrent accesses to avoid slowdowns at high write/read ratios. It goes on to link to a number of performance comparisons, because these things are very... sensitive to conditions.

So if your decision factor is, "which is faster?" Then the answer is "it depends. If it really matters, test your application against both." And if you really, really care, you get in two DBAs (one who specializes in each database) and get them to tune the crap out of the databases, and then choose. It's astonishing how expensive good DBAs are; and they are worth every cent.

When it matters.

Which it probably doesn't, so just pick whichever database you like the sound of and go with it; better performance can be bought with more RAM and CPU, and more appropriate database design, and clever stored procedure tricks and so on - and all of that is cheaper and easier for random-website-X than agonizing over which to pick, MySQL or PostgreSQL, and specialist tuning from expensive DBAs.

MySQL is the only database I've ever programmed against in my career that has had data integrity problems, where you do queries and you get nonsense answers back, that are incorrect.

and he also said:

It's just an anecdote. And that's one of the things that frustrates me, actually, about blogging or just the Internet in general. [...] There's just a weird tendency to make anecdotes into truths and I actually as a blogger I'm starting to feel a little bit guilty about this

Solution 2:

Just chiming in many months later.

The geographical capabilities of the two databases are very, very different. PostgreSQL has the exceptional PostGIS extension. MySQL's geographical functionality is practically zero in comparison.

If your web service has a location component, choose PostgreSQL.

Solution 3:

I haven't used Django, but I have used both MySQL and PostgreSQL. If you'll be using your database only as a backend for Django, it doesn't matter much, because it will abstract away most of the differences. PostgreSQL is a little more scalable because it doesn't hit the brick wall as fast as MySQL as data-size/client-count increase.

The real difference comes in if you are doing a new system. Then I'd recommend PostgreSQL hands down, because it has a lot more features which make your DB layer much more customizable, so that you can fine-tune it to any requirements you might have.

Solution 4:

Although it's a bit out of date, it would be worth reading the MySQL Gotchas page. Many of the items listed there are still true, to the best of my knowledge.

I use PostgreSQL.