Master-Master vs. Master-Slave Replication in Distributed Relational Databases

Updated October 2026: Fixed the claim that replication is a backup, corrected the write-scaling and consistency claims, switched to current source/replica and primary/standby terminology, and added sync vs async replication, lag, failover and Rails multi-database setup.

In the distributed relational database model, two common models are master-master and master-slave replication. Let’s explore each of these models and when to use them.

A note on names. “Master/slave” is the old terminology and is still in the title of this post because that’s what people search for. Today:

• MySQL says source and replica. The new statements arrived in MySQL 8.0.22–8.0.26 (START REPLICA, SHOW REPLICA STATUS, CHANGE REPLICATION SOURCE TO), and MySQL 8.4 LTS removed the old START SLAVE / SHOW SLAVE STATUS / CHANGE MASTER TO statements entirely.
• PostgreSQL says primary and standby (a “hot standby” accepts read-only queries).
• “Master-master” is usually called multi-primary (or multi-source / active-active).

I’ll use the current names below.

I. Multi-Primary (Master-Master) Replication

Multi-primary replication, also known as master-master or multi-master replication, is a technique where two or more database servers each accept writes and propagate their changes to the others. Every node is both a source and a replica at the same time.

Key Features of Multi-Primary Replication

1. Bi-Directional Replication: Changes made on one server are replicated to the other servers. With asynchronous replication (the classic MySQL circular setup), the nodes are only eventually consistent: for a while, two nodes can return different answers for the same row. Systems that certify each transaction across the group (see below) narrow that window, but even MySQL Group Replication describes itself as an eventual consistency system where stale reads are possible on lagging members.

2. High Availability: If one node fails, the others can keep accepting writes, so there is no “promote a new primary” step.

3. Read Scaling, not Write Scaling: Reads can be spread across all nodes. Writes don’t scale the way people hope: every node still has to apply every write from every other node, so the total write work per node is the same as on a single server — plus the cost of coordinating. Multi-primary buys availability and locality, not write throughput. To scale writes you need to split the data (sharding/partitioning).

4. Conflict Resolution: When the same row is modified on two nodes at the same time, something has to decide who wins. Depending on the system, the loser is rolled back (and the application must retry), silently overwritten, or replication stops until a human fixes it.

5. Unique ID Collisions: Two nodes inserting into the same table can generate the same auto-increment id. In a MySQL source-to-source setup, give each server its own sequence with auto_increment_increment and auto_increment_offset:

   # Server A: ids 1, 3, 5, ...
   auto_increment_increment = 2
   auto_increment_offset    = 1
   
   # Server B: ids 2, 4, 6, ...
   auto_increment_increment = 2
   auto_increment_offset    = 2
   

   These settings apply to every AUTO_INCREMENT column on the server. UUIDs are another way out.

Multi-Primary Options Today

• MySQL Group Replication / InnoDB Cluster: Group Replication runs in single-primary mode by default; multi-primary mode is optional. Transactions are certified across the group before commit, and a conflicting transaction fails and is rolled back. Multi-primary mode has restrictions (e.g. SERIALIZABLE transactions and cascading foreign keys fail at commit). InnoDB Cluster packages it with MySQL Shell for administration and MySQL Router for routing, with a minimum of three instances.
• Galera Cluster (MariaDB Galera Cluster, Percona XtraDB Cluster): a multi-primary cluster with certification-based, “virtually synchronous” replication — any node accepts writes, and conflicting transactions are rolled back. See the Galera documentation.
• PostgreSQL: core PostgreSQL has no built-in multi-primary cluster. Logical replication can be set up in both directions (the origin = none subscription option stops changes from bouncing back and forth), but conflicts are not resolved for you: incoming changes overwrite local ones, and a unique-constraint violation stops replication until you fix it.

Use Cases for Multi-Primary Replication

• Multi-Data Center Deployments: Each region writes to a nearby node, as long as you can partition who writes what (e.g. users are “homed” in one region) to avoid conflicts.

• Highly Available Systems: Systems that cannot afford a failover step when a node dies.

If neither applies, a single primary is simpler and safer.

II. Primary-Replica (Master-Slave) Replication

Primary-replica replication (formerly master-slave) is a technique where one database server (the primary/source) propagates its changes to one or more replicas (standbys). The primary handles writes, while the replicas copy the data and serve reads.

Key Features of Primary-Replica Replication

1. Unidirectional Replication: Changes flow from the primary to the replicas only. Note that this is a convention you have to enforce:

   • A PostgreSQL hot standby is read-only by design.
   • A MySQL replica accepts writes from clients unless you turn on read_only (which is off by default). Users with CONNECTION_ADMIN can still write under read_only; super_read_only blocks them too. Replication threads keep applying changes either way.

2. Read Scalability: Offloading reads to replicas reduces the load on the primary.

3. Reporting and Analytics: Long-running reports can run on a replica without slowing down the primary.

4. Failover: If the primary dies, a replica can be promoted to become the new primary. Promotion is not automatic in plain MySQL or PostgreSQL replication; you need tooling (MySQL InnoDB Cluster/Router, or Patroni-style managers and managed services on PostgreSQL) or a runbook — and your application must be pointed at the new primary.

Use Cases for Primary-Replica Replication

• Read-Heavy Workloads: Most web applications read far more than they write.

• Reporting and Analytics: Generating reports, analytics, and data processing tasks without impacting the performance of the primary.

• High Availability: A hot standby ready to be promoted cuts recovery time from “restore a backup” to “promote a replica”.

III. Replication Is Not a Backup

This is the most important point of this post, and the first version got it wrong.

Replication copies every change, including the bad ones. If someone runs DELETE FROM orders; without a WHERE, or a migration drops the wrong column, that statement is replicated to every replica within seconds. Replication protects you against a server dying, not against data being destroyed.

For that you need real backups:

• Regular snapshots/dumps (mysqldump, MySQL Enterprise Backup / Percona XtraBackup, pg_dump, pg_basebackup).
• Point-in-time recovery (PITR): a base backup plus archived binary logs (MySQL) or WAL (PostgreSQL), so you can restore to the moment just before the bad statement.
• Backups stored somewhere a compromised or broken primary can’t delete them — and restores that you actually test.

A delayed replica (MySQL SOURCE_DELAY, PostgreSQL recovery_min_apply_delay) can give you a short window to catch a mistake, but it is a safety net, not a backup.

IV. Synchronous, Asynchronous and Semi-Synchronous Replication

How long the primary waits before telling the client “committed” decides how much data you can lose when it crashes:

Synchronous modes cost at least one network round trip per commit, which is why async remains the default. References: MySQL semisynchronous replication, PostgreSQL synchronous replication.

Replication Lag and Read-Your-Writes

With async replication, a replica is always a little behind — usually under a second, but much more during a big migration or a long-running query on the replica. The classic bug: a user saves their profile (write goes to the primary), the next page load reads from a replica, and their change “disappears”.

Common fixes:

• Read from the primary for a short time after the same user writes (this is exactly what Rails does, see below).
• Send reads that must be fresh (checkout, “my account”) to the primary.
• In PostgreSQL, synchronous_commit = remote_apply makes the commit wait until the synchronous standby has applied the change, so reads there see it — at the cost of slower commits.

V. Primary-Replica in Ruby on Rails

Rails (6.0+) supports multiple databases with automatic switching between a writer and a reader. In config/database.yml, mark the replica with replica: true:

production:
  primary:
    database: my_primary_database
    adapter: postgresql
  primary_replica:
    database: my_primary_database
    adapter: postgresql
    host: replica.example.com
    replica: true

Then declare the roles on ApplicationRecord:

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  connects_to database: { writing: :primary, reading: :primary_replica }
end

Run bin/rails g active_record:multi_db to generate an initializer for automatic role switching, and enable it:

Rails.application.configure do
  config.active_record.database_selector = { delay: 2.seconds }
  config.active_record.database_resolver = ActiveRecord::Middleware::DatabaseSelector::Resolver
  config.active_record.database_resolver_context = ActiveRecord::Middleware::DatabaseSelector::Resolver::Session
end

With this, GET/HEAD requests go to the replica, other requests go to the primary, and a user who wrote something within the last delay (2 seconds) keeps reading from the primary — read-your-own-writes for that user. Tune the delay to your real replication lag. You can also switch manually:

ActiveRecord::Base.connected_to(role: :reading) do
  Report.generate_monthly!
end

Rails does not load-balance across several replicas or handle failover itself; that belongs to your infrastructure (a proxy, DNS, or your managed database). See the Rails multiple databases guide.

VI. Conclusion

• Primary-replica is the default choice: simple, well supported, scales reads, and gives you a standby for failover. Watch out for replication lag.
• Multi-primary buys availability and write locality, at the cost of conflicts, harder consistency, and no real write scaling. Prefer a system that certifies transactions (Group Replication, Galera) over hand-built circular replication.
• Neither is a backup. Keep snapshots and point-in-time recovery, and test your restores.

By understanding these trade-offs, you can choose the right replication model for your distributed database architecture.