Database Replication Explained: Physical vs Logical, Sync vs Async, Lag, and Failover

Database Replication Explained: Physical vs Logical, Sync vs Async, Lag, and Failover

Database replication means maintaining one or more additional copies of data by continuously transferring changes from one database node to another.

Teams use replication for several different goals:

• high availability
• disaster recovery
• read scaling
• analytics or downstream integrations
• migrations and upgrades

Those goals are related, but they do not require the same replication mode or operational guarantees. A read replica optimized for reporting is not automatically the best failover target, and a logical change feed is not the same thing as a byte-for-byte standby.

This guide explains the mechanics and trade-offs of database replication itself. Read-routing consistency and application use of replicas are covered separately.

The simplest model: primary and replica

A common topology is:

          writes
            |
            v
         primary
        /   |   \
       /    |    \
 replica A replica B replica C

The primary accepts authoritative changes. Replicas receive and apply those changes.

Depending on the technology, replicas may be:

• read-only
• promotable during failover
• delayed intentionally
• filtered to only some tables/data
• running a different major database version

The word “replica” therefore describes a role, not one universal implementation.

Physical replication

Physical replication copies low-level database changes that correspond closely to the storage representation.

In PostgreSQL, physical streaming replication sends WAL records generated by the primary and replays them on the standby.

Physical replication is a strong fit for:

• high availability
• hot/warm standby
• full-database copies
• fast promotion to a compatible server

Characteristics typically include:

• replica closely matches the primary's physical database state
• replication operates below normal table-level SQL semantics
• the standby usually runs a compatible database engine/version
• selecting only arbitrary rows/tables is generally not the purpose

It is often the simplest architecture when the requirement is “maintain another copy of this whole database server for failover or reads.”

Logical replication

Logical replication transfers changes in terms of logical data operations rather than copying the physical storage representation.

PostgreSQL's current logical replication uses a publish/subscribe model. A publication defines which table changes are produced; subscribers receive and apply them.

Logical replication is useful when you need finer control such as:

• replicate selected tables
• replicate between major PostgreSQL versions
• migrate data between systems
• create integration/change feeds
• consolidate selected data elsewhere
• use different physical storage layouts on source and destination

Because it operates at a higher logical level, it also introduces different schema-management and conflict considerations.

Physical vs logical: a practical comparison

Neither is “better.” Choose the replication layer that matches the job.

Initial synchronization comes before streaming

A new replica cannot begin from nowhere.

It usually needs:

1. an initial consistent snapshot/base copy
2. a point in the change log corresponding to that copy
3. continuous changes after that point

During this initial catch-up phase, the replica may be far behind and unsuitable for serving normal traffic.

Production automation should distinguish states such as:

initializing
catching up
healthy/streaming
lagging
failed

Do not add a newly created replica to the read pool merely because the process started successfully.

Asynchronous replication

In asynchronous replication, the primary can acknowledge a commit before the replica has durably received or applied it.

Benefits:

• lower write latency
• primary does not normally wait for distant replicas
• better tolerance of temporary replica/network slowness

Trade-offs:

• replicas can serve stale data
• a failover can lose acknowledged transactions that had not reached the promoted replica
• lag becomes an important operational metric

PostgreSQL's HA documentation explicitly describes this trade-off: asynchronous replication permits delay and therefore opens a window for stale reads and possible loss during failover.

Synchronous replication

Synchronous replication makes a commit wait for acknowledgement from one or more replicas according to the database's configured durability stage.

The goal is to shrink or remove certain failover data-loss windows.

The cost is coordination latency.

If a synchronous standby is far away or slow, application writes can become slow because commit latency now includes communication with that standby.

A simplified model is:

client
  |
  v
primary writes log
  |
  +---- wait for required replica acknowledgement
  |
commit acknowledged

Synchronous replication should be chosen from an explicit recovery-point requirement, not because “stronger is always better.”

“Synchronous” still needs a precise definition

Databases can acknowledge different stages, for example:

• replica received the log record
• replica flushed it to durable storage
• replica replayed/applied it so reads can observe it

Those are different guarantees.

In PostgreSQL, synchronous commit modes can distinguish remote write/flush/apply behavior.

If the business requirement is “a promoted replica must not lose the transaction,” that is different from “a read on the standby must immediately see the transaction.”

Define the exact guarantee you need.

Replication lag has multiple stages

A replica can be behind for different reasons.

Think of the flow as:

primary generated change
        |
        v
change sent
        |
        v
replica received change
        |
        v
replica flushed change
        |
        v
replica replayed/applied change

Lag can occur at any step.

That distinction helps diagnosis:

• large send gap: primary/network pressure
• large receive gap: network/receiver issue
• large replay gap: replica CPU/I/O or conflicting workload

“Replica lag = 5 seconds” is useful, but knowing where the delay occurs is more actionable.

Replication lag is workload-dependent

Lag can grow during:

• high write bursts
• large transactions
• network congestion
• slow storage
• CPU saturation on the replica
• long-running standby queries
• schema changes
• index maintenance
• a replica recovering after downtime

Always monitor the system under peak write workload, not only idle steady state.

A replica that is normally milliseconds behind may become minutes behind during a bulk load.

Replication is not backup

This distinction is critical.

If someone accidentally runs:

DELETE FROM customers;

replication can faithfully copy the deletion to every replica.

The same is true for many forms of:

• accidental updates
• application bugs
• malicious data changes
• schema mistakes

Replication improves availability and redundancy. A backup provides a recoverable historical copy.

Production systems generally need both:

replication -> stay available after node failure
backup/PITR -> recover from bad data changes or historical loss

Test restores; a backup that cannot be restored is only an assumption.

Delayed replicas can add another recovery layer

Some databases support intentionally delayed replication.

For example, a replica may apply changes 30 minutes after the primary.

That can create a short window to recover from accidental changes before they reach the delayed copy.

Trade-offs:

• it is deliberately stale
• it is not a normal read replica
• it may not be the preferred immediate HA failover target
• storage for unapplied logs must be planned

A delayed replica complements backups; it does not replace them.

Replication slots and retained logs need monitoring

Streaming/logical replication often requires the source to retain log data until consumers have received it.

If a subscriber stops consuming, retained WAL/binlog/change-log data can grow dramatically.

In PostgreSQL, replication slots can protect required WAL from being removed, which is valuable for consumers but dangerous when an abandoned slot causes unbounded disk growth.

Monitor:

• inactive consumers
• retained log volume
• disk usage
• restart/replay positions

A disconnected replica should not be able to fill the primary's disk unnoticed.

Schema changes matter more with logical replication

Physical replication normally carries the physical database state as part of the replication stream.

Logical replication often requires more explicit schema coordination.

A subscriber needs compatible target structures for the changes it receives.

Before running DDL, understand:

• whether that DDL is replicated automatically
• whether source and target schemas must be changed separately
• whether the change is backwards compatible during rollout
• what happens to replication while schemas differ

Schema migration procedures should be part of the replication design, not discovered during the first production deployment.

Logical replication needs a replication identity

To apply updates and deletes, the subscriber needs to identify which target row corresponds to the source change.

In PostgreSQL, logical replication normally relies on a replication identity, commonly the table's primary key.

Tables without a suitable identity may require special configuration and can make update/delete replication more expensive or impossible under the desired setup.

This is one reason good primary-key design helps operational architecture beyond normal application querying.

Multi-writer replication introduces conflicts

Single-primary replication has a straightforward ownership model: one node is the source of writes.

When multiple nodes can independently modify overlapping data, conflicts become possible:

node A: user.email = a@example.com
node B: user.email = b@example.com

Now the system needs a conflict model:

• one writer owns the record
• last-write-wins
• application-specific merge
• consensus before commit
• manual conflict resolution

Multi-primary replication is not simply “primary-replica, but twice as scalable.” It is a different consistency architecture.

Replication and failover are different mechanisms

Replication creates/upgrades copies.

Failover decides:

• which replica is safe to promote
• who has authority to promote it
• how clients discover the new primary
• how split brain is prevented
• what happens to the old primary when it returns

A database can have perfect replication and still have unsafe failover automation.

High-availability design needs both data movement and control-plane behavior.

Split brain is a serious failure mode

Split brain occurs when two nodes believe they are the writable primary at the same time.

That can create divergent histories that are difficult or impossible to merge safely.

HA systems use mechanisms such as:

• quorum/consensus
• fencing
• leases
• external coordination
• explicit promotion rules

The exact mechanism varies by database, but the principle is universal: old write ownership must be revoked before or as new write ownership is established.

Read scaling is a use of replication, not the definition of replication

Replicas can serve reads and offload the primary, but that introduces stale-read semantics.

Other deployments replicate solely for HA and never serve application queries from standbys.

Keep these decisions separate:

Do we need replication?       -> availability/redundancy/data distribution
Should app reads use replicas? -> consistency and read-scaling policy

This prevents a useful HA replica from accidentally becoming an overloaded reporting server.

Replication topology can be cascading

In some systems, a replica can stream changes to another downstream replica:

primary -> replica A -> replica B

Cascading can reduce the primary's direct fan-out and help remote topologies.

But each layer can add:

• lag
• another failure dependency
• operational complexity

Do not add layers without understanding how promotion and recovery work when an intermediate node fails.

Cross-region replication changes the economics

Asynchronous cross-region replication is common because geographic network latency does not sit directly in the commit path.

Synchronous cross-region replication gives stronger guarantees but write latency now includes wide-area communication.

Choose based on:

• recovery point objective (RPO)
• recovery time objective (RTO)
• acceptable write latency
• regional failure model
• regulatory/data-residency constraints

A “multi-region” diagram is not enough; the commit and failure semantics must be explicit.

What to monitor

A healthy replication system should expose evidence for:

• replication state
• bytes/LSN/GTID lag
• time-based lag where meaningful
• receive, flush, and apply/replay progress
• replica CPU and disk latency
• network throughput/errors
• retained WAL/binlog volume
• inactive replication slots/subscriptions
• failed apply/conflict events
• reconnect frequency
• replica role changes

For HA, also monitor the control plane:

• failover events
• promotion duration
• fencing success
• current authoritative primary

Replication health is more than “TCP connection exists.”

A practical replication decision framework

Use physical replication when

• you want a close full-database standby
• HA/failover is the primary goal
• source and replica can use compatible database versions/storage semantics

Use logical replication when

• only selected tables/data should move
• migrations/upgrades need flexibility
• downstream integration or CDC-like consumption is required
• source and destination physical layouts should differ

Use asynchronous replication when

• low primary write latency is important
• some lag/data-loss window is acceptable

Use synchronous replication when

• the defined RPO requires acknowledgement from replicas before commit
• the additional coordination latency is acceptable

Use both replication and backups when

• you care about availability and recovery from accidental/corrupt historical changes

Database replication is ultimately a contract about which copy has authority, how changes propagate, when a commit is considered safe, and what can be lost during failure. If those four questions are explicit, the topology becomes much easier to reason about.

References

• PostgreSQL — High Availability, Load Balancing, and Replication
• PostgreSQL — Streaming Replication
• PostgreSQL — Logical Replication
• MySQL — Replication