Database Replication and Its Types in System Design

Database replication means keeping copies of the same data on several database servers, so that every change made on one server is also applied to the others. Replication keeps your application running when a server fails, lets you spread read traffic across machines, and can place data closer to users in different regions.

This tutorial explains why replication is used, how changes travel from one server to another, the three main replication types (single-leader, multi-leader and leaderless), synchronous versus asynchronous replication, the problems caused by replication lag, and how failover works. You will also see how to send reads to replicas in a Spring Boot application and how replication is set up in PostgreSQL.

1. What Is Database Replication?

Replication is the process of copying data from one database server to one or more others and keeping those copies up to date. Each server that holds a copy is called a replica (or node). The server that accepts a change first and passes it on is usually called the leader or primary, and the servers that receive changes are called followers, secondaries or read replicas.

You will also see the older terms "master" and "slave" in documentation. Most projects now use primary/replica or leader/follower instead.

Replication is not the same as a backup:

• Replication copies every change within milliseconds or seconds. It protects you from server failure.
• A backup is a point-in-time snapshot kept separately. It protects you from mistakes and corruption. If someone accidentally deletes a table, replication faithfully deletes it on every replica too. Only a backup can bring it back.

2. Why Replicate a Database?

• High availability: if the primary crashes, a replica can take over, so the application keeps working instead of going down until the server is repaired.
• Read scalability: most applications read far more than they write. Spreading reads across several replicas multiplies read capacity.
• Lower latency for global users: a replica in Europe serves European users faster than a server in India.
• Disaster recovery: a replica in another data centre or region keeps your data safe if an entire site is lost.
• Isolating heavy work: analytics queries, reports and backups can run on a dedicated replica without slowing down the production primary.
• Zero-downtime maintenance: you can upgrade or patch one server at a time while others keep serving traffic.

3. How Replication Works

Databases already record every change in a log so they can recover after a crash. Replication reuses that idea: the leader sends its stream of changes to followers, and followers apply the same changes in the same order.

There are several ways to describe "a change":

For example, PostgreSQL's streaming replication ships its write-ahead log (WAL), and its logical replication publishes row changes. MySQL replicates using its binary log (binlog), usually in row-based format. Logical change streams are also what Change Data Capture (CDC) tools read to feed search indexes, caches and data warehouses.

4. Single-Leader (Primary-Replica) Replication

This is the most common type. One server is the leader. All writes go to it. The leader records each change and streams it to one or more followers. Clients can read from the leader or from any follower.

Strengths:

• Simple to understand and operate; supported by virtually every database.
• No write conflicts, because only one server accepts writes.
• Reads scale by adding more replicas.

Weaknesses:

• Write capacity is limited to what one leader can handle.
• If the leader fails, writes stop until a follower is promoted.
• With asynchronous replication, replicas may serve slightly old data.

Examples: PostgreSQL streaming replication, MySQL source-replica replication, MongoDB replica sets, and managed services such as Amazon RDS read replicas.

5. Multi-Leader Replication

In multi-leader (also called master-master or active-active) replication, more than one server accepts writes. Each leader sends its changes to the others. This is typically used across data centres: each region has its own leader, so users write to a nearby server.

Strengths: low write latency for users everywhere, and each region keeps working even if the link between regions fails.

The big challenge is write conflicts. If a user in Mumbai and a user in Frankfurt edit the same record at the same moment, each leader accepts a different value. Common ways to handle conflicts:

• Avoid them: route all writes for a given record (for example, one customer's data) to the same "home" leader.
• Last write wins (LWW): keep the change with the latest timestamp. Simple, but the other change is silently lost, and clocks on different servers are never perfectly in sync.
• Merge: combine both changes, for example by keeping all items added to a list.
• CRDTs: data types designed to merge automatically, such as counters and sets.
• Custom logic: store both versions and let the application or user decide.

Multi-leader replication is also how offline-first apps work: each phone or laptop acts as a leader while offline and syncs later. Use it only when you truly need it, because conflict handling adds significant complexity.

6. Leaderless Replication

In leaderless replication, there is no special leader. A client (or a coordinator node acting for it) sends each write to several replicas at once and each read to several replicas at once. This style was popularised by Amazon's Dynamo paper and is used by Cassandra, ScyllaDB and Riak.

It relies on quorums. With N replicas per item, a write must be confirmed by W replicas and a read must hear from R replicas. If R + W > N, at least one replica in every read has the latest write.

Replicas that missed writes are brought back up to date by:

• Read repair: when a read notices one replica has an older value, it writes the newer value back to it.
• Hinted handoff: another node temporarily stores writes meant for a down node and delivers them when it returns.
• Anti-entropy: a background process compares replicas (often using Merkle trees) and copies missing data.

Strengths: no failover needed, high write availability, and tolerance of slow or failed nodes. Weaknesses: eventual consistency by default, concurrent writes still need conflict resolution, and reasoning about correctness is harder.

7. Synchronous vs Asynchronous Replication

Independent of the replication type, you must decide when the leader tells the client "your write succeeded".

Many production setups use semi-synchronous replication: one nearby replica is synchronous for durability, while distant replicas in other regions are asynchronous to avoid slowing every write.

8. Replication Lag and Its Problems

With asynchronous replication, followers are always a little behind the leader. This delay is called replication lag. Usually it is a few milliseconds, but under heavy load, long-running queries or network trouble it can grow to seconds or minutes. Lag causes three classic user-visible problems:

• Reading your own writes: a user updates their profile photo, the page reloads from a lagging replica, and the old photo appears. The user thinks the update failed. Fix: read data the user just changed from the leader, for example for a few seconds after a write, or track the last write position and only read from replicas that have caught up.
• Monotonic reads: a user refreshes a page twice. The first request hits an up-to-date replica and shows a new comment; the second hits a lagging replica and the comment disappears. Fix: route each user's reads to the same replica, for example by hashing the user ID.
• Consistent prefix reads: an observer sees an answer before the question it replies to, because the two writes reached replicas in a different order. Fix: keep causally related writes in the same partition, or use databases that preserve ordering.

Always monitor lag. In PostgreSQL, the pg_stat_replication view on the primary shows it for each replica; in MySQL, SHOW REPLICA STATUS reports how far behind each replica is.

9. Failover: What Happens When the Leader Fails

Failover is the process of promoting a follower to become the new leader when the old one fails. It can be manual (an engineer decides) or automatic (a tool such as Patroni for PostgreSQL, MySQL Group Replication or a managed cloud service decides). Automatic failover typically follows these steps:

1. Detect the failure: the leader stops responding to health checks for a set time, such as 30 seconds.
2. Choose a new leader: usually the follower with the most up-to-date data, often through a consensus vote.
3. Reconfigure: other followers start replicating from the new leader, and clients are redirected through DNS, a virtual IP, a proxy or a service-discovery update.
4. Fence the old leader: if it comes back, it must not accept writes; it rejoins as a follower.

Failover has real risks:

• Lost writes: with asynchronous replication, writes the old leader confirmed but had not yet replicated are lost.
• Split brain: if the old leader is only cut off, not dead, two servers may both believe they are the leader and accept conflicting writes. Consensus-based leader election and fencing prevent this.
• False alarms: a timeout that is too short triggers unnecessary failovers during brief network hiccups or load spikes.

10. Comparing the Replication Types

11. Read/Write Splitting in Spring Boot

With single-leader replication, a common pattern is to send writes to the primary and read-only transactions to a replica. Spring's AbstractRoutingDataSource makes this possible by choosing a data source for each transaction:

app:
  datasource:
    primary:
      url: jdbc:postgresql://db-primary.internal:5432/shop
      username: shop_app
      password: ${DB_PASSWORD}
    replica:
      url: jdbc:postgresql://db-replica.internal:5432/shop
      username: shop_app
      password: ${DB_PASSWORD}

package com.javabytechie.replication;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;

public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        // Read-only transactions go to the replica, everything else to the primary
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "replica" : "primary";
    }
}

package com.javabytechie.replication;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy;

@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    @ConfigurationProperties("app.datasource.primary")
    public DataSourceProperties primaryProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("app.datasource.replica")
    public DataSourceProperties replicaProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    public DataSource dataSource() {
        DataSource primary = primaryProperties().initializeDataSourceBuilder().build();
        DataSource replica = replicaProperties().initializeDataSourceBuilder().build();

        ReadWriteRoutingDataSource routing = new ReadWriteRoutingDataSource();
        routing.setTargetDataSources(Map.of("primary", primary, "replica", replica));
        routing.setDefaultTargetDataSource(primary);
        routing.afterPropertiesSet();

        // Delay getting a real connection until the read-only flag of the transaction is known
        return new LazyConnectionDataSourceProxy(routing);
    }
}

package com.javabytechie.replication;

import java.util.List;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ProductService {

    private final ProductRepository products;

    public ProductService(ProductRepository products) {
        this.products = products;
    }

    @Transactional(readOnly = true)            // routed to the replica
    public List<Product> search(String keyword) {
        return products.findByNameContainingIgnoreCase(keyword);
    }

    @Transactional                             // routed to the primary
    public Product create(Product product) {
        return products.save(product);
    }
}

The LazyConnectionDataSourceProxy is important. Without it, Spring may fetch a connection before it marks the transaction as read-only, and every query would go to the primary. Also remember replication lag: if a user must see their own change immediately after saving it, read it in a normal (read-write) transaction so it comes from the primary.

12. Setting Up Replication: A PostgreSQL Example

Here is a simplified outline of PostgreSQL streaming replication with one primary and one standby. Managed cloud databases do all of this for you with a few clicks.

wal_level = replica              # write enough information to the WAL for replicas
max_wal_senders = 10             # how many replicas can stream at once
# Optional: make one standby synchronous
# synchronous_standby_names = 'replica1'

# allow the replication user to connect from the replica's network
host  replication  replicator  10.0.2.0/24  scram-sha-256

pg_basebackup -h 10.0.1.10 -U replicator -D /var/lib/postgresql/data -R -P
# -R writes standby.signal and the connection settings, so the server starts as a replica

SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

 client_addr |   state   | sync_state |   replay_lag
-------------+-----------+------------+-----------------
 10.0.2.15   | streaming | async      | 00:00:00.004213

13. Replication vs Sharding vs Backup

Large systems usually combine all three: data is sharded for scale, each shard is replicated for availability, and everything is backed up for recovery. See the Database Sharding tutorial for the next step.

14. Common Mistakes to Avoid

• Treating replicas as backups. A bad DELETE is replicated instantly. Keep separate, tested backups.
• Ignoring replication lag. Reading just-written data from a replica leads to confusing "my change disappeared" bugs.
• Never testing failover. A failover process that has never been practised often fails when it is needed. Rehearse it regularly.
• Putting all replicas in one place. Replicas on the same rack or availability zone can fail together.
• Long-running queries on replicas. Heavy reports can delay replay and increase lag, or be cancelled because they conflict with incoming changes.
• Using multi-leader without a conflict strategy. Unresolved conflicts silently corrupt data.

15. Best Practices

• Start with single-leader replication; move to multi-leader or leaderless only for clear needs such as multi-region writes.
• Use at least one synchronous or semi-synchronous replica when losing confirmed writes is unacceptable.
• Spread replicas across availability zones, and keep a replica in another region for disaster recovery.
• Monitor replication lag, and alert before it affects users.
• Use proven automatic failover tooling or a managed database service, with fencing to prevent split brain.
• Send read-only work to replicas, but serve read-your-own-writes paths from the primary.
• Keep regular backups with point-in-time recovery, and test restores.
• Run reporting and analytics on a dedicated replica so they never slow down production.

16. Summary

• Database replication keeps copies of data on multiple servers for availability, read scaling, lower latency and disaster recovery.
• Single-leader replication is the simplest and most common; multi-leader allows writes in several places but must resolve conflicts; leaderless uses quorums and has no leader to fail.
• Synchronous replication protects every write but adds latency; asynchronous is fast but can lose recent writes; semi-synchronous balances the two.
• Replication lag causes stale reads, so design for read-your-own-writes and monotonic reads.
• Failover promotes a follower when the leader fails, and must guard against lost writes and split brain.

Replication is the foundation of every highly available data layer. Open the FAQ tab for answers to common questions about database replication.

Frequently Asked Questions (FAQ)