If you’re reading this, it’s likely that you’re looking for a way to meet always-on requirements for your PostgreSQL database, where zero downtime maintenance and ultra high availability is a must. The key to solving this use case is active-active replication, where writes can occur on any instance in a database cluster and data is then replicated to all instances within the same cluster.

Your users expect instant access to data regardless of their geographic location or the time of day. When your database goes down or responds slowly, customer satisfaction plummets resulting in lost revenue.

Active-active replication can help organisations achieve resilience across multiple regions, design for multi-master architecture, or ensure business continuity when planning for disaster recovery.

What Exactly Is Active-Active Replication?

Active-active replication (also known as multi-master replication or MMR, and bi-directional replication or BDR) is a database architecture where multiple database nodes operate simultaneously, with each capable of handling both read and write operations. This stands in contrast to traditional active-passive setups, where only one primary node handles writes while standby servers remain in a passive state, ready to take over only if the primary fails.

In an active-active PostgreSQL environment, every node in your cluster is a full participant, enabling distributed writes across your entire infrastructure. Data changes made at any node are automatically replicated to all other nodes, maintaining consistency across the entire system.

How Does Active-Active High Availability Actually Work?

The magic behind active-active replication involves several key mechanisms:

1. Bi-directional Replication: Changes made on any node are captured and transmitted to all other nodes in the cluster. The pgEdge Spock extension is built upon the pgLogical open source project to bring functionality that ensures data integrity and optimal performance for a distributed Postgres cluster.

2. Conflict Prevention, Detection, and Resolution: When multiple nodes attempt to update the same data simultaneously, the system needs to be able to resolve conflicts. Using tools like snowflake sequences and the Spock extension to support data conflict evaluation strategies like last write wins based on timestamps, version vectors, or custom resolution logic ensures data consistency. This is particularly important when working with aggregates and running sums, where approaches like pgEdge’s conflict-free delta apply columns (a simplified form of CRDTs, or conflict-free resolution data types) can easily resolve conflicts.

3. Load Distribution: Incoming database requests are distributed across all nodes, balancing the workload and preventing any single node from becoming a bottleneck. With support for functionality offered by extensions like HAProxy and pgBouncer, pgEdge Postgres is ideal for users with high-activity applications.

4. Automatic Failover: If a node fails, the system automatically routes traffic to healthy nodes with minimal disruption to service. pgEdge Postgres and the Spock extension ensure that in the event of failover, nodes are synchronized and data is preserved. You can use the ACE (Active Consistency Engine) extension to verify that nodes have not diverged due to network downtime, node failure, or replication lag, and repair any tables that are damaged.

Although PostgreSQL isn’t natively an active-active database, pgEdge Distributed Postgres makes active-active replication possible by enhancing Postgres with supporting extensions with cluster health monitoring, automatic failover, and conflict detection and resolution.

Getting Active-Active Replication Out-of-the-Box

pgEdge provides native support for active-active replication and multi-master topologies alongside 100% community PostgreSQL with Spock, an open-source PostgreSQL extension. This means the pgEdge Platform is a fully open-source database solution that allows you to run applications at or near the network edge or across cloud regions, without worrying about vendor lock-in or the problems that accompany proprietary alternatives.

pgEdge keeps globally distributed data in sync by using smart conflict resolution and automatic DDL replication. When two nodes try to update the same data simultaneously, pgEdge will automatically:

● Pick the most recent update (last-writer-wins)

● Merge changes intelligently for things like counters and running totals

● Flag complex conflicts for human review when needed

As a result, your end users can update records independently (without breaking anything!) while the system quietly reconciles differences in the background. Essentially, Spock acts as a built-in mediator for your database nodes.

This approach addresses several challenges with standard Postgres deployments:

Streamlined configuration: pgEdge provides tools to simplify the setup and management of active-active databases to streamline the process and reduce the complexity traditionally associated with such deployments.

Advanced conflict resolution: pgEdge Platform includes sophisticated, automatic, and configurable conflict resolution mechanisms like conflict-free delta apply columns to avoid conflicts and ensure eventual data consistency across all nodes.

Performance optimisation: pgEdge improves replication performance with advanced conflict resolution mechanisms, strict data consistency cheques, and effective data distribution to minimise latency and maximise throughput.

Geographical distribution support: pgEdge replication nodes can be distributed across multiple cloud regions and/or vendors, making it ideal for global applications or cost optimisation.

Horizontal scaling support: Whereas PostgreSQL itself does well when vertically scaled, in many use cases it's helpful to take on a horizontal scaling approach (or even a combination between the two, as a hybrid solution). Spock delivers this functionality alongside PostgreSQL's native capabilities.

pgEdge installation is straightforward. After you’re up and running, you can create nodes, define replication sets, and establish subscriptions between nodes with either simple pgEdge commands:

-- Create a new cluster node
./pgedge cluster add-node demo n1 n4
-- Create a subscription
./pgedge spock sub-create sub_n1n2 'host=10.0.0.6 port=5432 user=rocky dbname=acctg' acctg

or SQL commands, such as:

-- Create a Spock node
SELECT spock.node_create(
node_name := 'node1',
dsn := 'host=localhost dbname=mydb'
);
-- Create subscriptions between nodes
SELECT spock.subscription_create(
sub_name := 'sub1',
provider_dsn := 'host=node2 dbname=mydb',
replication_sets := ARRAY['default']
);

What Else Does pgEdge Support?

While active-active replication offers significant advantages, it doesn’t suit every use case. pgEdge also supports other replication methods to fit different use cases:

Active-Passive: For applications that don't require the full capabilities of active-active, pgEdge supports traditional primary-replica configurations where one server handles writes while others serve as backups or read-only replicas.

Logical Replication: pgEdge enhances PostgreSQL's built-in logical replication capabilities, allowing for selective data replication at the table level.

Cascading Replication: This configuration allows data to flow through multiple levels of nodes, creating efficient replication topologies. Spock implements cascading replication with changeset forwarding.

When is an Active-Active Database Most Useful?

Overall, you’ll benefit most from a multi-master topology if you’re looking for low network latency, high availability, and low to zero downtime maintenance. Some examples of where these replication features are most useful include:

Applications requiring high levels of availability: Above all else, applications that depend on always-on infrastructure can benefit the most from distributed PostgreSQL. This can include instances where service downtime can cause significant negative consequences for a business, or could threaten the safety of end-users.

Global applications with distributed users: Having multiple primaries that are globally distributed allows traffic to be routed closer to respective end-users for faster performance and rendering.

High-volume e-commerce platforms: Online stores experiencing heavy traffic can distribute the load across multiple nodes to minimise latency and ensure availability of your online store (even when the system is experiencing high load).

Financial systems: Banks and financial institutions benefit from the real-time updates and always-on capabilities provided by pgEdge active-active replication.

Edge AI systems: As AI applications move from experimentation to production deployments, having AI decisions and intelligence closer to end users becomes more important, particularly for applications with globally distributed users.  pgEdge, when coupled with popular Postgres vector and AI extensions such as pgvector can significantly reduce latency in delivering results to users.

Applications with strict data residency requirements: When regulations or customer requirements require sensitive data to remain within specific geographic boundaries, pgEdge Postgres active-active replication supports strategic data placement while maintaining global accessibility. Data residency and data sovereignty concerns can be heightened in AI applications, making Edge AI architectures as referenced above even more attractive or necessary.

IoT applications: Systems collecting information from distributed IoT devices can process information more efficiently with strategically placed active nodes, especially when data residency is used to keep vast amounts of sensor data local.

When Not to Use Active-Active Replication

Of course, it’s not always sensible to use active-active replication methods. For example, if you have:

Applications with complex transaction logic: If your application relies heavily on complex, interdependent transactions that must maintain strict consistency, the eventual consistency model of an active-active implementation may cause unexpected behaviour.

Very small applications: If you have a simple application with limited traffic, you won’t see value that’s worth the added complexity of an active-active setup.

Get Started

Active-active replication provides a powerful foundation for creating highly available, geographically distributed PostgreSQL clusters. pgEdge combines the power of 100% standard PostgreSQL with extensions that enable safe and durable active-active replication support. We created this solution to help individuals overcome the traditional limitations of multi-master replication, improve performance, optimise costs, and design scalable, resilient infrastructure.

Want to experience pgEdge's distributed PostgreSQL solution for yourself? Get started using our cloud platform free for 30 days, or self-host the open sourced pgEdge platform.

Have any questions or need support? We’re here to help.