Replication

Replication copies data to more than one node. It is the foundation of both availability and read scaling, and the source of most of the subtle behaviour in a database cluster.

Physical versus logical

Physical replication ships the storage-level change log — WAL records, or InnoDB redo equivalents — and the replica applies them byte for byte. The replica is an exact copy: same version, same page layout, all databases, all objects.

• Cheap to apply, since no SQL is parsed or planned.
• Cannot filter, transform or replicate between major versions.
• Used for high availability and read replicas.

Logical replication decodes the change log into row-level operations and applies them as statements on the subscriber. The subscriber can run a different major version, hold a subset of tables, and have extra indexes.

• More expensive to apply, and typically single-threaded per subscription unless configured otherwise.
• Requires a replica identity (a primary key, usually) for updates and deletes.
• Used for major-version upgrades, data movement and feeding downstream systems. See Change Data Capture.

Synchronous versus asynchronous

This determines your data loss window, and it is the single most consequential replication setting.

Asynchronous replication is the default nearly everywhere, and it means an acknowledged commit can be lost in a failover. That may be perfectly acceptable — but it must be a decision, not a discovery.

Replication lag

Lag has three components, and diagnosing it means knowing which one is growing:

1. Send lag — WAL generated but not yet sent. Usually network bandwidth.
2. Receive/write lag — received but not yet written to the replica's disk. Usually replica I/O.
3. Apply lag — written but not yet applied. Usually single-threaded replay hitting a CPU limit, or a conflicting query on the replica blocking apply.

A replica that is fine at 2am and 90 seconds behind at peak is usually apply-bound, and adding network capacity will not help. See the Replication lag playbook.

Topologies

• Single primary with replicas. One writer, N readers. Simple, well understood, and requires a failover procedure. See Primary-Replica Architecture.
• Cascading replication. A replica feeds other replicas, reducing load on the primary at the cost of longer lag chains.
• Multi-primary. Several nodes accept writes and reconcile conflicts. See Multi-Primary Architecture.
• Consensus replication. A majority must persist each write before it commits, so failover needs no manual promotion. See Consensus Algorithms.

What replication does not give you

Delayed replicas (PostgreSQL's recovery_min_apply_delay, MySQL's SOURCE_DELAY) partially close this gap: a replica held an hour behind gives you an hour to notice a destructive statement before it applies. That is a useful complement to backups, not a replacement.