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

Free Platform DownloadFree Cloud Sign upFree Whitepaper

database developers looking at screen

New approaches bolster effectiveness of Postgres multi-master (active-active) database architecture

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?

diagram showing bi directional replication between 3 Postgres database nodes

Traditional bi-directional replication

One of the key features of multi-master Postgres is bi-directional replication. This allows you to replicate data between multiple instances in real-time, ensuring that data is always up-to-date across all instances. Any changes made to one master database are replicated to all other masters in real-time. This ensures that all master databases are kept in sync with each other. Additionally, with bi-directional replication, you can ensure that conflicts are avoided or resolved, ensuring that your data remains consistent.

Diagram showing PostgreSQL conflict resolution

Traditional multi master conflict resolution and avoidance

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 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.

Screen shot of multi-master and multi-region PosgreSQL database

How pgEdge delivers multi master distributed Postgres across locations

pgEdge addresses previous concerns by providing a flexible and scalable high-availability solution for PostgreSQL databases by synchronizing multi-master bi-directional replication across multiple databases and locations. This is done through pgEdge's Spock extension that allows you to create a multi master replication solution for your PostgreSQL database.

Spock uniquely provides a true replication architecture that ensures that your users can read and write transactions on any master node simultaneously. The extension captures all conflict resolution events and stores them in a Postgres table, allowing you to see all conflict resolutions in a central location. To learn more about Spock, read the blog.

multi master conflict avoidance

pgEdge conflict avoidance with conflict free delta-apply

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.

multi master map with people in different locations

pgEdge distributed server model

The pgEdge platform provides asynchronous multi-master replication with conflict resolution that allows your users access to up-to-date data hosted on multiple servers in multiple regions safely and efficiently.  pgEdge improves database performance and cost by allowing you to host information on servers that are geographically closer to the user, while maintaining data consistency across a distributed database.

If required, personal identifying information (pii) data can be restricted or managed according to the rules of the country in which the data resides to conform to General Data Protection Regulation (GDPR) or Health Insurance Portability and Accountability Act (HIPAA) requirements. This capability can be used in conjunction with granular replication sets (see below) to address data residency requirements.

multi master blue green databases balancing

pgEdge enables blue-green upgrades with little to zero downtime

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.

Distributed PostgreSQL column filtering 4x

pgEdge granular replication sets meets data residency requirements

The pgEdge platform also allows you to refine the replication set to include only the data that you need to replicate. This is critical in addressing data residency needs such as international regulations that require data created by citizens of a specific region or country to be stored within that respective region or country.

You can replicate global data globally while keeping the required local data local. It can also save considerable time and money that would be otherwise spent storing extra information. Instead of replicating an entire database, you can selectively replicate individual columns, rows, or entire tables on either the publisher or subscriber node. pgEdge also supports robust and granular replication for partitioned tables.

Conclusion

In summary, multi-master (active-active) distributed Postgres is powerful enough to allow you to scale your database horizontally across multiple locations while ensuring high availability and data consistency. With the pgEdge Platform (which includes the Spock extension), you can achieve true multi-master (active-active) replication and capture all conflict resolution events, providing you with a robust and scalable database replication solution. With pgEdge, you can achieve faster read and write performance, improved scalability, and better data consistency.

Additional Multi-Master Resources

21 Ways Multi-Master Distributed Postgres Improves Costs and Customer Loyalty Whitepaper

21 Ways Multi-Master Distributed PostgreSQL Improves Costs and Customer Loyalty

Featuring a fully open, fully standard approach

PostgreSQL databases need to be always available globally, but traditional architectures can cause latency issues. Multi-master logical replication databases offer technical benefits like improved latency and high availability, benefiting various industries by cutting costs and enhancing customer satisfaction.

Download Whitepaper

Your Choice

Self-hosted or Cloud deployment
pgEdge Cloud
  • Fully managed Database-as-a-Service (DBaaS)

  • Handles provisioning, security and monitoring

  • Access via web dashboard, CLI and API

  • Multi-cloud support available for AWS, Azure and Google Cloud

Get Started for Free
pgEdge Platform
  • All features of pgEdge Distributed PostgreSQL

  • Self-host on-premises or in cloud accounts (AWS, Azure, GCP, Equinix Metal, Akamai Linode)

  • For developer evaluations or production usage

  • Enterprise support available

Dive deeper into pgEdge

dive-img

Enhancing pgLogical with Multi-Master (Active-Active) Features

dive-img

Distributed PostgreSQL Buyer's Guide: How to evaluate vendor claims of Postgres compatibility

dive-img

Unlocking PostgreSQL 16 New Features

Get started today.

Experience the magic of pgEdge Distributed PostgreSQL now.