So for one reason or another you have decided that you need horizontal scalability and you need to achieve it with PostgreSQL as your database. So what options do you have?

The PostgreSQL database supports vertical scalability and can run on bigger and faster machines to increase the performance. But when it comes to horizontal scalability, it does not have many options. However it does provide some truly feasible options to achieve that. 

Let’s see what these options are and how much they can help?

1- Read Scalability

So if your need for horizontal scaling your database is just to increase the read performance, then you are in luck. PostgreSQL has a very efficient built-in replication system that can be utilized to provide such scalability. This replication feature is based on a single primary multi-replica’s system.

In this system, all you need is to configure one or more replica nodes as you see fit and set up a load balancer to distribute the read queries to these nodes.

2- Read-Write Scalability

Well, if your solution relies on the read-write scalability then you have to be prepared for some headache. PostgreSQL is not a distributed database engine by design. However there are still some options that one can utilize to achieve the write scalability such as data shards.

Sharding in postgres relies on the table partitioning and postgre FDW’s (foriegn data wrappers). Currently postgres also supports declarative partition, so it has become somewhat easier to set up. In this setup, each partition can be put on a different machine. The system knows how to access the data in a seamless and transparent way.

Logical Replication is another feature of PostgreSQL that can be utilized for the read-write scalability. In this setup only a subset of the database can be replicated such as select tables. It also allows the data to flow in multiple directions, hence making it possible to have multiple nodes receive the writes and reads.

Other then these option, there are few other solution available as well that are built to provide horizontal scalability like:

• Postgres-XL
  Postgres-XL is an open source project to provide both write-scalability and massively parallel processing transparently to PostgreSQL. It can be configured as many database servers as you want and handle many more writes. You can have more than one database server that provides a single database view. Any database update from any database server is immediately visible to any other transactions running on different servers
• Citus
  Citus is also another open source project that provides horizontal scalability. It’s implemented as postgres extension that distributes data and queries across multiple nodes in a cluster.

Asif Rehman

Asif Rehman is a Senior Software Engineer at HighGo Software. He Joined EnterpriseDB, an Enterprise PostgreSQL’s company in 2005 and started his career in open source development particularly in PostgreSQL. Asif’s contributions range from developing in-house features relating to oracle compatibility, to developing tools around PostgreSQL. He Joined HighGo Software in the month of Sep 2018.