PostgreSQL gets a lot of flack for not having replication. It's been my experience that it just doesn't matter in practice. Let me explain.
MySQL's performance sucks. It's great lightly loaded and with little concurrency. Add load and it noses over. (I've read that this is mostly because of the malloc they use. Regardless of the cause, it's a problem.)
I tested MySQL 5 vs PG 8.3 to handle session storage. This is about as simple of a test as possible. PostgreSQL outperformed MySQL by a significant margin across every load scenario, sometimes up to 2x the capabilities. PG's performance flatlined with load. MySQL flatlined and then hit a wall and dropped significantly as load and concurrency rose.
So the first thing someone does when MySQL noses over is to buy faster hardware. But that doesn't really help as much as it should. So they add replication to be able to distribute load across multiple servers. That's all fine and dandy until you realize that for every rep node you add, you are adding overhead for writes. And the headaches of keeping MySQL's brain dead replication in sync. Eventually you spend all of your time waiting for replication to finish or rebuilding a failed sync or chasing down ghosts in the system.
Back to postgres. Its performance scales nearly linearly with load and number of cores. Adding more hardware resources actually improves performance accordingly. So you end up being able to stay on one machine longer.
If you're truly going to have more load than one server can handle, break the data set up into smaller pieces (sharding).
Postgres is an awesome database. It behaves consistently and correctly. It has a lot of polish when you get into it. psql is a pleasure to use. It supports more of the SQL standard than MySQL. It's query analyzing tools are great. The only real pain point is that you have to do a dump/load to upgrade point releases (8.1 -> 8.2).
With all of that said, MySQL is great for smaller sites. It's available everywhere and every framework and language supports it. There are a million people who can administer it. Postgres is a lot harder to find competent DBAs.
PostgreSQL has been improving significantly throughout the 8.x series. Autovacuum has made life easier for everyone. Full text search is now built in. It now supports recursive queries and a bunch more.
MySQL's performance sucks. It's great lightly loaded and with little concurrency. Add load and it noses over. (I've read that this is mostly because of the malloc they use. Regardless of the cause, it's a problem.)
I tested MySQL 5 vs PG 8.3 to handle session storage. This is about as simple of a test as possible. PostgreSQL outperformed MySQL by a significant margin across every load scenario, sometimes up to 2x the capabilities. PG's performance flatlined with load. MySQL flatlined and then hit a wall and dropped significantly as load and concurrency rose.
So the first thing someone does when MySQL noses over is to buy faster hardware. But that doesn't really help as much as it should. So they add replication to be able to distribute load across multiple servers. That's all fine and dandy until you realize that for every rep node you add, you are adding overhead for writes. And the headaches of keeping MySQL's brain dead replication in sync. Eventually you spend all of your time waiting for replication to finish or rebuilding a failed sync or chasing down ghosts in the system.
Back to postgres. Its performance scales nearly linearly with load and number of cores. Adding more hardware resources actually improves performance accordingly. So you end up being able to stay on one machine longer.
If you're truly going to have more load than one server can handle, break the data set up into smaller pieces (sharding).
Postgres is an awesome database. It behaves consistently and correctly. It has a lot of polish when you get into it. psql is a pleasure to use. It supports more of the SQL standard than MySQL. It's query analyzing tools are great. The only real pain point is that you have to do a dump/load to upgrade point releases (8.1 -> 8.2).
With all of that said, MySQL is great for smaller sites. It's available everywhere and every framework and language supports it. There are a million people who can administer it. Postgres is a lot harder to find competent DBAs.
PostgreSQL has been improving significantly throughout the 8.x series. Autovacuum has made life easier for everyone. Full text search is now built in. It now supports recursive queries and a bunch more.