How to Backfill Millions of Rows in a Rails Database Without Downtime

Backfilling a new column on a table with forty million rows is a different problem than backfilling one with forty thousand. Below a certain size, a single bulk update just works. Above it, the same one-liner can lock the table, blow past a statement timeout, or bloat the transaction log enough to fill the disk.

Batch it, always

Invoice.where(currency: nil).in_batches(of: 2_000) do |batch|
  batch.update_all(currency: "EUR")
  sleep 0.05 # let replicas and autovacuum keep up
end

Each batch is its own transaction, so a failure partway through leaves you with a resumable checkpoint instead of a single all-or-nothing operation spanning the entire table. The small sleep between batches isn't laziness, it's giving replication lag and autovacuum room to keep pace with the write volume instead of falling permanently behind.

Run it outside the request and deploy cycle

A rake task or a background job, never a migration that runs during deploy. A migration blocking a deploy for twenty minutes while it backfills is a self-inflicted outage; a job that runs the same logic in the background, monitored and resumable if it fails, isn't.

Watch the database, not just the job

Replication lag, autovacuum activity and disk I/O are the metrics that actually tell you whether a backfill is safe to keep running at its current speed, not the job's own progress log. We throttle batch size and sleep interval dynamically against replication lag on anything touching a table with active production traffic, slowing down automatically if the database shows signs of falling behind.

Index-only backfills need special care

Populating a new column that will immediately get indexed doubles the write cost of every batch: the row update, and the index maintenance behind it. Where it's practical, we backfill the column first, then build the index concurrently afterward, rather than making every batch pay the index cost from the start.