Replication #

In modern backend systems, the database is often both a single point of failure and a performance bottleneck. When traffic grows, read queries get heavier, or availability becomes a primary requirement, a single database server usually isn’t enough. This is where database replication comes in.

This article covers in depth:

What database replication is
Why replication is so important
Types of database replication
Common problems with replication
Best practices for implementing database replication

What Is Database Replication #

Database replication is the process of copying and synchronizing data from one database (usually called the primary / master) to one or more other databases (called replicas / standby / slaves).

Its main goals:

Providing high availability
Improving read scalability
Reducing the load on the primary database
Providing backup and disaster recovery

In general, the simple flow looks like this:

flowchart TD
    Client["Client"] -->|"Write"| Primary["Primary DB"]
    Primary -->|"Replication"| Replica["Replica DB(s)"]
    Client -->|"Read"| Replica

Why Database Replication Matters #

High Availability (HA) #

If the primary database goes down (hardware failure, OOM, full disk, etc.), a replica can be promoted to primary.

Without replication:

Downtime = the application dies

With replication:

Faster failover
Much smaller downtime

Read Scalability #

Most applications:

Are read-heavy (dashboards, data lists, reports, admin)
Have relatively fewer writes

With replication:

Writes → primary
Reads → replicas

The result:

Primary load drastically reduced
Heavy read queries don’t disturb writes

Isolating Heavy Query Load #

Queries like:

Reporting
Data export
Analytic queries
Full table scans

Are very dangerous to run on the primary.

With replication:

Heavy queries are routed to replicas
The primary stays responsive

Disaster Recovery #

Replicas can:

Be in a different availability zone
Even in a different region

If something happens:

Data center failure
Human error on the primary

The replica is the last line of defense.

Types of Database Replication #

Synchronous Replication #

Characteristics:

A write is considered successful only if the primary and the replicas have received the data

Advantages:

Consistent data (strong consistency)
Almost no replication lag

Disadvantages:

Write latency increases
If a replica is slow → the primary slows down too

Good for:

Financial systems
Highly critical data

Asynchronous Replication #

Characteristics:

The primary immediately considers the write successful
Replicas receive the data later

Advantages:

Very fast writes
The primary doesn’t depend on replicas

Disadvantages:

There’s replication lag
Risk of data not yet copied when the primary crashes

Good for:

Most web applications
Read-heavy traffic systems

Semi-Synchronous Replication #

Characteristics:

The primary waits for at least one replica to receive the data

Trade-off:

Safer than async
Faster than full sync

Common Problems with Database Replication #

Replication Lag #

The replica falls behind the primary.

Common causes:

Write queries too heavy
Suboptimal indexes
Weaker replica hardware

Impact:

Users see stale data
Inconsistent user experience

The Read After Write Problem #

A user:

Updates data
Immediately reads it

If the read is routed to a replica:

The data isn’t synchronized yet
The user sees stale data

Queries Unsafe on Replicas #

Some queries are dangerous:

SELECT ... FOR UPDATE
Long transactions
Locking queries

A replica is not a place for locking.

Schema and Migrations #

Schema changes:

Heavy DDL
Index creation

Can:

Disrupt replication
Cause extreme lag

Database Replication Best Practices #

Separate Write and Read Connections #

Use:

Write connection → primary
Read connection → replica

Usually via:

A database proxy
Application-level routing

Don’t Use Replicas for Critical Real-Time Data #

For cases like:

Login
Balance updates
State machines

Use the primary.

Replicas are good for:

Lists
History
Reports

Handle Read After Write Correctly #

A few approaches:

Sticky sessions (read from the primary for a few seconds)
Explicit read from the primary
Caching the write result

Monitor Replication Lag #

Must monitor:

Seconds behind primary
Replication IO / SQL threads

If lag is high:

Reduce heavy queries
Optimize indexes
Scale replicas

Avoid Heavy Queries on the Primary #

Make sure:

Reporting
Exports
Analytic cron jobs

Run on replicas.

Use Dedicated Replicas for Specific Loads #

Example:

Replica A → API reads
Replica B → reporting
Replica C → backup

Load isolation = stability.

Migrate Schemas Carefully #

Best practices:

Online schema migrations
Add indexes gradually
Avoid DDL during peak hours

Never Assume Replicas Are Always Consistent #

Design the application with the assumption that:

Replicas can lag
Replicas can be temporarily unavailable

Failover logic must be clear.

Closing #

Database replication is not just data duplication, but an important foundation for:

Scalability
Availability
System stability

However, replication also brings complexity:

Data consistency
Read/write routing
Lag and failover

With the right design and disciplined best practices, database replication can be a system saver, not a new source of problems.

“Replication doesn’t make the database simpler, but it makes the system more resilient.”