Horizontal Postgres Scaling

Question

Suppose you run your business on top of a postgresql database. After a while, you will get so much traffic that it cannot be processed with a 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 kind of solution with a key / value is not an option.

How would you do this with postgresql?

PS. Postgresql Version: 9.5

Answer 1

• When it comes to reading workloads, you should simply add replicas. Add as many replicas as needed to handle the entire workload. You can balance all requests for replicas in a circular mode.

• When it comes to write workloads, you must split your database into many servers. You can place different tables on different machines, or you can outline one table on many machines. In the latter case, you can outline the table with a range of primary keys or a hash of the primary key, or even vertically in rows. In each of the above cases, you may lose the transaction, so be careful and make sure that all data has been changed and requested by the transaction on the same server.