Tineye.com, for those who have heard of it, also uses PostgreSQL (I set it up). It is mostly a KV store with a metadata join, but it's +1.5 billion rows returning +10 random uncached rows in <100ms.
Originally we used MySQL, which revealed its true face at ~500 million rows. Queries that were <300ms suddenly turned into 2-3 minutes because the query planner decided it would be fun to do a full scan or somesuch. Migrating to PostgreSQL not only reduced the query time of the same queries by more than half but also allowed us to scale the number of rows pretty much linearly with a small constant.
Then I did some horizontal partitioning and things got really awesome.
Skype uses PostgreSQL for their VOIP services.
Yahoo! has a customized PostgeSQL for their data warehousing storing a couple of petabytes of data.
I believe IMDB is another quite prominent user.