I'm more interested in knowing what PgSQL does better than MySQL today, in 2011. MySQL definitely has a few caveats, my question is mostly, is PgSQL now significantly better than MySQL or they're both more or less the same, with different pros and cons.
1. MySQL's "feature set" is usually described as a union of storage engine features; whereas in fact you only ever get some of them at a time. I find that extremely annoying.
2. PostgreSQL has a decent, smart query planner. In cases where I have multiply-layered views, I've seen MySQL throw up its hands and manually churn through each view in turn, while PostgreSQL did the smart thing and combined all the views into a single execution plan.
3. It's much more featuresome for in-database programming. The web world tends to look on RDBMSes as flat files with a funny accent, so this doesn't matter for a lot of programmers. But sometimes you absolutely must either a) protect the data or b) place computation as close to the data as possible. Featuresome databases like PostgreSQL, Oracle, DB2 etc let you do this. MySQL not as well.
"I'm more interested in knowing what PgSQL does better than MySQL today, in 2011."
The most striking difference[1] is probably the cost-based optimizer and the availability of a lot more execution plans. Most often cited is that MySQL has no hash join, but there are lots of plans that make a huge difference in execution time, and (for the most part), mysql just doesn't have those execution plans available.
And these are algorithmic differences. Typically, you think of optimization as something that does your same algorithm, just a little faster. In a SQL DBMS, optimization means choosing a better algorithm.
And it's hard for MySQL to add new algorithms because it doesn't have a cost-based optimizer, so it would have no idea which ones to choose.
[1] There are lots of other differences, but sometimes differences aren't easily represented as convincing "check-mark" features. So I really think you are asking the wrong question. The right question is: if I were making non-trivial application XYZ in postgres, how would it be done (from both dev and operational standpoint)? And is it easier to develop and smoother to operate if I do it that way?
Looking over the list here, I'd call out a few specific things that are important to me:
* HStore, the key-value store column type
* PGXN, still new, but going to change the world
* Replication that Actually Works For Real No Kidding.
* Window functions, which are a total brainf*ck but once you internalize them, indispensible.
