From PostgreSQL wiki
Jump to navigationJump to search

PostgreSQL supports unidirectional master-slave replication. Moreover, it supports hot standby mode in which it is possible to execute read-only queries at replicas. Replication can be either asynchronous or synchronous, but even in the case of synchronous replication there is a time gap between primary and standby, so the client executing read-only query at standby may not see changes it has performed in the previous transaction at primary. Also, prior to PostgreSQL 9.6 only one synchronous standby was supported, so it was practically not possible to use synchronous replication for load balancing. As a result, current streaming replication in PostgreSQL provides only fault tolerance (HA), but not scaling performance.

2ndQuadrant provides Postgres-BDR®, a multi-master technology stack. In this case, updates can be performed at any node of the cluster and then propagated to other nodes. BDR is essentially asynchronous: changes are applied at nodes some time later after committing transaction at master and various ways of resolving conflicts are proposed. BDR is fast (provides almost the same speed as hot standby), and provides the option to choose between 'eager' and 'efficient' consistency modes.

BDR is based on a new PostgreSQL feature named logical decoding. Changes are extracted from WAL and are followed by the logical output plugin. It can then apply these changes to some other database, save in log, or do whatever else it likes. BDR uses logical replication to deliver changes to other nodes. Logical replication is part of PostgreSQL since v9.5. The multimaster is based on the pglogical_output plugin provided by 2ndQuadrant. Implementation of the receiver part for this plugin has been made, which is also partly based on the BDR code. At the receiver side, we have a pool of background workers that concurrently apply changes received from remote walsender.

From a client's point of view, it works just with a set of identical PostgreSQL instances. It can login and send queries to any of them. It does not mean whether it is read-only or update query. But certainly, as far as updates have to be applied to all nodes, multimaster can provide scaling only for read-only queries.

The diagram below shows the performance results of multimaster installed at three node clusters. We run our dtmbench benchmark, varying percent of updates. We compare results of multimaster with the performance of standalone PostgreSQL. Providing ACID properties for distributed transactions adds essential overhead: multimaster is about 2 times slower on updates than a single node. In the case of asynchronous replication, it is possible to get much better results but without global consistency. At mostly read-only workloads multimaster provides much better performance, but still, there is on linear scalability.

Rw ratio.png



Vertical axis: TPS, thousands

Horizontal axis: number of client connections