DB Replica Lag Catch-up Calculator

Current lag backlog:

Prefer a byte backlog over timestamp lag because catch-up time depends on work remaining.

Primary write generation:

Use a representative sustained window, not a brief spike, unless the spike will persist.

Replica apply throughput:

Use replay throughput after storage and transaction-shape limits, but before the modeled safety reserve.

Catch-up target window:

The target controls required apply capacity and the inside/after-target decision.

Apply efficiency:

Lower values make the plan more conservative when peak throughput is not sustainable.

Apply reserve:

Use zero only when all observed apply capacity can safely be spent on catch-up.

Scenario write throttle:

Compare a temporary traffic reduction, queue hold, or maintenance pause.

Scenario apply boost:

Compare faster storage, more replay workers, a larger instance, or tuned apply behavior.

Replica inputs need attention

{{ issue.message }}

Replica label:

Blank is neutral; add an export label when comparing several replicas.

Retained log limit:

Zero is neutral and omits retained-log pressure from the verdict.

Chart horizon override:

Zero uses an automatic horizon based on the target and finite catch-up ETA.

Signal	Status	Evidence	Operator move	Copy
{{ row.cells[0] }}	{{ row.cells[1] }}	{{ row.cells[2] }}	{{ row.cells[3] }}

Scenario	Write rate	Apply rate	Net drain	Catch-up ETA	Target	Copy
{{ row.cells[0] }}	{{ row.cells[1] }}	{{ row.cells[2] }}	{{ row.cells[3] }}	{{ row.cells[4] }}	{{ row.cells[5] }}

A replica catches up only when it applies changes faster than the primary creates new ones. An 18 GiB backlog can shrink rapidly on a quiet system, remain almost unchanged when apply capacity barely exceeds write traffic, or grow without limit when incoming work is faster than replay.

Lag is often reported as a time value, but vendor lag fields do not always predict catch-up time. Some describe the age of the last applied transaction, the delay of a recent synchronous operation, or a sampled difference between log positions. A catch-up estimate needs a backlog measured in bytes and sustained rates measured over the same interval.

Backlog is the volume of log or change data that has not yet been applied.
Write generation is the continuing rate at which the primary adds new work.
Apply throughput is the replica's raw replay or apply rate before efficiency and reserve adjustments.
Net drain is the effective apply rate left after subtracting new writes.

Efficiency and reserve serve different purposes. Efficiency discounts an optimistic throughput observation for stalls, transaction shape, storage pressure, network delay, or parallelism limits. Reserve then holds back part of the adjusted capacity so the estimate does not assume every available unit of throughput remains usable throughout the recovery window.

Retained logs create a second deadline. Even when the replica can eventually drain its backlog, the primary or archive must keep every required log segment available until it is consumed. If the backlog is still growing, remaining retention headroom can disappear before the target window closes and force a rebuild or re-seed.

A modeled improvement is not yet an operational change. Throttling writes can affect application latency and business throughput, while increasing apply capacity may move the bottleneck to storage, locks, network, or a single-threaded replay stage. Compare scenarios first, then verify the chosen action against live replication status and host telemetry.

Catch-up is also different from failover readiness. A backlog that reaches zero does not prove transaction consistency, promotion safety, endpoint fencing, or recovery-point compliance. Those checks still belong in the database runbook.

How to Use This Tool:

Use measurements from one stable observation window. Mixing a peak write rate with an average apply rate can create a scenario that never existed.

Enter the Current lag backlog as MiB, GiB, or TiB. Use a byte or log-position difference rather than a timestamp-lag field.
Enter Primary write generation and Replica apply throughput with their source units, then set the Catch-up target window.
Adjust Apply efficiency and Apply reserve to reflect sustained performance rather than a short peak.
Add the real Retained log limit when loss of required logs is possible. A zero value deliberately leaves retention unmodeled.
Set a Scenario write throttle and Scenario apply boost, then compare their catch-up estimates with the baseline.
Correct any Replica inputs need attention message before using the readiness status or trajectory.

Interpreting Results:

The net drain rate decides whether a finite catch-up estimate exists. A positive value reduces backlog; zero or a negative value means continuing writes consume all effective apply capacity.

Catch-up fits the target means net drain is positive and the calculated time is less than or equal to the target window.
Catch-up misses the target means backlog is shrinking, but the estimate is greater than the target.
Backlog is still growing means effective apply throughput is less than or equal to write generation, so there is no finite ETA.
Retention risk means modeled headroom runs out within the target window while backlog is growing. Retention exhausted means the current backlog is already at or above the entered limit.

Before acting, confirm that the backlog and rates still match live telemetry. Locks, failed apply workers, missing logs, storage saturation, or a changed write mix can invalidate a previously sound estimate.

Technical Details:

The model treats replica lag as a stock-and-flow problem. Backlog is the stock, primary writes are an inflow, and effective apply throughput is an outflow. MiB is the common backlog unit, MiB per minute is the common rate unit, and minutes are the common time unit.

Formula Core

Raw apply throughput is discounted by both the efficiency percentage and the reserved share. Net drain then subtracts primary write generation.

\begin{matrix} A_{effective} & = & A_{raw} (\frac{e}{100}) (1 - \frac{r}{100}) \\ N & = & A_{effective} - W \end{matrix}

A_raw is observed apply throughput, e is apply efficiency, r is apply reserve, W is primary write generation, and N is net drain. Efficiency must be 10% to 100%; reserve must be 0% to 50%.

A positive net drain produces a catch-up time. The required rate equation also shows how much effective apply throughput is needed to meet a specific deadline.

\begin{matrix} T_{catch-up} & = & \frac{B}{N} when N is greater than 0 \\ A_{required} & = & W + \frac{B}{T_{target}} \end{matrix}

B is current backlog and T_target is the target window. When N is less than or equal to zero, catch-up time is deliberately reported as no finite ETA. Additional apply capacity is the positive difference between the required and current effective rates.

Retention Rule Core

Retention is modeled as a capacity limit rather than a vendor-specific setting. Headroom is the entered retained-log limit minus current backlog. When backlog grows, the exhaustion time divides that positive headroom by the absolute growth rate.

\begin{matrix} H & = & L - B \\ T_{retention} & = & \frac{H}{| N |} when H is positive and N is negative \end{matrix}

Replica catch-up state boundaries
State	Exact rule	Meaning
Inside target	Net drain > 0 and ETA ≤ target	Backlog reaches zero within the selected window.
After target	Net drain > 0 and ETA > target	Backlog falls, but not quickly enough.
Falling behind	Net drain ≤ 0	Backlog is flat or increasing.
Retention at risk	Headroom > 0 and exhaustion time ≤ target	Required logs may be lost within the target window.
Retention exhausted	Headroom ≤ 0	Current backlog is already at or beyond the entered limit.

Scenario transformations

The write-throttle scenario multiplies the write rate by 1 minus the throttle percentage. The apply-boost scenario multiplies effective apply throughput by 1 plus the boost percentage. Six comparisons are produced: baseline, throttle, boost, both changes, paused writes, and no apply reserve. Each scenario reuses the same backlog and target window.

Worked mechanism path

With an 18 GiB backlog, 450 MiB/min of new writes, 720 MiB/min raw apply throughput, 92% efficiency, and 5% reserve, effective apply throughput is 629.28 MiB/min. Net drain is 179.28 MiB/min, so 18,432 MiB clears in about 102.81 minutes. That is inside a two-hour target. A 25% write throttle would lower writes to 337.5 MiB/min and shorten the modeled ETA to about 63.17 minutes.

Limitations:

The estimate assumes constant rates and a byte backlog that is comparable with the entered throughput. Replication systems can depart sharply from that assumption.

Transaction size, conflicts, locks, parallel apply, storage latency, network delay, and recovery activity can change sustained throughput.
Vendor time-lag fields may describe transaction visibility or recent commit delay rather than remaining catch-up duration.
A retained-log limit may be enforced only at checkpoints or by provider policy, and archived logs may change the recovery path.
Use live replication status, error fields, log availability, and failover checks before promotion or re-seeding decisions.

References:

The Cumulative Statistics System, PostgreSQL Documentation.
Replication Configuration, PostgreSQL Documentation.
SHOW REPLICA STATUS Statement, MySQL 8.4 Reference Manual.
How to promote a replica to primary in MySQL or MariaDB, Simplified Guide.