Duplicate:
I've been toying with PostgreSQL for about a week, and, while I like it, I can't figure out what the specific advantages of PostrgreSQL over MySQL (using InnoDB) are.
There seem to be a lot of features, but for a web application, is it really better to use PostgreSQL? (assuming each page request does about 20 cached queries, 2-3 of which would be on large tables.
I saw this question get asked on Slashdot once about 5 or 6 years ago, and as far as I'm aware at least several hundred people have been killed as a result, and the argument is still going.
The single biggest reason for using MySQL over PostGreSQL is that most web hosting companies provide MySQL for a database backend, not PostGreSQL.
Apart from that, there are a host of reasons for picking one over the other.
PostgreSQL provides several useful built in data type, like types for geographic information, and implements a better locking mechanism for multiple updates. MySQL is more widely used and supports failover somewhat better.
The difference is frankly somewhat at the margin, except if you have a lot of GIS information.
PostgreSQL comes up with much better query plans than MySQL in many cases (uncorrelated subqueries to name a common case). This will be even more true shortly when 8.4 hits the streets. In small applications MySQL is fine, but on larger hardware or larger datasets, Postgres takes the performance crown hands down.
The biggest advantage MySQL has is that hosting providers are cheap and plentiful.
One advantage of MySQL over PostgreSQL is that MySQL provides built-in replication support (allowing you to have a backup database in case the main one fails), whereas PostgreSQL needs third-party addons.
MySQL is easier to start using.
Nicer UI tools. Faster, if you don't use ACID. More tolerant of invalid data. Autoincrement columns are as easy as typing autoincrement. Permissions aren't as tied to the file systems and OS users. Setting a delimiter is easier than using pg's "dollar sign quoting" when writing a stored proc. In MySQL, you connect to all databases, not just one at a time.
Posgtres is much more standards compliant, but it's uglier and more complicated, especially from a UI perspective. It used to require manual vacuuming, and actually enforces referential integrity (which is a great thing that can be a pain in the ass). Autoincrement is much more flexible, but requires sequences (which can me masked by using serial), and wait, what's an OID?
So if you don't really know or care much about databases, data validity, ACID compliance, etc, but you do care about ease and speed, you tend to go with MySQL.
Too many (not all, but many) "web programmers" know a lot about "web 2.0" or PHP or Java, but don't know much about database theory or practice ("an index? what's that?"). They tend to see a database as just a fancy hashtable or bag of data, and indeed one that's not anywhere as dynamically changeable or forgiving as a hashtable.
For these folks, MySQL -- because until 5.0 it wasn't really an RDBMS, and in many ways still is not -- is a godsend. It's "faster" than the competition, and doesn't "waste time" on "esoteric" database stuff a web programmer doesn't want, understand, or see the value of.
For somebody with a database background, on the other hand, MySQL is a minefield: stuff that should work (complicated views, group bys, order bys in group bys) may work or may if you're lucky crash the server, or if you're unlucky just give results with incorrect data.
I've spent days working around some of these things in admittedly complicated by not extraordinarily complex views and group bys.
And MySQL isn't really faster. If you're using InnoDb tables for ACID (or just because at more than 30 Million rows, MyIsam tables tend to get crappy), yes a straight one-table select is probably faster than in pg. But add in joins, and pg is suddenly significantly faster. (MySQL is especially bad at outer joins.)
In summary: if to you the database is a bag, if you never intend to do data mining or reporting, if you're mostly interested in serving up big hunks of text with few relations or updates -- that is, if you're using a database to power a blog, MySQL is a great choice.
But if you're actually managing data, if you understand that data lives longer and is more valuable to a business than front-end programs and middle-tier business rules , if you need the features of a real database, use pg.
A "web programmer" who has decided all his table structures can be auto-generated by Hibernate (or some other ORM) looks at that and says, "too complicated" and "I bet complicated means more cost and slower speed" and so he goes with MySQL.
As I said, pg is far superior, and I hate mucking with MySQL's bizarre bugs, and I think that overall pg performance is probably better than MySQL for any even slightly complicated query.
But MySQL makes things look (deceptively) simple, so you get a lot of people who don't really understand database design figuring that MySQL is a great choice.
Use pg. It's consistent, it's reliable, it's standards-compliant, it's faster on (even moderately) complicated queries, it doesn't completely throw off your schedule with weird bugs.
MySQL as a start and then PosgreSQL when you need and are ready for the advanced features.
Let's dispel some myths ;) I am talking about RECENT versions (as of 2009) here, not stone-age postgres 7.X or mysql 4.x !
That is a misconception. The database you know well and know how to optimize will be the fastest one. For database dummies, MySQL tends to be faster though, because postgres will require more configuration. But if you are a database dummy, you don't ask yourself the question, you use what your service provider provides...
On a very simple query like "SELECT * FROM table WHERE pkey = ...", postgres using prepared queries will be faster than mysql. Not using prepared queries, it will be about the same.
Your client code will probably be slower than the database since a query like above takes about 50 microseconds... so the client library becomes very important and can be a major part of the benchmark !
Postgres wins big time.
A Postgres database designed by a good postgres DBA will generally outperform a MySQL database designed by a good MySQL DBA except if :
HOWEVER
On complex queries, postgres can come up with pretty smart plans, and MySQL often commits suicide. So if you have one query taking 2 hours in MySQL and 5 seconds in Postgres (which happens), this can offset quite a lot of queries taking 100 microseconds in MySQL and 150 microseconds in Postgres !...