Database Replication Explained: From Primary to Read Replica

1. What database replication actually solves

Database replication continuously copies data from a primary instance to one or more replica instances. The reason is rarely redundancy for its own sake, but two very concrete problems: read load that a single instance can no longer handle, and fault tolerance when the primary instance becomes unreachable for whatever reason. Without database replication, every application depends on the availability of exactly one database instance, a single point of failure that is rarely acceptable in production systems.

The difference between good and bad replication architecture rarely lies in the technology itself, but in understanding the consistency guarantees. Anyone who misunderstands database replication as a plain, delay free copy builds applications that read from a replica and treat stale data as current. The following sections explain physical versus logical replication, synchronous versus asynchronous replication, and how read replicas and failover strategies are configured in practice.

2. Physical vs. logical replication

Physical database replication copies the database at the byte level: every change to data pages is transferred identically to the replica, usually via the same write ahead log mechanism used for crash recovery. The result is an exact copy of the primary instance, including indexes, statistics and internal structure. Physical replication is efficient because it requires no SQL interpretation, but has one downside: the replica must run the same database version and architecture as the primary.

Logical database replication replicates at the level of change operations (INSERT, UPDATE, DELETE), not at the byte level. This allows flexibility that physical replication does not offer: different database versions between primary and replica, selective replication of only certain tables, and even replication between different database engines. The price is higher processing overhead, because every change has to be interpreted and reapplied.

-- PostgreSQL: set up physical streaming replication
-- On the primary (postgresql.conf)
-- wal_level = replica
-- max_wal_senders = 10

-- On the replica: base backup, then recovery config
-- pg_basebackup -h primary_host -D /var/lib/postgresql/data -U replicator -P

-- Check replication status on the primary
SELECT client_addr, state, sync_state, replay_lag
FROM pg_stat_replication;

-- Logical replication: publication on primary, subscription on replica
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- On the replica
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=primary_host dbname=shop user=replicator'
PUBLICATION orders_pub;

3. Synchronous vs. asynchronous replication

With synchronous database replication, the primary instance only confirms a transaction as committed once at least one replica has also confirmed the change. This guarantees that a committed record is never lost, even if the primary fails immediately afterward. The price is increased latency on every write, because the system has to wait for network confirmation from the replica, which becomes noticeable with geographically distant replicas.

Asynchronous database replication confirms a transaction immediately on the primary, regardless of whether the replica has already received the change. This minimizes write latency, but has a serious consequence: if the primary fails exactly between commit and transfer to the replica, the most recent transactions are lost irrecoverably. Most production systems use asynchronous replication for read replicas and reserve synchronous replication for cases where data loss is unacceptable, for instance financial transactions.

4. Read replicas for distributing read load

A read replica is a read only copy of the primary database that serves exclusively read requests. This form of database replication solves a very concrete scaling problem: for heavily read oriented applications (reporting dashboards, product catalogs, search functionality), read load is often orders of magnitude higher than write load. Instead of burdening the primary with this read load, read requests are distributed across multiple read replicas, freeing the primary for write operations.

It is important that application code explicitly distinguishes between read and write connections, usually via a connection router or a database proxy such as PgBouncer or ProxySQL. A common source of errors: an application writes to the primary and immediately afterward reads from a replica before the change has arrived there, and the user does not see their own change. This problem is called read after write consistency and must be explicitly addressed with database replication, for instance by deliberately reading from the primary right after a write.

5. Understanding and measuring replication lag

Replication lag is the time span between a change on the primary and its arrival on a replica. With asynchronous database replication, this lag is never exactly zero, but fluctuates depending on network latency, write load on the primary and resources on the replica. Growing lag is an early warning sign: either the replica hardware is undersized, or write load on the primary exceeds the replica's processing capacity.

Monitoring replication lag is part of every production replication setup. In PostgreSQL, pg_stat_replication provides lag in bytes and optionally in time, in MySQL SHOW REPLICA STATUS shows the value Seconds_Behind_Source. Alerting when lag exceeds a defined threshold prevents applications from unknowingly reading severely stale data from a replica.

-- MySQL: check replication lag on a replica
SHOW REPLICA STATUS\G
-- Look for: Seconds_Behind_Source, Replica_IO_Running, Replica_SQL_Running

-- PostgreSQL: replication lag in bytes and time on the primary
SELECT
  client_addr,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
  replay_lag
FROM pg_stat_replication;

-- Alert threshold example: fail health check if lag exceeds 30 seconds
SELECT CASE
  WHEN EXTRACT(EPOCH FROM replay_lag) > 30 THEN 'UNHEALTHY'
  ELSE 'HEALTHY'
END AS replica_status
FROM pg_stat_replication
LIMIT 1;

6. Failover strategies for a primary outage

If the primary instance fails, one of the replicas has to be promoted to become the new primary, a process called failover. Manual failover, where a human detects the outage and triggers the promotion, is simple to understand but slow, often taking several minutes. Automatic failover with tools such as Patroni or Orchestrator detects the outage on its own and promotes a suitable replica within seconds, but carries its own risk: split brain, where two instances simultaneously believe they are the primary.

A robust failover setup for database replication uses an external consensus mechanism (such as etcd or Consul) to unambiguously determine which instance is the primary, instead of relying on local heuristics of individual nodes. After a failover, all remaining replicas also need to be realigned to the new primary, and application connections need to be automatically redirected to the new primary, usually via a virtual hostname or a proxy.

-- Check current replica status before promoting it during failover
SELECT pg_is_in_recovery(); -- true on a replica, false on a primary

-- Promote a PostgreSQL replica to become the new primary
-- (executed by the failover tool, e.g. Patroni, not manually in normal operation)
SELECT pg_promote();

-- After promotion, verify the new primary accepts writes
INSERT INTO health_check (checked_at) VALUES (NOW());

7. Multi primary replication and its pitfalls

Multi primary replication allows write operations on multiple instances simultaneously, instead of restricting them to a single primary. This sounds attractive for geographically distributed applications, but brings a fundamental problem: conflict detection. If the same row is changed on two different primaries at the same time, a conflict resolution mechanism has to decide which change wins, usually via last write wins or application specific logic.

In practice, multi primary database replication is significantly more complex to operate than a simple primary replica model and is usually only used when geographically distributed write load provides a real business advantage. For most applications, a single primary with several read replicas is sufficient and considerably easier to operate, debug and monitor.

-- Last-write-wins conflict resolution: a common pattern for multi-primary setups
CREATE TABLE customer_profile (
  customer_id INT PRIMARY KEY,
  email VARCHAR(200),
  updated_at TIMESTAMP NOT NULL,
  origin_node VARCHAR(50) NOT NULL
);

-- Conflict resolution logic applied during replication merge:
-- keep the row with the most recent updated_at, break ties by origin_node
SELECT DISTINCT ON (customer_id) *
FROM customer_profile
ORDER BY customer_id, updated_at DESC, origin_node ASC;

8. Accounting for replication in the application

Application code has to actively account for database replication instead of treating it as transparent infrastructure. Critical read operations right after a write belong routed to the primary, not to a replica with unknown lag. Less critical read operations, for instance reporting or analytics, can run comfortably on replicas, even with several seconds of delay.

A proven pattern is explicitly marking database connections as read write or read only in application code, combined with a connection router that respects that marking. That way, the decision of whether a query goes to the primary or a replica stays a deliberate, testable decision, rather than an implicit random outcome of a load balancer.

-- Example: explicit read/write connection routing at the application layer
-- Write connection: always the primary
-- DSN: postgresql://app_user@primary.internal:5432/shop

-- Read connection: routed to a replica pool via a proxy
-- DSN: postgresql://app_user@replica-pool.internal:5432/shop

-- Critical read right after a write: force primary explicitly
BEGIN;
UPDATE orders SET status = 'paid' WHERE order_id = 1001;
COMMIT;

-- Immediately re-reading order 1001 must use the primary connection,
-- not the replica pool, to guarantee read-after-write consistency
SELECT status FROM orders WHERE order_id = 1001; -- via primary DSN

-- Non-critical reporting query: safe to run on any replica
SELECT region, SUM(total_amount)
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY region; -- via replica-pool DSN

9. Replication models compared

Choosing the right replication model depends on consistency requirements, latency tolerance and operational effort.

For most applications, asynchronous database replication with one or more read replicas and automated failover is a good compromise between operational effort and fault tolerance. Synchronous replication only pays off when data loss during failover is unacceptable from a business standpoint, and multi primary should remain the exception, not the default architecture.

10. Summary

Database replication solves two central problems: distributing read load across multiple instances and providing fault tolerance against losing the primary instance. Physical replication copies at the byte level and is efficient, logical replication replicates change operations and offers more flexibility. Synchronous replication prevents data loss at the price of higher latency, asynchronous replication minimizes latency at the price of possible data loss during failover.

Read replicas relieve the primary for read heavy applications, but need to be explicitly accounted for with read after write consistency in application code. Automatic failover drastically reduces downtime but requires a robust consensus mechanism to avoid split brain. Anyone configuring database replication correctly gains scalability and fault tolerance without accepting silent consistency problems.

11. FAQ: Database Replication Explained