PostgreSQL is the PAST and it will forever remain there until it solves its biggest problems.
It is the hardest out of all databases I've used to cluster, replicate, shard and manage. And the database world is moving towards scaling horizontally rather than vertically. I can push a button in say CouchDB to replicate and shard. Try doing that in PostgreSQL.
"And the database world is moving towards scaling horizontally rather than vertically."
I think that's an oversimplification. Two counterpoints:
* Database systems will always need to make heavy use of locality. The speed of light means that synchronizing access over long distances (even medium distances -- light only goes about a foot per nanosecond) will always be a challenge.
* Multi-core means vertical scaling is back in (if by "vertical scaling" you mean "scaling on one box"), and probably for a while.
Postgres is doing an excellent job at both.
I do agree with the less-exaggerated point that postgres really needs to improve its multi-machine scaling. But it's far from a solved problem on any system under discussion.
I think you're referring to postgres's inability to use more than one core per query, which is true (or mostly true... there are quite a few helper processes that take on some of the work).
For many smaller queries, postgres does great on multi-core, and pgbouncer is a good connection pooler.
People still use SQLlite. Why? Because its suits a specific set of circumstances.
Postgres does the same---its an incredibly powerful database with a large suit of features that make application development both easy and sane. To the vast majority of projects where you won't outgrow a single server, it makes sense to use it.
It is the hardest out of all databases I've used to cluster, replicate, shard and manage. And the database world is moving towards scaling horizontally rather than vertically. I can push a button in say CouchDB to replicate and shard. Try doing that in PostgreSQL.