Rails Data Migrations: Safe Backfills with data-migrate, Maintenance Tasks, and Batched Updates

The deploy had been running for six minutes when the DBA walked over to my desk. She did not say anything. She just turned her laptop around. The pg_locks table was packed wall to wall: a single UPDATE orders SET status = 'shipped' WHERE ... was holding an ACCESS EXCLUSIVE lock on the orders table and the entire application had stopped accepting requests.

The developer who wrote the migration had mixed up two fundamentally different things: a schema migration and a data migration. The schema migration added a status column, correct, a DDL statement that runs in milliseconds. Immediately after it, they had pasted two thousand lines of Ruby looping through every order record to populate the column. The migration ran inside a transaction. The transaction held an ACCESS EXCLUSIVE lock for the entire twelve minutes it took to touch four million rows. We rolled it back, but the application had been dead for six minutes by then.

After nineteen years of Rails I have seen this mistake in some form at least a dozen times. It is not stupidity — the logic is intuitive. You add a column, you fill it, you deploy. The problem is that Rails data migrations and Rails schema migrations are different operations with different safety profiles, and running one inside the other is how you take down production.

This post is the guide I hand to every team I work with: what the difference is, the three tools you can use to handle data migrations safely, and when to reach for each one.

Schema Migrations vs. Data Migrations: The Core Distinction

A schema migration changes the database structure: add_column, create_index, drop_table. These statements are DDL. In PostgreSQL, most DDL is transactional — if you wrap them in a BEGIN/COMMIT block and the migration fails, the schema change is rolled back cleanly. Rails wraps every migration in a transaction by default for exactly this reason.

A data migration changes the content of existing rows: populating a new column, splitting a denormalized field into two columns, recalculating computed values, moving records between tables. These statements are DML. They are also transactional, which sounds fine — but the transaction that wraps a data migration holds locks on every row it touches for the entire duration of the operation. On a four-million-row table, that lock duration is measured in minutes, not milliseconds.

The correct rule is simple: never run data migrations inside schema migrations. The schema migration adds or renames the column. A separate process, running outside a long transaction, fills the data. The two operations deploy independently, possibly days apart, and your application handles both states.

This is not a new idea, but it is one that Rails’s migration file convention actively works against. The db/migrate/ directory trains developers to put all database-touching changes in one place. The fix is to pick the right tool for data-specific changes and route them somewhere else.

The data-migrate Gem: Versioned Data Migrations

The data-migrate gem adds a second migration directory — db/data/ — for data-only changes. It maintains its own version history table (data_migrations) separate from schema_migrations, and provides Rake tasks that run data migrations independently of or alongside schema migrations.

Setup

# Gemfile
gem "data-migrate"

bundle install
bundle exec rails data_migrate:install:migrations
bundle exec rails db:migrate

The install step creates the data_migrations tracking table. From this point, two directories exist:

• db/migrate/ — schema changes only (DDL)
• db/data/ — data changes only (DML)

Generate a data migration the same way you generate a schema migration:

bundle exec rails generate data_migration BackfillOrderStatus

This creates db/data/20260915100000_backfill_order_status.rb.

Writing a Data Migration

class BackfillOrderStatus < ActiveRecord::Migration[7.1]
  def up
    Order.in_batches(of: 1000) do |batch|
      batch.where(status: nil, shipped_at: ..Time.current).update_all(status: "shipped")
      batch.where(status: nil).update_all(status: "pending")
    end
  end

  def down
    Order.where(status: ["shipped", "pending"]).update_all(status: nil)
  end
end

Two things to notice. First, in_batches — never update all rows in a single statement inside a data migration. A single UPDATE orders SET status = 'shipped' WHERE status IS NULL on four million rows holds a row-level lock on all four million rows simultaneously for the full update duration. Batches of 500 to 2000 rows keep each individual lock brief and give other queries a chance to run between batches.

Second, the down method. Most data migrations are difficult to reverse cleanly, but provide one if you can. If the rollback is truly destructive — for example, the migration deletes records that cannot be reconstructed — use disable_ddl_transaction! plus an explicit comment:

class DeduplicateSubscriptions < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def up
    # Find duplicate subscriptions (same user_id + plan_id) and keep only the newest.
    # This is irreversible: once the duplicates are gone, the data is gone.
    duplicate_scope = Subscription
      .select("user_id, plan_id, COUNT(*) as count")
      .group(:user_id, :plan_id)
      .having("COUNT(*) > 1")

    duplicate_scope.each do |dupe|
      Subscription
        .where(user_id: dupe.user_id, plan_id: dupe.plan_id)
        .order(created_at: :desc)
        .offset(1)
        .delete_all
    end
  end

  def down
    raise ActiveRecord::IrreversibleMigration
  end
end

disable_ddl_transaction! opts this migration out of Rails’s wrapping transaction. Long-running DML inside a transaction holds locks for the transaction’s full duration; without the transaction wrapper, each update_all commits immediately and releases its locks.

Running Data Migrations

The gem gives you three Rake tasks that matter in production:

# Run only schema migrations (the DDL deploy):
bundle exec rails db:migrate

# Run only data migrations (the DML deploy):
bundle exec rails data:migrate

# Run schema migrations, then data migrations:
bundle exec rails db:migrate:with_data

A typical deploy sequence on a schema-plus-data change:

1. Deploy the schema migration (db:migrate). The application runs with the new column, status is nil for existing records.
2. Deploy any application code that handles status: nil as a backward-compatible default.
3. Run the data migration (data:migrate) — this is a separate step, often a few minutes or hours after the application deploy.
4. Once all rows are backfilled, remove the nil-handling code in a final cleanup deploy.

This sequence means your application is live and serving traffic while the backfill runs. No lock, no downtime, no combined schema-and-data deploy that blocks for twelve minutes.

The data-migrate Gem vs. Schema Migrations in CI

In CI, run db:migrate:with_data instead of db:migrate to ensure both migration sets are applied. If you use database fixtures or seeds, the order is: schema migrate, then data migrate, then seed. Data migrations that depend on a clean schema being present should always run after db:schema:load + db:migrate.

Maintenance Tasks: Shopify’s Approach to Large-Scale Backfills

data-migrate handles versioned, one-shot data migrations well. For long-running backfills — the ones that take hours, touch hundreds of millions of rows, and need to survive a process restart — Shopify’s maintenance_tasks gem is the better tool.

maintenance_tasks provides a Rails engine with a web UI for running, pausing, resuming, and monitoring data tasks. Tasks are plain Ruby classes. Progress tracking, throttling, and cursor-based resumability are built in.

Setup

# Gemfile
gem "maintenance_tasks"

bundle install
bundle exec rails maintenance_tasks:install:migrations
bundle exec rails db:migrate

Mount the engine in config/routes.rb:

mount MaintenanceTasks::Engine => "/maintenance_tasks"

Restrict access in production — you do not want this UI exposed to the internet:

# config/routes.rb
authenticate :user, ->(user) { user.admin? } do
  mount MaintenanceTasks::Engine => "/maintenance_tasks"
end

Writing a Maintenance Task

bundle exec rails generate maintenance_tasks:task BackfillUserFullName

This creates app/tasks/maintenance_tasks/backfill_user_full_name_task.rb:

module MaintenanceTasks
  class BackfillUserFullNameTask < MaintenanceTasks::Task
    # Process 500 records per iteration
    def collection
      User.where(full_name: nil).select(:id, :first_name, :last_name)
    end

    def count
      collection.count
    end

    def process(user)
      user.update_columns(full_name: "#{user.first_name} #{user.last_name}".strip)
    end
  end
end

collection returns an ActiveRecord relation. The engine iterates over it in batches, calling process for each record. Between each batch, it checks whether the task has been paused or cancelled, writes a progress checkpoint to the database, and optionally sleeps according to a throttle condition.

Throttling to Protect Production

The most important feature in maintenance_tasks for large backfills is the throttle callback. Without throttling, a backfill iterates as fast as the database can handle — which, on a write-heavy production system, means competing directly with live traffic for I/O.

module MaintenanceTasks
  class BackfillUserFullNameTask < MaintenanceTasks::Task
    throttle_on(backoff: 30.seconds) do
      # Pause if the database primary is under load.
      # This checks a custom lag metric — adapt to your monitoring.
      ApplicationRecord.connection
        .execute("SELECT count(*) FROM pg_stat_activity WHERE state = 'active'")
        .first["count"]
        .to_i > 50
    end

    def collection
      User.where(full_name: nil).select(:id, :first_name, :last_name)
    end

    def count
      collection.count
    end

    def process(user)
      user.update_columns(full_name: "#{user.first_name} #{user.last_name}".strip)
    end
  end
end

When the throttle block returns true, the task pauses for backoff seconds before resuming. You can also throttle on Sidekiq queue latency, Redis memory, CPU load from a monitoring API — anything measurable from Ruby.

Cursor-Based Resumability

If a maintenance task crashes or is deliberately paused, it resumes from the last checkpoint. The checkpoint is stored per-batch in the maintenance_tasks_task_runs table. On resume, maintenance_tasks reconstructs the collection query starting from the last processed cursor position.

For ActiveRecord-backed tasks, the cursor is the primary key of the last processed record. For CSV or enumerable tasks, you pass the cursor type explicitly:

module MaintenanceTasks
  class ReprocessInvoicesTask < MaintenanceTasks::Task
    # Process invoices from a CSV export — cursor is the row number
    csv_collection(has_header: true)

    def process(row)
      invoice = Invoice.find_by(external_id: row["external_id"])
      invoice&.reprocess!
    end
  end
end

Running an eight-hour backfill overnight and resuming at 7am when traffic picks up is a normal maintenance_tasks workflow. The task logs show exactly how many records were processed and at what rate, so you can estimate completion time.

The Batched Update Pattern: When You Don’t Need a Gem

For one-off backfills too small to warrant maintenance_tasks and too risky to run in a schema migration, the plain in_batches pattern is enough:

# Run this from rails console, a Rake task, or a one-off job
Order.where(tax_rate: nil).in_batches(of: 500) do |batch|
  batch.update_all(tax_rate: 0.21)

  # Optional: throttle between batches
  sleep 0.1
end

For larger tables where you want a progress indicator:

total  = Order.where(tax_rate: nil).count
done   = 0
start  = Time.now

Order.where(tax_rate: nil).in_batches(of: 500) do |batch|
  batch.update_all(tax_rate: 0.21)
  done += batch.count

  elapsed = Time.now - start
  rate    = done / elapsed
  eta     = (total - done) / rate

  Rails.logger.info "Backfill: #{done}/#{total} (#{(done * 100.0 / total).round(1)}%) — ETA: #{eta.round}s"
  sleep 0.05
end

in_batches uses keyset pagination under the hood — it generates WHERE id > last_id LIMIT batch_size queries rather than LIMIT ... OFFSET ... queries. This matters for large tables: OFFSET queries require scanning and discarding the preceding rows, which grows more expensive with every batch. Keyset pagination stays at roughly constant cost regardless of how deep into the table you are. This is the same idea behind Rails cursor-based pagination.

For tables without an auto-increment integer primary key, pass of: and use find_in_batches with an explicit ordering:

Event.order(:created_at, :id).find_in_batches(batch_size: 1000) do |events|
  Event.where(id: events.map(&:id)).update_all(processed: true)
end

One trap: do not call update_all inside in_batches on the column you are filtering by. If your batch scope is where(status: nil) and update_all sets status: 'active', the records move out of the scope and the batch cursor may skip rows. Always scope the batch on a stable attribute — typically the primary key or a created_at range.

When to Use Which Approach

The decision tree is short.

Use data-migrate when:

• The data change is a one-shot operation tied to a schema change (add column, rename column, split column).
• The table is under a few million rows and the migration runs in under five minutes per batch pass.
• You want the data change tracked in version control alongside schema migrations.
• You want db:migrate:with_data to handle both schema and data in CI automatically.

Use maintenance_tasks when:

• The backfill will take more than fifteen minutes end-to-end.
• You need to pause, resume, or cancel mid-flight.
• You want progress tracking and a web UI for operations visibility.
• The backfill touches a table with hundreds of millions of rows.
• You want throttling tied to production load signals.
• The operation is not tied to a specific deploy — it is an ongoing or repeated data operation.

Use plain in_batches when:

• The backfill is a one-off investigation or correction that will never need to run again.
• The table is small enough that it completes in under a minute.
• You want to run it from the Rails console interactively and watch progress.

In practice, most teams use data-migrate for the ninety-five percent case and maintenance_tasks for the large recurring operations. A plain console in_batches occasionally handles hotfixes at 3am.

Testing Data Migrations

Data migrations need tests. A migration that corrupts data in a direction you did not anticipate is significantly worse than a migration that raises and rolls back.

For data-migrate, test the migration class directly in RSpec or Minitest:

# spec/db/data/20260915100000_backfill_order_status_spec.rb
require "rails_helper"

describe BackfillOrderStatus do
  let(:migration) { described_class.new }

  describe "#up" do
    it "marks shipped orders with a shipped_at date" do
      order = create(:order, status: nil, shipped_at: 2.days.ago)
      migration.up
      expect(order.reload.status).to eq("shipped")
    end

    it "marks unshipped orders as pending" do
      order = create(:order, status: nil, shipped_at: nil)
      migration.up
      expect(order.reload.status).to eq("pending")
    end

    it "does not overwrite an already-set status" do
      order = create(:order, status: "cancelled", shipped_at: 2.days.ago)
      migration.up
      expect(order.reload.status).to eq("cancelled")
    end
  end
end

For maintenance_tasks, the task class is plain Ruby and testable in isolation:

# spec/tasks/maintenance_tasks/backfill_user_full_name_task_spec.rb
require "rails_helper"

describe MaintenanceTasks::BackfillUserFullNameTask do
  describe "#process" do
    it "concatenates first_name and last_name into full_name" do
      user = create(:user, first_name: "Alice", last_name: "Jong", full_name: nil)
      task = described_class.new

      task.process(user)

      expect(user.reload.full_name).to eq("Alice Jong")
    end

    it "strips extra whitespace when last_name is blank" do
      user = create(:user, first_name: "Cher", last_name: "", full_name: nil)
      task.process(user)
      expect(user.reload.full_name).to eq("Cher")
    end
  end
end

Test the edge cases: nil values, blank strings, records that should be skipped, records where the transformation is idempotent. The actual batching and throttling logic is in the gem itself and does not need to be tested in your test suite — test the process method in isolation and trust the framework.

If the migration touches a table covered by strong_migrations, check that the data migration does not trigger any of strong_migrations’s unsafe-operation warnings. A common mismatch: strong_migrations catches column removals in schema migrations, but a data migration that calls destroy_all on a large table can hold a long-running table lock that strong_migrations has no visibility into. Verify manually.

Frequently Asked Questions

What is the difference between a data migration and a schema migration in Rails?

A schema migration changes the database structure — columns, tables, indexes, constraints. A data migration changes the content of existing rows — populating columns, transforming values, moving records. Schema migrations in Rails are wrapped in a transaction and apply DDL; data migrations apply DML and should not run inside a long transaction on large tables to avoid locking rows for extended periods.

Can I run data-migrate data migrations in Rails CI automatically?

Yes. Replace rails db:migrate with rails db:migrate:with_data in your CI pipeline. This runs schema migrations first, then data migrations, and records both in their respective version tables. For rails db:schema:load (used in test environment setup), run rails data:migrate immediately after to apply any pending data migrations.

How does maintenance_tasks handle a task crash or server restart?

maintenance_tasks persists a cursor to the database after each batch. If the process crashes or is restarted, the task run record shows status interrupted. You resume it from the web UI or via MaintenanceTasks::Task.named("BackfillUserFullNameTask").resume!. The task picks up from the last persisted cursor and continues from where it stopped. No rows are reprocessed and no rows are skipped.

Should I wrap a data migration in a transaction?

No, not for large tables. A transaction on a 5-million-row update_all holds row-level locks on all five million rows for the full update duration — which blocks concurrent writes on those rows for as long as the update runs. Let each in_batches batch commit independently. Each batch completes and releases its locks before the next batch begins. The tradeoff is that if the migration fails halfway through, some rows are migrated and some are not — which is fine, because a resumable migration picks up from the last successful batch.

How do I handle dependent associations in a data migration?

Use find_each or in_batches rather than includes + iteration when touching multiple models. Eager loading with includes can load enormous result sets into memory on large tables. For multi-model backfills, process in batches keyed by the primary table and query dependent records inside each batch:

Order.in_batches(of: 500) do |batch|
  orders = batch.includes(:line_items).to_a
  orders.each do |order|
    total = order.line_items.sum(&:amount)
    order.update_columns(cached_total: total)
  end
end

includes here loads line items for five hundred orders in two queries — not N+1 — and the batch commits before moving to the next five hundred.

Dealing with a backfill that has been blocked on “we can’t afford the downtime”? Or trying to untangle a schema migration that grew into a data migration over the years? TTB Software does this work for teams who need it done right. Nineteen years of Rails production experience, including a few twelve-minute outages we helped clean up.