MySQL vs PostgreSQL? Which should I choose for my Django project?

Question

My Django project is going to be backed by a large database with several hundred thousand entries, and will need to support searching (I'll probably end up using djangosearch or a similar project.)

Which database backend is best suited to my project and why? Can you recommend any good resources for further reading?

Solution 1

As someone who recently switched a project from MySQL to Postgresql I don't regret the switch.

The main difference, from a Django point of view, is more rigorous constraint checking in Postgresql, which is a good thing, and also it's a bit more tedious to do manual schema changes (aka migrations).

There are probably 6 or so Django database migration applications out there and at least one doesn't support Postgresql. I don't consider this a disadvantage though because you can use one of the others or do them manually (which is what I prefer atm).

Full text search might be better supported for MySQL. MySQL has built-in full text search supported from within Django but it's pretty useless (no word stemming, phrase searching, etc.). I've used django-sphinx as a better option for full text searching in MySQL.

Full text searching is built-in with Postgresql 8.3 (earlier versions need TSearch module). Here's a good instructional blog post: Full-text searching in Django with PostgreSQL and tsearch2

Solution 2

For whatever it's worth the the creators of Django recommend PostgreSQL.

If you're not tied to any legacy system and have the freedom to choose a database back-end, we recommend PostgreSQL, which achives a fine balance between cost, features, speed and stability. (The Definitive Guide to Django, p. 15)

Solution 3

large database with several hundred thousand entries,

This is not large database, it's very small one.

I'd choose PostgreSQL, because it has a lot more features. Most significant it this case: in PostgreSQL you can use Python as procedural language.

Solution 4

Go with whichever you're more familiar with. MySQL vs PostgreSQL is an endless war. Both of them are excellent database engines and both are being used by major sites. It really doesn't matter in practice.

Solution 5

Even if Postgresql looks better, I find it has some performances issues with Django:

Postgresql is made to handle "long connections" (connection pooling, persistant connections, etc.)

MySQL is made to handle "short connections" (connect, do your queries, disconnect, has some performances issues with a lot of open connections)

The problem is that Django does not support connection pooling or persistant connection, it has to connect/disconnect to the database at each view call.

It will works with Postgresql, but connecting to a Postgresql cost a LOT more than connecting to a MySQL database (On Postgresql, each connection has it own process, it's a lot slower than just popping a new thread in MySQL).

Then you get some features like the Query Cache that can be really useful on some cases. (But you lost the superb text search of PostgreSQL)

View more solutions

Comments

[Wayne Koorts]

"This is not a large database, it's very small one." Well, it's smaller than databases larger than it, and larger than smaller ones.

[Nathan Osman]

Erm... I think DBMS refers to "database migration system" which would be South itself, not MySQL.

[Nathan Osman]

Django 1.6 is adding support for persistent connections, so this should no longer be a concern.

[Kedare]

DBMS = DataBase Management System = MySQL / Postgresql / Etc.

[Emile Bergeron]

As of Django 1.7, migrations are now an integral feature of Django.

[Meetai.com]

"MySQL lacks support for transactions around schema alteration operations, meaning that if a migration fails to apply you will have to manually unpick the changes in order to try again (it’s impossible to roll back to an earlier point)." Source: docs.djangoproject.com/en/1.9/topics/migrations

[Kamesh Jungi]

Question clearly specify that, one need to evaluate database for Django point of view. So it's not relevant.

[doubleo46]

for the persistent connection, we can set "conn_max_age" in database configuration to a positive value which is in seconds.

[run_the_race]

I disgree with this, see my answer below, why I started with MySQL cause I knew mySQL very well, but regretted it later on.