Backfilling Data in Rails Without Locking

A new region column needs values for forty million existing orders. The first attempt was one line in a migration — Order.update_all("region = CASE ship_country WHEN 'DE' THEN 'eu' ... END") — and it ran as a single UPDATE inside the migration’s transaction for twenty-five minutes. Every row it touched stayed locked until the end, so checkout requests updating their own orders queued behind it; replicas received the change as one giant transaction and fell minutes behind; and the deploy pipeline timed out, rolling the whole thing back and leaving nothing done. Backfills are data work, not schema work, and they need a different shape: small batches, each its own short transaction, paced to what the database and replicas can absorb, and resumable when interrupted. This guide builds that shape in Rails. It is part of Rails Active Record Migrations.

Symptom / Error Signatures

A backfill written as a single statement or a single transaction shows these signals:

• pg_stat_activity shows the migration’s session with an xact_start minutes old, and application sessions waiting on transactionid locks for rows it has updated.
• Replica lag is flat during the backfill and then spikes sharply when it commits.
• n_dead_tup on the table rises steeply, and autovacuum cannot remove the old row versions until the transaction ends.
• The deploy fails with a timeout, or ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR: canceling statement due to statement timeout, and all progress is lost because the transaction rolled back.
• With find_each and per-record save, the backfill is instead extremely slow and fires callbacks and validations for every row.

Root Cause Analysis

Three properties of a naive backfill cause the damage. It runs inside the migration transaction, so its row locks and dead tuples accumulate until the very end, and replicas receive it only at commit. It is one statement or one loop, so there is nowhere to pause, measure lag or resume after failure. And it runs as part of the deploy, so its duration becomes the deploy’s duration.

Rails provides the building blocks to fix all three. in_batches(of: n) walks the table by primary key and yields relations of n rows; calling update_all on each relation issues one set-based UPDATE ... WHERE id IN (...) or id-range statement per batch, with no model instantiation or callbacks. Running outside a transaction — in a migration with disable_ddl_transaction!, or better, in a background job — makes each batch commit on its own. A short sleep between batches, and a check of replica lag, keeps the pace sustainable, using the reasoning in tuning backfill batch size against replication lag.

Immediate Mitigation

1. If a monolithic backfill is hurting production, cancel it. It will roll back entirely, which is painful but immediately releases locks and stops the replica backlog from growing.

-- PostgreSQL · requires pg_signal_backend · WARNING: rolls back every row updated so far
SELECT pid, now() - xact_start AS xact_age, left(query, 80) FROM pg_stat_activity
WHERE query ILIKE 'UPDATE "orders"%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Rewrite it as batched and idempotent. Filter on the rows that still need work so the backfill can be stopped and restarted freely.

# Ruby · app/jobs/backfill_order_region_job.rb · runs in a background worker, not in the deploy
# WARNING: each batch commits independently; the job must remain idempotent (region IS NULL filter).
class BackfillOrderRegionJob < ApplicationJob
  BATCH = 5_000
  MAX_LAG_SECONDS = 2

  def perform
    Order.where(region: nil).in_batches(of: BATCH) do |batch|
      wait_for_replicas
      batch.update_all(<<~SQL.squish)
        region = CASE ship_country WHEN 'DE' THEN 'eu' WHEN 'FR' THEN 'eu'
                                   WHEN 'US' THEN 'na' WHEN 'CA' THEN 'na' ELSE 'other' END
      SQL
      sleep 0.05
    end
  end

  private

  def wait_for_replicas
    loop do
      lag = ActiveRecord::Base.connection.select_value(
        "SELECT COALESCE(EXTRACT(EPOCH FROM max(replay_lag)), 0) FROM pg_stat_replication").to_f
      break if lag < MAX_LAG_SECONDS
      sleep 1
    end
  end
end
# ROLLBACK PATH: the column is nullable; reverting code leaves it unused, and a later migration can drop it.

3. Enqueue the job from a migration or a one-off task. Keep the schema migration fast; it only adds the column.

# Ruby · db/migrate/20260918120000_add_region_to_orders.rb
class AddRegionToOrders < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :region, :string   # nullable, instant
  end
end
# then, after deploy: bin/rails runner "BackfillOrderRegionJob.perform_later"

Permanent Fix / Long-Term Pattern

Treat backfills as a first-class, separate step in the expand-and-contract lifecycle. The schema migration adds the new structure; a background job fills it, idempotently, in throttled batches; the next release tightens constraints once the job reports completion. That sequence is framework-neutral and described in Backfill Optimization; Rails just supplies in_batches, update_all and Active Job.

Make batches efficient on large tables. in_batches on a filtered relation (where(region: nil)) plucks ids for each batch; with an index on the filter column, or on PostgreSQL a partial index WHERE region IS NULL, those lookups stay fast as the backfill progresses. For the very largest tables, iterate explicit id ranges (where(id: start...start + BATCH)) to avoid the id plucking entirely, per cursor-based vs keyset pagination for large backfills. Record progress (last id, rows done) somewhere visible, and alert if the job stalls.

When the backfill must run inside a migration — for example because a later migration in the same deploy depends on it — use disable_ddl_transaction! and the same batched, idempotent loop, and keep that migration free of schema operations.

Verification Checklist

• The backfill runs outside the schema migration’s transaction, and each batch commits independently.
• Batches use update_all (no per-row callbacks) and a filter that makes reruns skip finished rows.
• Replica lag stayed under its threshold throughout a staging run on production-sized data.
• Progress is observable: remaining rows can be counted and the job logs its last processed id.
• Constraint tightening ships only after the remaining-row count reaches zero.

Frequently Asked Questions

Why not use find_each and save? It instantiates every record, runs validations and callbacks, and issues one UPDATE per row with all changed attributes. For a backfill that is orders of magnitude slower than a set-based update_all per batch, and callbacks may have side effects you do not want to trigger for historical data.

Does in_batches hold a transaction open? No. in_batches only paginates; transactions are determined by where the code runs. Inside a normal PostgreSQL migration everything shares the migration’s transaction, which is why the backfill belongs in a job or a migration with disable_ddl_transaction!.

How big should a batch be? Start with a few thousand rows and adjust based on the time per batch and replica lag. Aim for batches that finish in well under a second; if lag rises, shrink the batch or lengthen the sleep rather than letting the backfill race ahead.

What if the backfill job fails halfway? Rerun it. Because each batch committed and the relation filters on rows that still need work, the rerun continues where the previous attempt stopped without redoing or double-applying anything.