Scaling Postgres horizontally

database postgresql horizontal-scaling
user232343 picture user232343 · Jan 16, 2016 · Viewed 25.1k times · Source

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

Answer

Dennis Anikin picture Dennis Anikin · Jan 17, 2016

  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.

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
What is this Javascript "require"?

I'm trying to get Javascript to read/write to a PostgreSQL database. I found this project on github. I was able to get the following sample code to run in node. var pg = require('pg'); //native libpq bindings = `var pg = …

Read More
Import SQL dump into PostgreSQL database

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site. Now, I'm trying to set this up on a local WAMP server to test this. The only problem is that I …

Read More