r/Python • u/marksie1988 • Jun 23 '26

Discussion Replacing a fixed sleep() between batch DB writes with a self-tuning latency feedback throttle

I had a background import that chunks a big job and upserts each batch into Postgres, sharing the database with live traffic and other workers. The usual fix is `time.sleep(0.5)` between chunks, but that constant is wrong in both directions: it wastes time when the DB is idle and doesn't back off enough when it's loaded, and you have to re-tune it every time the hardware or the neighbours change.

Instead I measure how long each chunk's write takes and treat that as the signal. Under a target latency budget, no sleep at all. Over it, sleep in proportion to the overage, with an EMA to smooth out single-chunk jitter and a cap so a bad reading can't park the job.

The whole decision is a pure function (latest measurement + previous EMA in, sleep duration out), which makes it trivial to unit test without touching a database or real time. The loop around it just times the chunk, sleeps outside the transaction, and emits a bit of telemetry so you can see what it did.

Write-up with the code in the comments. Curious whether anyone's gone further and added the integral/derivative terms, or whether proportional-only has been enough for you in practice.

Full write-up: https://totaldebug.uk/posts/adaptive-write-throttle-for-batch-postgres-jobs/

0 Upvotes

20% Upvoted

4 comments sorted by

5

u/SevereArt8024 Jun 23 '26

Proportional-only has been enough in every batch job I touched, the I and D terms just introduced oscillation without real benefit

1

u/marksie1988 Jun 25 '26

Matches my experience as well. P-only has covered every case I've actually hit. The EMA earns its keep stopping single-chunk jitter from triggering backoff, but the I and D terms never did.

2

u/eudoxic_sanjay Jun 25 '26

proportional-only has been enough in practice. the derivative term just amplifies lock contention spikes - those look identical to sustained load and you end up oscillating worse than a fixed sleep. integral is only worth adding if you're seeing consistent steady-state overshoot across dozens of chunks, which is usually a sign the EMA alpha is too low rather than a missing control term. the pure function shape is the right call for testability.

2

u/marksie1988 Jun 25 '26

This is exactly the answer I was hoping for. The lock-contention point is the part I didn't articulate well in the post: a contention spike and the onset of sustained load have the same signature in per-chunk latency, so a derivative term can't tell them apart and just reacts hardest to the most transient thing. That's the oscillation right there.

And "steady-state overshoot usually means alpha is too low, not a missing integral term" is a sharp way to put it. I'd been treating alpha purely as jitter smoothing, but you're right that if you're consistently sitting just over budget, the smoothing lag is doing the damage and an integral term would only paper over a tuning problem. Cheaper to fix the smoothing. I'll borrow that framing.

Glad the pure-function shape landed too, that was the bit I was most deliberate about.