Running Backfills as Queued Background Jobs

The deploy pipeline has a 30-minute timeout, and the migration that adds and fills invoices.tax_region needed four hours for the fill. The pipeline killed it at minute 30; the migration tool recorded it as failed; the next deploy was blocked until someone repaired the state by hand. Schema changes and data changes have very different shapes: DDL should take seconds and run once, as a gated deploy step; a backfill of hundreds of millions of rows takes hours, should be throttled against production load, needs to survive restarts, and has no business holding up a deploy. The fix is structural — the migration adds the column and enqueues work; background workers do the work. This guide shows how to split a backfill into queued chunks, pace it, track it to completion, and connect it to the next schema step. It belongs to Backfill Optimization.

Symptom / Error Signatures

Backfills embedded in migrations cause:

• Deploy pipelines timing out on the migration step, and migration tools left in a failed or dirty state (Dirty database version, P3009, a failed Flyway entry).
• Long transactions from the migration holding locks or preventing vacuum, visible as an old xact_start in pg_stat_activity.
• No way to pause the backfill when production load rises without killing the deploy.
• A single thread doing all the work, so the backfill is slower than the database could safely sustain.

Root Cause Analysis

Migration runners are built for short, ordered, transactional steps executed once per deploy. A large backfill violates every one of those assumptions: it is long, it benefits from limited parallelism, it must be paced against live traffic, it must be restartable, and its completion is a precondition for later schema steps rather than part of the current one. Job queues — Sidekiq, Celery, BullMQ, Oban, a database-backed queue — are built for exactly those properties: retries, concurrency limits, scheduling and visibility.

The shape that works is a coordinator plus chunks. The coordinator divides the key space into chunks (for example 100,000 ids each), enqueues one job per chunk, and records the plan. Each chunk job processes its range in small batches, idempotently, and records completion. Concurrency is capped at the queue level. Pacing uses a shared signal — replica lag or a pause flag — checked before each batch.

Immediate Mitigation

If a migration-embedded backfill has failed or is blocking deploys:

1. Repair the migration state so the schema part is recorded correctly — the column exists — using your tool’s repair command (migrate force, prisma migrate resolve, flyway repair). The schema change itself was short and committed or rolled back cleanly; only the data part is incomplete.

2. Replace the backfill with a queued job. The migration keeps only the DDL and, optionally, enqueues the coordinator. A progress table makes the job observable:

-- PostgreSQL · migration role · tracks chunk completion for the backfill
CREATE TABLE IF NOT EXISTS backfill_chunks (
  job      text   NOT NULL,
  lo       bigint NOT NULL,
  hi       bigint NOT NULL,
  done_at  timestamptz,
  PRIMARY KEY (job, lo)
);
-- ROLLBACK PATH: DROP TABLE backfill_chunks;  (after the job is complete)

3. Enqueue chunks from the coordinator.

# Python · Celery coordinator task · runs once, enqueues one task per id range
# WARNING: route chunk tasks to a dedicated queue whose worker concurrency is capped (e.g. 4).
@app.task
def plan_tax_region_backfill(chunk=100_000):
    with db.cursor() as cur:
        cur.execute("SELECT min(id), max(id) FROM invoices")
        lo, hi = cur.fetchone()
        for start in range(lo, hi + 1, chunk):
            cur.execute("INSERT INTO backfill_chunks (job, lo, hi) VALUES (%s, %s, %s) ON CONFLICT DO NOTHING",
                        ("invoices_tax_region", start, start + chunk - 1))
            backfill_tax_region_chunk.apply_async(args=[start, start + chunk - 1], queue="backfill")
    db.commit()

4. Process each chunk in small, paced, idempotent batches.

# Python · Celery chunk task · batches of 2,000 with a lag check before each
@app.task(bind=True, max_retries=10, default_retry_delay=30, acks_late=True)
def backfill_tax_region_chunk(self, lo, hi, batch=2000):
    for start in range(lo, hi + 1, batch):
        wait_until_replica_lag_below(seconds=2)
        with db.cursor() as cur:
            cur.execute("""UPDATE invoices SET tax_region = compute_tax_region(country, postcode)
                           WHERE id BETWEEN %s AND %s AND tax_region IS NULL""",
                        (start, min(start + batch - 1, hi)))
        db.commit()
    with db.cursor() as cur:
        cur.execute("UPDATE backfill_chunks SET done_at = now() WHERE job = %s AND lo = %s",
                    ("invoices_tax_region", lo))
    db.commit()

Permanent Fix / Long-Term Pattern

Adopt a team rule: migrations change schema; jobs change data. Any data change touching more than a few thousand rows ships as a background job, with the migration adding structure and the next release — gated on the job’s completion — adding constraints or removing old structure. Give backfills their own queue with capped concurrency, so they never starve user-facing jobs, and a global pause switch (a feature flag or a row in a control table) that every chunk checks. The pacing logic is covered in throttling backfills to protect OLTP latency.

Make completion a gate, not a guess. The release that tightens the column checks SELECT count(*) FILTER (WHERE done_at IS NULL) FROM backfill_chunks WHERE job = ... equals zero and a final remaining-rows count is zero. Framework-specific versions of the same pattern appear in backfilling data in Rails without locking, and resumability within a single worker in resuming an interrupted backfill from a checkpoint.

Verification Checklist

• The migration contains only DDL (and at most an enqueue); no large UPDATE runs in the deploy.
• Backfill jobs run on a dedicated queue with capped concurrency and a pause switch.
• Each chunk processes small idempotent batches and checks replica lag before each.
• A progress table records chunk completion, and failed chunks retry automatically.
• The follow-up release is gated on all chunks done and zero remaining rows.

Frequently Asked Questions

Why not just raise the deploy timeout? Because the problem is not only time. A backfill inside a migration cannot be paused, parallelised, or retried piecemeal, holds a long transaction in many tools, and blocks every other deploy while it runs.

How many workers should process chunks? Few — often two to four. The limit is the database’s spare capacity and replica apply rate, not the queue’s throughput. Start low, watch lag and latency, and increase cautiously.

What if a chunk job fails permanently? Its row in the progress table stays incomplete, so the completion gate does not pass. Inspect the error, fix the cause (often bad data in a few rows), and re-enqueue that chunk; idempotent batches make reruns safe.

Should the migration enqueue the job automatically? It can, but some teams prefer an explicit operator step after the deploy, so the backfill starts at a chosen time. Either way, the job must be safe to enqueue twice.