PostgreSQL Replication, High Availability HA and Scalability

In this course, we explore the task of creating scalable solutions based on PostgreSQL, using the resources of multiple servers. There is a natural limitation for such systems – you always have to make a compromise between performance, reliability, and consistency. You can improve one aspect, but others may suffer as a result. In this course, we will see how to find the best balance for our tasks, to precisely understand which aspects require scaling and how to avoid the typical trade-offs in distributed systems.

Scaling PostgreSQL is a journey. After this course, you will be better able to evaluate scaling needs, understand how to scale reading and how to scale writing.

Each solution presented in this course will improve a particular aspect of scalability, but each one will also add complexity and possibly some limitations.

To begin with, it's important to ask the right questions to understand the system requirements, which is why we've dedicated an entire session to examining what questions we should ask ourselves before embarking on the path to scaling.

After this course, you will be better prepared and understand how to scale reading.

We have several options for replication, depending on whether performance or flexibility is more important to us.

Replication can be used as a backup or as a failover solution that activates in case of a primary server failure.

Additionally, replication can improve system performance as it allows distributing the load across multiple database servers.

Next, if one form of replication is set up, you might consider having several computers serve the same data.

For this, a query distribution mechanism is needed. We will explore two of the most popular available options here.

Furthermore, if the number of database connections is high, you will likely need a connection pool. We will also consider two options here.

We will also study how to scale writes and make traffic growth more predictable by adding queues to the architecture.

After this, we will look into partitioning for cases where it is necessary to handle large tables.

We will also study sharding for scaling writes and all the complex decisions associated with it.

Finally, we will briefly look into the multi-master solution, which is a relatively new concept but looks promising.

If our goal is simply high availability (HA) or the ability to continue operation even in the event of a failure of one of the servers in the cluster, we can consider only those solutions that are suitable for these tasks.

To ensure high availability, it's necessary to set up a replication strategy.

Then, tools that allow the standby server to quickly take over the load if the primary server fails can be used.