Hot Standby in postgres is pretty damn good for replication (like you said making failover occur properly takes a little bit of effort but its not too hard).

Also pgpool2 helps a lot with partitioning and parallel queries.

For a relational db, postgres does a pretty good job at distribution and fault tolerance.