I am working on a web application using Python (Django) and would like to know whether MySQL or PostgreSQL would be better 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?
Well, I don't think you should be using a different database brand in anything past development (build, staging, prod) as that will come back to bite you.
From how I understand it PostgreSQL is a more 'correct' database implementation while mySQl is less correct (less compliant) but faster.
So if you are pretty much writing a CRUD application mySQL is the way to go. If you require certain features out of your database (if you're not sure then you don't) then you may want to look into postgreSQL.
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.
count of rows back. The plural of anecdote is not data. He said:
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
If you are writing an application which may get distributed quite a bit on different servers, MySQL carries a lot of weight over PostgreSQL because of the portability. PostgreSQL is difficult to find on less than satisfactory web hosts, albet there are a few. In most regards, PostgreSQL is slower than MySQL, especially when it comes to fine tuning in the end. All in all, I'd say to give PostgreSQL a shot for a short amount of time, that way you aren't completely avoiding it, and then make a judgement.
I haven't used Django, but I have used both MySQL and PostgreSQL. If you will be using your database only as a back-end for Django, it doesn't matter that much, because it will abstract away most of the differences. PostgreSQL is a little more scalable (it doesn't hit the brick wall as fast as MySQL as data size / client count increases).
The real difference comes in if you are doing a new system. Then I'ld 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.
There's this discussion addressing your "better" question
Come on! Using a five year old post to compare MySQL and PostgreSQL is absolutely ridiculous.
Thank you. I've used Django with MySQL and it's fine. Choose your database on the features you need. Hard to compare MySQL and Postgres. Better to compare Postgress to SQl Server.
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.
MySQL is a relational database management system while PostgreSQL is an object-relational database management system. PostgreSQL is suited well for C++ or Java developers, as it gives us more control over how queries are written. ORDBMS also gives us Objects and User Defined Types. The SQL queries themselves are much closer to the ISO standards than MySQL.
Do you need an ORDBMS or a RDBMS? That will better answer your question.
@WolfmanDragon
PostgreSQL has (tiny) support for objects, but it is, by nature, a relational database. From its about page:
PostgreSQL is a powerful, open source relational database system.
Some say MySQL is simple and fast.
Fast - Well if you use the default MyIsam storage engine, it is fast. It has nearly zero serious database feature, but it's fast. If I'd be sarcastic, I'd say a grep on a flat file is fast too. Funny enough it's not that far from how MyIsam stores data. So don't expect any of relational database feature there. Not even reference integrity constraints.
Simple - Now let's say you want a fair subset of real database features. Some stuff like integrity constraints, triggers, transaction isolation, functional indices maybe? So obviously you have to let MyIsam engine away and choose from one of the main 3 other storage engines. Who have been developed by different vendors, each having a particular configuration/behaviour.
Have a look there : http://dev.mysql.com/doc/refman/6.0/en/storage-engines.html and if you don't get a headache, I'll agree MySql is simple from your point of view.
From those facts, I'll let you make your mind about those who say MySql is fast and simple.
If you want to scale more, save time, have less weird bugs to hunt, have more simple application code, spend more time optimizing your business, not fighting with your database, then the open source solution of choice is Postgresql.
hoping to contribute, we had the same problem in my startup, here is a blog spot on why we chose PostgreSQL: Choice of DBMS
I think the chosen best answer summarizes the argument in a very good sense. I do not know if the discussion should be open again after Sun (owner of MySQL) was bought by Oracle.
I use both extensively. My choice for a particular project boils down to:
By special sauce I things like:
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.