A read replica repeatedly falls minutes behind its primary during peak hours, even though the replication network link is nowhere near saturated. What are the usual causes of apply-side replication lag, and how would you narrow it down?

## Step zero: prove it is apply lag Before theorising, split the pipeline. If the replica has received and flushed nearly everything the primary has produced but replayed far less, the backlog is on the apply side and network/bandwidth theories are dead. In PostgreSQL that is a large `flush_lsn → replay_lsn` gap; in MySQL it is a large gap between the received position and the executed position. Anything else — a gap before the received position — is a sender, network, or replica-disk story instead. ## Cause 1: the concurrency asymmetry This is the structural reason apply lag exists at all. A primary executes writes with as much parallelism as its clients supply: 200 sessions committing small transactions simultaneously. The replica's job is to reproduce the *result* in a way that preserves ordering guarantees, and historically it does so with a single replay process. One CPU replaying what many CPUs produced only works because replay is cheaper per change than the original execution (no parsing, no planning, no client round trips). When the primary's write rate rises past that advantage, lag grows without bound — it is a **throughput deficit, not a spike**, and it will not recover until the write rate falls. Remedies are architectural: enable multi-threaded/parallel apply where the engine supports it (MySQL's multi-threaded applier with logical-clock/writeset-based parallelism; PostgreSQL parallelizes only certain aspects of recovery, so physical standbys remain largely serial), reduce write volume, or shard. ## Cause 2: transaction shape How work is packaged matters as much as its volume. - **Long-running write transactions** may be transmitted only when they commit (typical for row-based logical streams and for MySQL's binlog, which writes on commit). A transaction that ran for 20 minutes on the primary arrives as one indivisible slab; the replica cannot start it earlier and cannot split it, so lag jumps by roughly the transaction's duration the moment it lands. - **Bulk operations** — mass UPDATE/DELETE, index builds, table rewrites, large imports — generate change volume out of all proportion to their statement count. In row-level replication a single `DELETE FROM t WHERE created_at < ...` touching 10 million rows becomes 10 million row events. - **DDL** is typically serialized and can block the applier entirely while it runs. The operational fix is batching: chunk large writes into bounded transactions with pauses, and schedule them against a lag budget. ## Cause 3: no usable index on the apply path This one produces the most spectacular numbers. In row-level replication the applier receives "the row that had these identifying values changed to this" and must locate that row locally. If the table has a primary key (or a unique index designated as the row identity) and the replica has it too, this is one index probe per row: fast. If the table has no primary key — or the replica's copy of the table is missing the relevant index, which happens when someone tailors indexes on a reporting replica — the applier does a **full scan per row event**. A batch of 100,000 row changes on a 50-million-row table becomes 100,000 sequential scans. Lag goes from milliseconds to hours. Diagnosis: look at what the applier thread is executing and its state; a scanning applier shows as CPU/IO-bound on one table with no progress on the position. The cure is a primary key or a suitable unique index on both sides. ## Cause 4: the replica is simply weaker or busier Replicas are frequently provisioned smaller, live on cheaper storage, take the nightly backup, feed an ETL extract, or serve heavy analytical reads. Replay competes with all of that for I/O and buffer cache. A replica whose working set does not fit in memory pays a random read for every page the applier must modify, while the primary had those pages hot. This is why a replica can lag under exactly the workload the primary handles comfortably. ## Cause 5: replay is blocked, not slow On PostgreSQL hot standbys, replay of WAL that removes rows still visible to a long-running standby query creates a **recovery conflict**. Depending on configuration, replay pauses up to `max_standby_streaming_delay` and then cancels the offending query. So a single analyst running a 40-minute report can pin replay and manufacture 40 minutes of lag for every reader. Turning on `hot_standby_feedback` prevents the conflict by making the primary retain the needed row versions — trading replica lag for table bloat and delayed cleanup on the primary. That trade is a real decision, not a free fix. Similar blocking happens on any engine when the applier waits on a lock held by a local session (for example, a session on the replica holding a lock on a table the applier must modify in a writable/logical setup). ## A narrowing procedure 1. Split receive vs apply; stop here if the gap is before receive. 2. Ask whether the applier is **working or waiting** — CPU/IO busy versus blocked on a conflict or lock. 3. If working: what is it executing? Identify the table and the transaction; check its size and whether it is a bulk/DDL event. 4. Check the row-identity/index situation for that table on both sides. 5. Correlate lag onset with the primary's write pattern — batch jobs, cron, deploys, backfills — and with replica-side activity such as backups and long reports. 6. Distinguish a **drainable spike** (lag rises then falls; capacity is adequate) from a **sustained deficit** (lag rises monotonically; apply throughput is below write throughput and only a structural change fixes it).