Scaling Postgres horizontally

Let we say you are running your business on top of postgresql database. After some time you get so much traffic that it cannot be handled by single postgresql instance, so you want to add more instances (scale it horizontally) to be able to handle growth.

Your data is relational, so probably switching to some key/value solution is not an option.

How would you do it with postgresql?

PS. Postgresql version: 9.5

1. If it is about read-heavy workload then you should just add replicas. Add as many replicas as you need to handle the whole workload. You can balance all the queries across the replicas in the round robin fashion.

2. If it is about write-heavy workload then you should partition your database across many servers. You can put different tables on different machines or you can shard one table across many machines. In the latter case you can shard a table by a range of the primary key or by a hash of the primary key or even vertically by rows. In each of the cases above you may lose transactionality, so be careful and make sure that all the data changed and queried by a transaction be resided on the same server.