Before we delve into the main subject of this blog, it is essential to understand the benefits of PostgreSQL replication, and the difference between single-master replication (SMR) and multi-master replication (MMR). In every modern business application, the database is becoming a critical part of the architecture and the demand for making the database performant and highly available is growing tremendously.

Planning Ahead for Better Performance

Our goal when designing a system for high performance is to make the database more efficient when handling an application request - this ensures that the database is not becoming a business bottleneck. If your database resides on a single host, the resources of the system that is hosting the database can be easily exhausted; having a system that supports scaling the database so it can more effectively respond to the application's heavy load.

With pgEdge Distributed Postgres and the power of PostgreSQL, you can perform both horizontal and vertical scaling:

  • Vertical scaling is the ability to add more power to a single server that is hosting your database.

  • Horizontal scaling is distributing your database across multiple machines (for example, sharding or partitioning the database).

The technique of replicating data across multiple PostgreSQL databases that are running on multiple servers can also be considered horizontal scaling. The data is not distributed, but database changes are replicated to each cluster node so the application load can be divided across multiple machines to achieve better performance.

Reliability and high-availability are also crucial for a powerful and responsive system:

  • Reliability means that the database is able to respond to user/application requests at all times with consistency and without any server interruption.

  • High-availability is also a critical consideration that ensures that database operations are not interrupted and the database downtime is minimized.

Statistically, downtime per year reflects the ability of your database and application to handle failures and outages without user downtime. Often, downtime per year is negotiated into a service level agreement (SLA) for applications that require high-availability; this clause specifies the cumulative length of time within that year that the database can be down. To minimize downtime, pgEdge can actively replicate the same data to each node in the cluster. Components that handle failover and query routing are also used to ensure that the database remains highly available under stress.

PostgreSQL provides two methods of replication: asynchronous and synchronous.

  • If you are using asynchronous replication, data is written to the primary server first and then it is replicated to other database nodes without waiting on confirmation from each replicated node that the data has been written.

  • If you are using synchronous replication, data is written to primary and replica nodes simultaneously.

There are tradeoffs between asynchronous and synchronous replication.  Synchronous replication is safer for critical data or high-end transactional workloads that require resiliency. Asynchronous replication is suitable for most workloads, but failover might take longer when compared to a synchronous replication configuration, and there might be some risk of data loss if all the changes are not replicated to all nodes.

In this summary, we've defined the terms used to describe replication in a PostgreSQL database. Lets now delve into the two deployment models for PostgreSQL replication.

Single-Master Replication

single-master-replicationA single-master replication model consists of one primary node and one or more secondary nodes. In this model, write transactions are only sent to the primary node while read transactions are sent to both primary and secondary nodes. The secondary nodes (read-only  replicas) are used to handle query requests that don't modify data. This scenario employs middleware products (like HAProxy) that sort the write and read requests between the primary and secondary nodes. In the event of a failure, the secondary node is promoted to become a primary node with automated failovers handled by products like Patroni and Pgpool. When a failover completes, the middleware (HAproxy) is updated to ensure that writes are sent to the new primary node.

Multi-Master Replication and Conflicts

The multi-master replication deployment model consists of multiple nodes that act as the primary (or master) node. Each node is performing active-active replication between each secondary node; in an MMR cluster, client applications can perform both write and read operations against any node in the cluster. This configuration employs shared-nothing architecture without a coordinator node.

You can configure single-master replication using only native PostgreSQL tooling, but multi-master replication capabilities must be provided by companies like pgEdge. pgEdge provides a fully distributed and 100% PostgreSQL based cluster with benefits like low latency for high performance, selective filtering for data residency, and conflict resolution. Once configured, a pgEdge MMR cluster enables a client application to send write commands to all of the nodes in the cluster. It's worth noting that multiple clients updating the same record concurrently can lead to conflicts that are handled by the conflict-resolution solution provided by pgEdge.multi-master-replicationDuring active-active replication, synchronization of data between nodes can cause a conflict if changes are applied to the same row on multiple nodes concurrently by more than one client session. A conflict can occur even if the transactions causing the problem take place in a different timestamp; the conflict will arise when replicating the changes to synchronise the nodes.

Different types of transactions will cause different types of conflicts in a MMR replication scenario; this will help you get a better understanding of MMR conflicts:

  • INSERT-INSERT conflict: This conflict occurs when multiple clients try to insert the same primary key or second unique key on multiple nodes at the same time. This causes a unique key violation error resulting in a conflict and breaking replication.

  • UPDATE-UPDATE conflict: This conflict occurs when multiple clients try to update the same row on multiple nodes at the same time. As I mentioned above, the transactions causing the conflict may have slightly different timestamps, so a conflict will arise when two nodes try to synchronize when replicating the changes.

  • DELETE-DELETE conflict: The DELETE-DELETE conflict arises when the same row is deleted from multiple nodes. When the delete transaction is performed on one node, the incoming delete transaction from the second node will not find the record, causing the conflict.  

Conflict Detection and Resolution

From the PostgreSQL documentation:

“Logical replication behaves similarly to normal DML operations in that the data will be updated even if it was changed locally on the subscriber node. If incoming data violates any constraints the replication will stop. This is referred to as a conflict. A conflict will produce an error and will stop the replication; it must be resolved manually by the user. Details about the conflict can be found in the subscriber's server log”

The MMR solution from pgEdge provides a solution for detecting and resolving conflicts without breaking replication between nodes. In the examples that follow, UPDATE-UPDATE conflicts are used to demonstrate how pgEdge platform detects and resolves issues automatically without impacting replication. pgEdge platform utilizes an open source extension named Spock that provides MMR capabilities with automatic DDL updates, conflict detection/resolution, and more.

In our example, we are going to use a 3 node pgEdge cluster that is running on localhost on different ports. The spock.node table below displays the nodes in the cluster.

testdb=# select * from spock.node;
node_id | node_name | location | country | info
---------+-----------+----------+---------+------
49708 |   n1   |       |       |

26863 |   n2   |       |       |

9057  |   n3   |       |       |

We have created the employee table shown below, and used automatic DDL replication functionality from the Spock extension to replicate it across our cluster.

testdb=# \d employee
Table "public.employee"
Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
emp_id   | integer |           | not null | 
emp_name | text    |           |          | 
emp_mgr  | integer |           |          | 
emp_dept | integer |           |          | 
Indexes: "employee_pkey" PRIMARY KEY, btree (emp_id)

 psql -p 5432 -U ahsan -d testdb -c "update employee set emp_name = 'Shuja Hadi' where emp_id = 100;" & psql -p 5433 -U ahsan -d testdb -c "update employee set emp_name = 'Ahsan Hadi' where emp_id = 100;" & psql -p 5434 -U ahsan -d testdb -c "update employee set emp_name = 'Fahar Abbas' where emp_id = 100;" &

The above command spawns three psql sessions in the background and tries to update the employee name for the same row on all three nodes; this could potentially result in an UPDATE-UPDATE conflict. The conflict is resolved automatically by pgEdge Spock extension, by applying the commit with the latest timestamp hence using a last update wins strategy.

testdb=# select * from spock.resolutions;

id                  | 2
node_name           | n1
log_time            | 2025-03-28 16:44:54.338552+05
relname             | public.employee
idxname             | employee_pkey
conflict_type       | update_update
conflict_resolution | keep_local
local_origin        | 0
local_tuple         | {"emp_id":100,"emp_name":"Ahsan Hadi","emp_mgr":100,"emp_dept":20}
local_xid           | 797
local_timestamp     | 2025-03-28 16:44:54.334886+05
remote_origin       | 26863
remote_tuple        | {"emp_id":100,"emp_name":"Fahar Abbas","emp_mgr":100,"emp_dept":20}
remote_xid          | 794
remote_timestamp    | 2025-03-28 16:44:54.334852+05
remote_lsn          | 0/75566900

Exception Logging

The pgEdge distributed Postgres Spock extension provides exception logging that logs the errors that are encountered while trying to apply changes at the replication subscriber. Exception logging ensures that replication between nodes isn't broken due to the errors caused by applying the replication changes.

The examples below cause an INSERT-INSERT conflict by inserting the same value in the primary key column from multiple psql clients. The duplicate key violation error is captured in the exception log, and replication continues to function without any interruptions.

psql -p 5432 -U ahsan -d testdb -c "insert into employee values (400,'Ahsan Hadi',100,20);" & psql -p 5433 -U ahsan -d testdb -c "insert into employee values (400,'Fahar Abbas',100,20);" & psql -p 5434 -U ahsan -d testdb -c "insert into employee values (400,'Shuja Hadi',100,20);" &

testdb=# select * from spock.exception_log ;
remote_origin    | 9057
remote_commit_ts | 2025-04-02 13:12:27.164555+05
command_counter  | 1
retry_errored_at | 2025-04-02 13:12:32.223302+05
remote_xid       | 804
local_origin     | 
local_commit_ts  | 
table_schema     | public
table_name       | employee
operation        | INSERT
local_tup        | 
remote_old_tup   | 
remote_new_tup   | [{"value": 400, "attname": "emp_id", "atttype": "int4"}, {"value": "Shuja Hadi", "attname": "emp_name", "atttype": "text"}, {"value": 100, "attname": "emp_mgr", 
"atttype": "int4"}, {"value": 20, "attname": "emp_dept", "atttype": "int4"}]
ddl_statement    | 
ddl_user         | 
error_message    | duplicate key value violates unique constraint "employee_pkey"

The example that follows causes a DELETE-DELETE conflict while deleting the same record from multiple psql clients. The error occurs because during synchronization of nodes, the row to be deleted is missing on some nodes; this error is captured in the Spock exception log table without causing any interruption to the replication between nodes.

psql -p 5432 -U ahsan -d testdb -c "delete from employee where emp_id=400;" & psql -p 5433 -U ahsan -d testdb -c "delete from employee where emp_id=400;" & psql -p 5434 -U ahsan -d testdb -c "delete from employee where emp_id=400;" &

testdb=# select * from spock.exception_log ;
remote_origin    | 9057
remote_commit_ts | 2025-04-02 13:15:06.308024+05
command_counter  | 1
retry_errored_at | 2025-04-02 13:15:07.342374+05
remote_xid       | 808
local_origin     | 
local_commit_ts  | 
table_schema     | public
table_name       | employee
operation        | DELETE
local_tup        | 
remote_old_tup   | [{"value": 400, "attname": "emp_id", "atttype": "int4"}, {"value": null, "attname": "emp_name", "atttype": "text"}, {"value": null, "attname": "emp_mgr", "atttyp
e": "int4"}, {"value": null, "attname": "emp_dept", "atttype": "int4"}]
remote_new_tup   | 
ddl_statement    | 
ddl_user         | 
error_message    | logical replication did not find row to be deleted in replication target relation (public.employee)

Spock's exception logging ensures that replication between nodes doesn’t fail when a discrepancy is encountered while trying to replicate changes to a node. The above examples demonstrate how conflicts are captured in the exception log table without causing any interruption to the replication. This allows you to review issues at a time that is convenient for you.

Conclusion: Single and Multi-Master Pros & Cons

Choosing between a single-master or multi-master solution boils down to your requirements, your workload, and how your customer base is distributed. Both approaches have their pros and cons; what you choose for your business should be driven by your business requirements.

Multi-master replication in PostgreSQL allows multiple nodes (servers) to act as both READ-ONLY nodes  and WRITE nodes. Here are some key reasons why you might choose multi-master replication with pgEdge PostgreSQL:

  • High-Availability

    - Failover Capability: In an MMR setup, if one node goes down, the other nodes can continue the read/write operations without hindering the availability of your application.

    - Load balancing: The read and write requests can be distributed across all of the nodes in your cluster.

  • Scalability: Multi-master replication allows you to add more nodes to a cluster as your workload increases. Each node can handle both read and write queries, which means you can horizontally scale the database more easily than with traditional single-master replication.

  • Geographic distribution/Low latency: Multi-master replication is beneficial in globally distributed systems where you might have PostgreSQL instances in different geographic locations. It reduces the need for a single central master, which can lead to latency issues for remote users. Putting the data closer to the end user reduces latency and provides higher performance.   

  • No single point of failure: In a typical single-master replication scenario, the master node is a single point of failure. If the master node goes down, no writes can be made to the database until the master is back online or a failover process takes place. Multi-master replication removes this bottleneck, as any node can act as a master, and the failure of one node does not prevent the system from writing data.

  • Better disaster recovery: Since all nodes are capable of handling writes and have the full copy of the data, the system can more easily recover from disasters, even if a specific node fails. The other nodes contain the same data and can take over without a significant loss of service.

In conclusion, multi-master replication offers significant advantages in terms of availability, scalability, and flexibility, but it does require careful planning, implementation, and management due to its complexity, especially when dealing with data conflicts. It's an excellent choice for applications that require high availability, fast read/write performance, and geographic distribution. However it has the following caveats

  • Complexity: MMR is more complex to deploy and maintain, and requires strategy for handling conflicts.

  • Data discrepancies: Cluster nodes can develop data discrepancies if conflicts arise that aren’t handled automatically; schema changes that aren’t propagated to all the nodes and incoming traffic can cause node synchronization issues.

  • Network overhead/replication lag: Nodes in an MMR cluster are resilient enough to be geographically distributed. An MMR cluster doesn't lose data/system integrity if traffic between nodes is delayed due to latency issues; other nodes can cover for the missing node until a temporarily interrupted node can catch up.

In a single-master setup you have one master node that accepts read and write operations, and one or more replica nodes that only accept read operations. Single-master replication can have the following pros and cons.

Pros :

  • Simple architecture/setup: A single-master replication cluster is easy to setup and maintain.

  • High Availability: In the event of a failover, an SMR cluster promotes the read replica to the new master.

  • Read scalability: WRITE transactions are performed only on the master node; READ transactions can be distributed to replica nodes.

  • Data consistency: Due to having a single write node, there are no conflicts (so the master node is the only source of truth).

Cons :

  • Single point of failure; if a master node is compromised, WRITE transactions are interrupted until a secondary node is promoted.

  • WRITE transactions are performed only on the master node, so are not scalable.

The pgEdge distributed PostgreSQL platform installs the tooling required to successfully manage a distributed MMR PostgreSQL solution.