Throttling Backfills to Protect OLTP Latency

Replica lag is flat. The backfill is idempotent, pages by keyset, and every batch commits in under a second. And yet the checkout endpoint’s p99 latency has doubled since 21:00, the exact minute the backfill worker started, and the payment team is asking why authorizations are timing out. Nothing on the replicas is wrong — the damage is on the primary, where the backfill and live OLTP traffic are competing for the same buffer cache, the same I/O queue, and the same lock manager. Sizing batches against replication lag, as covered in Tuning Backfill Batch Size Against Replication Lag, protects your followers but says nothing about how much of the primary’s headroom the backfill is quietly eating. This page is about the other half of the throttle: pacing the job against live user-facing latency so it always yields to the requests that pay the bills.

The core idea is a controller that watches two signals at once — the primary’s p99 statement latency and the slowest replica’s lag — and slows or pauses the backfill the moment either one crosses a budget. A backfill has no deadline that matters more than the SLO of live traffic, so it should always be the process that gives way.

Symptom / Error Signatures

The backfill logs look healthy; the pain surfaces in application and database latency metrics, not in errors from the job itself.

• Application APM shows p99 (and sometimes p95) latency on OLTP endpoints stepping up at the exact timestamp the backfill started, with p50 barely moving — the tell that a tail, not the whole distribution, is being starved.
• PostgreSQL: pg_stat_statements shows rising mean_exec_time on ordinary OLTP queries while a batched UPDATE dominates total_exec_time; wait_event_type on live sessions shifts toward IO or LWLock (BufferContent, WALWrite).
• MySQL: SHOW ENGINE INNODB STATUS reports growing History list length and buffer-pool pressure; performance_schema.events_statements_summary_by_digest shows OLTP digests with climbing AVG_TIMER_WAIT.
• Client-side timeouts that were never seen before: canceling statement due to statement timeout (PostgreSQL) or Lock wait timeout exceeded; try restarting transaction (MySQL error 1205) on user requests, not on the backfill.
• Connection-pool saturation as slow OLTP queries hold their connections longer, the connection pool exhaustion cascade that turns a latency blip into an availability incident.

If pausing the backfill for sixty seconds drops p99 back to its baseline, you have confirmed the backfill — not a bad query plan or a traffic spike — is the cause.

Root Cause Analysis

Replication lag and OLTP latency are two different resources, and a backfill can be gentle on one while brutal to the other. Lag is a downstream cost: how fast followers replay the change stream. Latency is an on-primary cost: how much the backfill’s reads and writes contend with live queries for buffer cache pages, I/O bandwidth, WAL/redo throughput, and lock-manager partitions. A small batch that keeps lag flat can still evict hot OLTP pages from the buffer cache, forcing user queries to hit disk — invisible to any lag metric.

The reason the tail moves first is queuing. Under normal load the primary has spare capacity, so the median request never waits. Add a steady stream of backfill I/O and the busiest moments — already the slowest requests — tip over the knee of the latency curve. p50 shrugs; p99 spikes. This is why a throttle keyed on average load reacts too late: by the time the mean moves, the tail has already breached its SLO.

The two throttle strategies are complementary, not alternatives:

Because each is blind to what the other measures, a production-grade backfill throttles on both, taking the more conservative of the two decisions each cycle. The throttle is not a fixed sleep; it is closed-loop feedback, the same adaptive shape used for resumable jobs in Optimizing Backfill Scripts for Zero-Downtime Deploys.

Immediate Mitigation

When p99 is already climbing and the backfill is the suspect, act in this order. The goal is to relieve the primary right now, then resume under a latency budget.

1. Confirm the backfill is the cause before touching it. Pause the worker (do not kill it — an idempotent, keyset-cursor job resumes cleanly) and watch p99 for one minute.

# bash · run from the deploy host as the operator · safe anytime.
# Context: sends SIGTERM so the worker stops AFTER the current batch commits, leaving no half-write.
kill -TERM "$(cat /var/run/backfill.pid)"   # graceful pause; the IS NULL guard makes a later resume a no-op

2. Read the primary’s current p99 directly so you are throttling on data, not on a hunch.

-- PostgreSQL · run on the PRIMARY as a monitoring role · read-only, safe anytime.
-- Context: needs pg_stat_statements; approximates p99 via mean+stddev per statement.
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2)                       AS mean_ms,
       round((mean_exec_time + 2.33 * stddev_exec_time)::numeric, 2) AS approx_p99_ms
FROM   pg_stat_statements
WHERE  calls > 100
ORDER  BY approx_p99_ms DESC
LIMIT  10;

-- MySQL 8.0 · run on the PRIMARY · read-only, safe anytime.
-- Context: 95th-percentile wait per digest straight from Performance Schema.
SELECT DIGEST_TEXT,
       COUNT_STAR                       AS calls,
       ROUND(AVG_TIMER_WAIT/1e9, 2)     AS avg_ms,
       ROUND(QUANTILE_95/1e9, 2)        AS p95_ms
FROM   performance_schema.events_statements_summary_by_digest
ORDER  BY QUANTILE_95 DESC
LIMIT  10;

3. Resume with a hard latency ceiling and a smaller batch. Do not restart at full speed. Give the worker a p99 budget and a low-write-window flag so it self-limits.

# bash · run as a single post-deploy worker (never in parallel) · safe to re-run.
# Context: --max-p99-ms is the OLTP SLO the backfill must never breach; --max-lag-seconds guards replicas too.
./bin/backfill --table orders --column region_code \
  --batch 1000 --max-p99-ms 120 --max-lag-seconds 2 --pause-seconds 2

4. If p99 will not recover even paused, the backfill is not the only pressure — check for a competing job or a traffic surge, and defer the backfill to an off-peak window. A backfill has no urgency that justifies breaching a live SLO.

Permanent Fix / Long-Term Pattern

A --max-p99-ms flag is the right instinct, but a static ceiling with a fixed batch still cannot adapt to how load shifts across the day. The durable pattern is a controller that samples both signals every iteration and treats batch size and sleep as controlled variables: additive-increase when there is headroom, multiplicative-decrease the moment either budget is threatened. Whichever signal is stricter wins the cycle.

# Adaptive, dual-signal backfill throttle.
# Context: single worker, post-deploy job, idempotent keyset upserts; safe to pause/resume.
P99_CEIL_MS   = 120    # OLTP SLO on the primary — the tail must stay under this
LAG_CEIL_S    = 2.0    # slowest replica may not exceed this
BATCH_MIN, BATCH_MAX = 500, 20000
batch, last_id = 1000, 0

def budget_pressure():
    # Normalize each signal to a 0..1+ ratio of its budget; the worst one governs.
    p99 = primary_p99_ms() / P99_CEIL_MS
    lag = max_replica_lag_seconds() / LAG_CEIL_S
    return max(p99, lag)

while True:
    rows, last_id = upsert_next_batch(last_id, batch)   # keyset cursor, never OFFSET
    if rows == 0:
        break
    pressure = budget_pressure()
    if pressure >= 1.0:                                 # over budget on some signal
        batch = max(BATCH_MIN, batch // 2)              # multiplicative decrease
        sleep_until(lambda: budget_pressure() < 0.6)    # hysteresis: resume well under budget
    elif pressure < 0.5:                                # comfortable headroom on both
        batch = min(BATCH_MAX, batch + 500)             # additive increase

The primary_p99_ms() helper should read the same number your alerting does — ideally a real p99 exported to your metrics system, or the pg_stat_statements / Performance Schema approximation above sampled over a short window so a single slow statement does not thrash the loop. Two structural rules keep the controller honest. First, iterate with a keyset cursor rather than LIMIT/OFFSET, for the scan-cost reasons detailed in Cursor-Based vs Keyset Pagination for Large Backfills. Second, write each batch as an idempotent upsert so any pause, retry, or crash is harmless — the discipline formalized in Idempotent Script Design. For the surrounding resumability, metrics, and crash-recovery concerns that wrap this loop, the parent Backfill Optimization section is the reference.

One more lever protects the tail without any application code: cap the backfill’s per-statement cost at the database. Set a tight statement_timeout on the worker’s session so a pathological batch can never hold resources long enough to poison p99, and keep the backfill role’s work off the buffer cache’s hot set where the engine allows it.

-- PostgreSQL · run at the start of each backfill session as the worker role · safe.
-- Context: bounds any single batch so it can never starve OLTP for more than this window.
SET statement_timeout = '3s';       -- a batch that exceeds this aborts and is retried smaller
SET lock_timeout      = '500ms';    -- never queue behind a live row lock; back off instead

Verification Checklist

• The backfill samples primary p99 latency (from pg_stat_statements, Performance Schema, or your metrics pipeline) at least once per batch, not on a fixed timer.
• The controller throttles on BOTH primary p99 and the slowest replica’s lag, taking the stricter of the two decisions each cycle.
• A latency ceiling pauses the job and a lower resume threshold restarts it (hysteresis), so the loop never thrashes at the budget line.
• The worker session sets a short statement_timeout and lock_timeout so no single batch can starve OLTP or queue behind a live lock.
• Pausing the worker for one minute measurably drops p99 back to baseline, proving the throttle signal is wired to the right metric.
• An independent APM alert fires on OLTP p99 regardless of the backfill, so an unthrottled run is caught even if the loop’s own probe fails.

Frequently Asked Questions

Why isn’t throttling on replica lag enough? Replica lag measures a downstream resource — how fast followers replay the change stream — and is completely blind to what the backfill does to the primary. A small batch can keep lag perfectly flat while still evicting hot pages from the primary’s buffer cache and stealing I/O from live queries, so user-facing p99 climbs even though every lag graph is green. Protecting live traffic requires a second signal read directly on the primary: its own statement latency.

Why does p99 spike while p50 barely moves? Because the tail of the latency distribution lives at the busy moments where requests already queue. Adding steady backfill I/O consumes the primary’s spare headroom, and the requests that were already slowest tip over the knee of the curve first. The median request still finds free capacity and shrugs. That asymmetry is exactly why you must throttle on a tail percentile, not on average load — the mean moves too late to protect the SLO.

How do I get a real p99 to throttle on without heavy instrumentation? Three options, cheapest first. Read an approximate p99 from pg_stat_statements (mean_exec_time + 2.33 * stddev_exec_time) or the exact QUANTILE_95/QUANTILE_99 columns in MySQL’s Performance Schema digest table. Or export a true p99 from your APM and have the worker poll that metric. Sample over a short rolling window so one anomalous statement does not cause the controller to thrash.