Multi-primary replication

Multi-primary replication, also called multi-master replication, is a database replication topology in which two or more nodes accept writes for the same data and propagate their changes to one another. Every node acts as both a source and a target of replication, unlike a primary-replica setup, where only one node may write.

The design improves write availability and lets applications write to a nearby node in each region, but it introduces a problem that single-primary systems avoid: conflicting writes to the same row on different nodes. How a system detects and resolves those conflicts, or prevents them, determines whether multi-primary replication is safe for a given workload.

How multi-primary replication propagates writes

In multi-primary replication, each node commits local transactions and then ships the resulting changes to its peers, usually as row-level change events derived from the write-ahead log or binary log. Peers apply incoming changes as if they were local, while making sure that a change is not echoed back endlessly, which systems prevent by tagging events with their origin node identifier.

Propagation can be asynchronous, where a node commits first and replicates afterward, or synchronous or certification-based, where a transaction commits only after the cluster agrees it does not conflict. The choice decides whether conflicts are resolved after the fact or rejected before commit.

The table below compares the main propagation approaches.

Conflict types and resolution strategies

A write conflict occurs when two nodes change related data concurrently without seeing each other’s change. Typical cases are two updates to the same row, an update to a row that another node deletes, and two inserts with the same unique key.

Last writer wins is the most widespread default because it is simple, but it discards data without warning and depends on synchronized clocks. Systems that cannot tolerate lost updates either reject conflicting transactions at commit time or avoid conflicts through careful data ownership.

Multi-primary compared with other replication topologies

Multi-primary replication trades simplicity and strict consistency for write availability. Its neighbors in the replication family make that trade-off differently.

Consensus protocols such as Raft elect a single leader per data range and require a quorum for each write, so they give strong consistency at the cost of cross-node latency. Asynchronous multi-primary gives the lowest local latency but accepts eventual consistency and possible conflicts.

Designing a workload for multi-primary replication

The most reliable way to use multi-primary replication is to design conflicts out of the workload. When each row has a natural home node, for example customers assigned to a region, concurrent writes to the same row become rare, and the replication layer mainly provides availability and local reads.

• Avoid shared auto-increment keys: use UUIDs, per-node key ranges or offset and increment settings so that inserts do not collide.
• Route each entity to one home node whenever possible, and write elsewhere only during failover.
• Keep transactions small, because large transactions increase the window for conflicts.
• Monitor replication lag and conflict counts as first-class metrics.
• Test failure scenarios, including network partitions, before relying on the topology in production.

Global constraints, such as unique usernames or account balances that must never go negative, are hard to enforce across asynchronous primaries. They usually need a single authoritative node or a consensus-based service.

Risks and common misconceptions

A frequent misconception is that adding primaries multiplies write capacity. Every node must still apply every other node’s writes, so total write throughput is bounded by what each node can replay; multi-primary mainly improves availability and locality, not raw write scaling.

Split-brain deserves special care: if a partition separates the nodes and both sides keep accepting writes, the divergence may be large by the time the link returns. Synchronous clusters protect themselves by letting only the majority partition continue.

A non-technical view

Multi-primary replication means that several copies of a database can all accept changes and keep sending those changes to each other.

Think of a shared family calendar kept in three houses. Anyone can add an appointment in any house, and the houses phone each other to stay in sync. This works well until two people book the same evening differently at the same time. Then someone must decide which booking stays. The key takeaway is that writing anywhere is convenient, but it needs clear rules for clashes.

A concrete example

A logistics company runs its shipment tracking database in two data centers, one in Europe and one in North America. Warehouse scanners in each region must keep working even if the transatlantic link fails, so the team chooses asynchronous multi-primary replication between the two sites.

Each shipment is assigned a home region, and scanners write only to their local node. Primary keys are UUIDs, so inserts never collide. Conflicts arise only when a shipment crosses regions, and a custom handler keeps the scan with the later physical event time. During a four-hour link outage, both sites continue operating, and after reconnection the nodes exchange their backlog. The outcome is uninterrupted tracking, with a handful of logged conflicts reviewed by the operations team.