Master-Slave Database Replication: Achieving High Availability and Scalability

Your database is a single point of failure. If that one server goes down, your entire application goes with it. Replication fixes that.

Database replication creates copies of your data on multiple servers. The primary (master) handles all writes. The replicas (slaves) handle reads. If the primary dies, a replica can be promoted to take over.

How Master-Slave Replication Works

One server is the primary. It accepts all write operations -- inserts, updates, deletes. Every change gets recorded in a write-ahead log (WAL) or binary log.

One or more replicas continuously stream these changes from the primary and apply them locally. They maintain near-identical copies of the data.

Your application sends writes to the primary and distributes reads across replicas. Most web applications are read-heavy (often 80-90% reads), so this architecture handles scale well.

Setting It Up with PostgreSQL

Configure the primary server:

CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'secure_password';

Edit postgresql.conf on the primary:

wal_level = replica
max_wal_senders = 3
wal_keep_size = 64

Edit pg_hba.conf to allow the replica to connect:

host replication replica_user <replica_ip>/32 md5

On the replica, create a base backup and configure recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=<primary_ip> port=5432 user=replica_user password=secure_password'

Start the replica. It begins streaming changes from the primary.

The Replication Lag Problem

Replication is asynchronous by default. The primary commits a write and returns success immediately. The replica receives that change milliseconds to seconds later.

During that gap, a read on the replica returns stale data. This is called replication lag.

For most applications, a few milliseconds of lag is fine. For others, it's not. If a user updates their profile and immediately reads it back from a replica, they might see old data. The fix: route reads-after-writes to the primary, or use synchronous replication (at the cost of write latency).

Multi-Master Replication

In multi-master setups, every node accepts writes. This eliminates the single-writer bottleneck but introduces conflict resolution. What happens when two nodes write to the same row simultaneously?

Most teams avoid multi-master unless they genuinely need it. The conflict resolution logic is complex and error-prone. Master-slave is simpler and sufficient for most use cases.

The Trade-offs

Benefits:

• Read scalability by distributing reads across replicas
• High availability -- promote a replica if the primary fails
• Geographic distribution -- place replicas closer to users

Costs:

• Replication lag means reads may be stale
• Write scalability doesn't improve -- the primary is still the bottleneck
• Failover isn't instant and requires careful orchestration
• More servers to manage, monitor, and pay for

Replication is typically the first scaling strategy you reach for after indexing and caching. It's simpler than sharding and solves the most common bottleneck: too many reads for a single server.