Anatomy of a migration engine that survives being killed

Kill the worker at any second of a twelve-hour import and the answer has to be the same: restart it, lose at most twenty-five rows of redone work, duplicate nothing.

That single requirement produces almost every decision below. This is the whole architecture — seven layers, the SQL that enforces the important ones, and the six failure classes with the level each is caught at.

The system it describes moved 483,631 records in 12 h 29 m against an API that prices every request, with the database at 9–15% CPU throughout and zero failed tasks.

1. The shape of the problem

Half a million rows is not much data. It is a lot of time, spent talking to a third party that throttles you, times out, and meters what you spend.

Three properties fall out of that:

Nothing may run in one process. A twelve-hour loop is a twelve-hour window in which a deploy, an OOM or a rescheduled container destroys all progress.

Nothing may be held across a network call. Not a lock, not a transaction. The provider decides how long it takes to answer, and any lock you hold is a lock they are holding.

Every unit of work must be re-runnable. Not "idempotent if you are careful" — re-runnable by construction, because it will be re-run.

2. Topology: where the work actually executes

Two containers on one host. The web container runs Puma and executes no jobs at all — its queue configuration is :external, so a request thread can never be blocked behind a migration page. The jobs container is one process, one GVL, and inside it a scheduler per queue, each with its own thread pool.

3. Phases: a barrier, not a sequence

A run is a directed graph of phases. Each is cut into tasks, and a phase cannot start until every task in the phase before it has succeeded.

That fence is what lets the deal writer assume its contact already exists, which is what lets every writer be a straight-line function instead of a retry loop that waits for its parents.

The barrier is advanced by a fenced update, not by whoever happens to finish last. A task that thinks it is closing a phase must win a conditional write to do so, which is what stops two finishing tasks both opening the next phase.

4. Paging: cursor in a row, not on a stack

A page is 500 rows. The engine does not sit in a loop holding the collection's progress in memory — each page is a row in a table carrying its own cursor.

body = get(path, params.merge(start: start, limit: PAGE_SIZE))   # 500
pagination = body.dig("additional_data", "pagination") || {}
more  = pagination.key?("more_items_in_collection") ?
        !!pagination["more_items_in_collection"] : data.length >= PAGE_SIZE
start = next_start && next_start > start ? next_start : start + PAGE_SIZE

Three of those lines are scar tissue. One collection omits the pagination block entirely, so a full page has to mean "probably more" and costs one empty trailing fetch. Another ignores start and returns the whole set on every call — caught by noticing a page opens with the previous page's first id. A stale cursor falls back to offset arithmetic. MAX_PAGES backstops the rest.

Four stripes walk one collection at once, each owning every fourth page, so they never contend for the same offset.

A task is claimed by a fenced update — a conditional write that only one worker can win — so two workers can never hold the same page.

5. Windows: why 25, and why it is not a tuning knob

500 rows cannot be one transaction. Postgres caches at most 64 subtransaction ids per top-level transaction (PGPROC_MAX_CACHED_SUBXIDS, compiled in). Past 64 the snapshot is marked suboverflowed and visibility checks fall back to SLRU lookups on disk.

This path opens two savepoints per row: one so a raising row cannot poison the window, and one inside the ledger write, which must be its own or raise ActiveRecord::Rollback is swallowed by the outer block and commits a record whose claim was refused.

64 cached subxids ÷ 2 savepoints per row = 32 rows of headroom
WINDOW = 25                                 ← the sibling importer uses 50
                                              because it opens one per row

6. The ledger: one statement decides ownership

Every write goes through a unique index on (tenant, source, kind, source_id), and one statement resolves the claim:

INSERT INTO migration_records
  (tenant_id, source, kind, source_id, record_type, record_id, action, ...)
VALUES (...)
ON CONFLICT (tenant_id, source, kind, source_id) DO UPDATE SET
  record_type = EXCLUDED.record_type,
  record_id   = EXCLUDED.record_id,
  action      = EXCLUDED.action,
  reason      = EXCLUDED.reason
WHERE migration_records.action = 'skipped'
RETURNING id

RETURNING yields a row for a fresh insert or a takeover, and nothing when the slot is genuinely owned. Three outcomes from one round trip:

The practical consequence: a re-run needs to know nothing about previous runs. 451,772 already-written rows are each refused in one statement.

7. The rate limiter: lease before the call, not during it

The provider prices requests rather than counting them — a single-record read costs 2 tokens, a list costs 20 — against a daily allowance of roughly 30,000 × plan multiplier × seats. There is also a short burst window measured in requests per two seconds.

Two rules make this survivable.

Take the burst lease on the job thread, before fanning out. Email bodies are fetched four at a time on threads that hold no database connection. Those threads must not each take a row lock on the shared budget row, so the job thread reserves the slots first and the fetch threads spend them:

def reserve_slots!(count) = count.times { await_slot! }

The waiting and the lock happen once, on one thread, before any of the concurrency starts.

Spend 90% of the window, not 100%. Our count and the provider's disagree by whatever is in flight plus clock skew on the boundary, and the disagreement always leans toward a rejection. Giving up a tenth of the throughput removes the rejections that were costing far more than a tenth in released tasks and re-fetched pages.

8. Six failure classes, six containments

This is the part that decides whether the previous seven sections are architecture or decoration.

Two of those rows were bought expensively. An earlier version counted a lock conflict as a row error: the run reported success with 10 of 3,000 deals silently missing. A version that retried it in place lost 1,002, because the retry's savepoint ran inside an already-aborted transaction.

9. What it did in production

483,631 records · 4,966 jobs · 12 h 29 m
0 failed tasks · 5 rows not imported
CPU 9–15% · connections 21–31 · storage 0.6 GB

Throughput was not flat, and the shape is the design working:

midday      215 emails/min     nothing contending
mid-run      75 emails/min     201 jobs waiting in backoff
evening     439 emails/min     backoff cleared itself

Nobody intervened at any point in that curve. The engine traded speed for compliance while the provider pushed back and took it again when it stopped.

It ended by stopping itself: the allowance hit the reserve floor, the run parked, three log lines were written, the queue drained to 3 jobs and the database went idle. One phase was unfinished, with 44,092 of 57,316 parents already marked — so a resume asks about the remaining 13,224 and nothing else.

10. The constants, and what set them

None of those is a preference. Each is the largest value that keeps a specific failure bounded, and the reason is worth more than the number — because when the provider changes, the number moves and the reasoning does not.