What is Multi-Master Distributed Postgres, and Do You Need It?

As a Postgres developer, you know the importance of having a robust, scalable, and highly available database solution that can handle a large volume of read/write traffic. That's where distributed Postgres multi-master, also known as active-active, comes into play. Until recently some users were concerned about the overall scalability across multiple locations, stability caused by conflicts and cost of non-standard solutions. 

At its core, multi-master distributed PostgreSQL allows you to have multiple master databases spread across different locations (multiple nodes), each capable of handling read and write traffic simultaneously, allowing for better performance and high-availability of your applications. It also ensures data consistency (i.e. eventual data consistency) and improved data access times for your applications by using bi-directional replication and conflict resolution.

But what does that all mean, and how does it work?

What happens when two or more users try to update the same row or record simultaneously? That's where built-in conflict resolution is important. A postgres multi-master approach provides a robust conflict resolution mechanism that ensures that all masters have the same data. Timestamp-based conflict resolution ensures that no data is lost during an update, and guarantees database consistency.

Logical multi-master replication can encounter conflicts when maintaining a running sum (such as a YTD balance). Suppose that a bank account contains a balance of $1,000. If two withdrawals come in for the account from different nodes (for example, if transaction A is a $1,000 withdrawal from the account, and transaction B is also a $1,000 withdrawal from the account), the transactions will be in conflict. If both transactions are honored, the resulting account balance will be $-1,000. pgEdge’s delta-apply algorithm resolves the collision, and avoids workload conflicts like this scenario (like a Transaction Processing Performance Council Benchmark C (TPC-C) benchmark) so transactions are processed correctly at lightning speed.

You can also use the pgEdge platform to perform a Blue-Green upgrade between major versions of Postgres with little to no downtime. During a Blue-Green upgrade, the 'blue' nodes of the database remain available to your users, while you perform an in-place server upgrade of the 'green' node. When the green node is ready, database traffic transitions from the blue nodes to the green node, leaving the blue nodes ready for upgrade at your convenience. This is active-active at its best.