Just had a discussion at work about the merits of using PostgreSQL over MySQL and vice-versa. Does anyone have any practical experience where there is a valid reason to use one over the other?
Some people were saying that Postgre is better for security purposes whereas MySQL is becoming more feature rich... I'm not sure what to make of it.
It can also depend on your point of view...
If you were an employer, you'd want to ensure the highest possible adoption rate so you can find developers that can work on it.
If you're a developer, features, documentation and community are important.
If you're an architect, security, features and scalability are important.
If you're the end user. You don't care!
Take a look at this notice that it's coming from Postgresql.org though
The simple answer is that postgres is better for both features and security.
However, MySQL is much more popular, so is more likely to be directly supported by a random application, especially web applications. MySQL also makes claims to be faster (probably since it has less features).
There are thousands of web pages devoted to this very topic.
This isn't really first-hand knowledge, but...
From what I've heard about speed:
MySQL's MyISAM is faster than PostgreSQL
PostgreSQL is faster than MySQL's InnoDB
(Note that I've never used PostgreSQL, only MySQL)
The question isn't which is better, but which is better for the particular use you're intending.
If you don't need transactional consistency, mySQL might give better performance. If you do, PostgreS can be faster. If you need spatial searching, PostgreS. Tables larger than 2GB, PostgreS. Bitmap indexes, PostgreS. INTERSECT, PostgreS.
... this could go on ad-nauseum. And then we get to the question of 'best', and this assumes that mySQL or PostgreS are the only two options. Sometimes, SQLite is the best. Or Oracle. Or even some storage engine that's not an RDBMS.
(and note that I said 'might' give better performance -- just because one's typically faster doesn't mean that it'll be faster for the way you're going to be stressing it)
I have used both and even migrated projects from MySQL to PostgreSQL and in my experience PostgreSQL was the better choice. For my applications it was at least on par speed wise with MySQL and offered needed features not found in MySQL. That being said, if MySQL meets your needs, it is a good little dbms and like other dbms' continues to improve.
Could write a new answer here, but there are so many existing ones it's tough to get motived to:
If you are using the DBMS for website with no transactional needs - your choice is MySQL with MyISAM engine. If you are develop the more industrial software - then PostgreSQL. The amazing feature from PostgreSQL is writing stored procedures in different languages not only PL/PgSQL. There can be C, Python, PERL... anything you want and for which the language handler exists.
Oracle 9i server is very strong RDBMS, and to learn how to use an RDBMS, i think PostgreSQL is a nice choice.