Writing Reversible RunPython Data Migrations

The data migration looked harmless: loop over every order, derive region from the shipping country, save. On a developer laptop it took two seconds. In production it ran for forty minutes inside a single transaction, held row locks on millions of orders, bloated the table with dead tuples that autovacuum could not clean until it committed, and lagged every replica. When someone tried to roll the release back, migrate refused with IrreversibleError: Operation <RunPython ...> in orders.0044 is not reversible. RunPython is the right tool for data changes that need application logic, but its defaults are tuned for small tables and forward-only history. This guide covers the four things that make a data migration production-safe: historical models, batching, per-batch commits, and a real reverse function. It is part of Django Migrations Without Downtime.

Symptom / Error Signatures

These signs point to a RunPython migration that needs restructuring:

• migrate runs for minutes with one long transaction visible in pg_stat_activity (xact_start far in the past, state active or idle in transaction between queries).
• Replica lag climbs for the duration, then drops all at once when the transaction commits.
• pg_stat_user_tables.n_dead_tup for the table rises sharply and autovacuum cannot reclaim it until the migration ends.
• Rolling back fails: django.db.migrations.exceptions.IrreversibleError: Operation <RunPython <function forwards>> in orders.0044_backfill_region is not reversible.
• Or the migration fails months later on a fresh database with AttributeError or FieldError, because it imported the current model, which has since changed.

Root Cause Analysis

Four separate defaults cause these symptoms. First, on PostgreSQL every migration is atomic unless the class sets atomic = False, so a RunPython over the whole table becomes one enormous transaction; every updated row stays locked, every old row version stays dead but unreclaimable, and replicas receive the whole change at commit. Second, looping with save() issues one UPDATE per row with every field, which is slow and fires signals. Third, RunPython(forwards) without reverse_code is irreversible by definition. Fourth, importing models directly (from orders.models import Order) binds the migration to today’s model; the correct approach is apps.get_model("orders", "Order"), which returns the historical model as of that migration.

Keyset pagination matters more than it looks: OFFSET 1000000 LIMIT 2000 makes PostgreSQL read and discard a million rows per batch, so later batches get slower and slower. Walking the primary key keeps every batch equally cheap, as explained in cursor-based vs keyset pagination for large backfills.

Immediate Mitigation

If a long-running data migration is hurting production now:

1. Decide whether to let it finish or cancel it. Cancelling rolls back all of its work (it is one transaction), so if it is 90% done, waiting may be cheaper. If replica lag or lock waits are causing user-facing errors, cancel it.

-- PostgreSQL · requires pg_signal_backend · WARNING: rolls back all rows updated so far
SELECT pid, now() - xact_start AS xact_age, left(query, 80) FROM pg_stat_activity
WHERE application_name LIKE '%manage.py%' OR query ILIKE 'UPDATE "orders_order"%';
SELECT pg_cancel_backend(<pid>);

2. Rewrite the migration before rerunning it. Replace it with the batched, non-atomic version below. Because the cancelled attempt rolled back and Django did not record it, the rewritten migration simply runs next time.

3. Watch the rerun batch by batch. With per-batch commits, progress is visible and interruptible: count remaining rows, watch replica lag, and stop the job between batches if lag exceeds your budget — the finished batches stay committed and the idempotent filter resumes where it stopped.

-- PostgreSQL · read-only · run periodically while the migration executes
SELECT count(*) AS remaining FROM orders_order WHERE region IS NULL;
SELECT client_addr, replay_lag FROM pg_stat_replication;

Permanent Fix / Long-Term Pattern

Write every data migration that touches more than a few thousand rows in this shape: historical model, keyset batches, set-based updates, a commit per batch, a pause between batches, and a reverse function.

# Python · orders/migrations/0044_backfill_region.py · PostgreSQL or MySQL
# WARNING: atomic = False — batches commit individually; the function must be safe to rerun.
import time
from django.db import migrations, transaction

BATCH = 2000
COUNTRY_TO_REGION = {"DE": "eu", "FR": "eu", "US": "na", "CA": "na", "JP": "apac"}

def forwards(apps, schema_editor):
    Order = apps.get_model("orders", "Order")
    last_id = 0
    while True:
        ids = list(Order.objects.filter(id__gt=last_id, region__isnull=True)
                   .order_by("id").values_list("id", flat=True)[:BATCH])
        if not ids:
            break
        with transaction.atomic():
            for country, region in COUNTRY_TO_REGION.items():
                Order.objects.filter(id__in=ids, ship_country=country).update(region=region)
        last_id = ids[-1]
        time.sleep(0.05)   # yield to OLTP traffic and replicas

def backwards(apps, schema_editor):
    Order = apps.get_model("orders", "Order")
    last_id = 0
    while True:
        ids = list(Order.objects.filter(id__gt=last_id).order_by("id").values_list("id", flat=True)[:BATCH])
        if not ids:
            break
        with transaction.atomic():
            Order.objects.filter(id__in=ids).update(region=None)
        last_id = ids[-1]

class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0043_order_region")]
    operations = [migrations.RunPython(forwards, backwards)]
# ROLLBACK PATH: migrate orders 0043 runs backwards(), resetting region in batches.

The region__isnull=True filter makes the forward function idempotent: rerunning after a partial failure skips rows already done, the property described in making data backfills idempotent with upserts. For very large tables, consider moving the backfill out of the migration entirely into a background job that the migration only enqueues, so a slow backfill never blocks a deploy — the pattern in Backfill Optimization. Tune BATCH and the sleep against replica lag rather than guessing.

Verification Checklist

• The migration uses apps.get_model() and never imports models from the application.
• atomic = False is set, and each batch runs in its own transaction.atomic() block.
• Batches use keyset pagination on the primary key, not OFFSET.
• A reverse_code function exists and was tested by migrating forward and back on staging.
• Replica lag and n_dead_tup stayed within normal bounds during a staging run on production-sized data.

Frequently Asked Questions

Why use apps.get_model() instead of importing the model? Migrations must work against the schema as it was at that point in history. apps.get_model() returns a historical model built from the migration state, while a direct import returns today’s model, which may have fields that do not exist yet or lack fields that do.

Is atomic = False safe for a data migration? It is safe if the migration is idempotent, because a failure leaves earlier batches committed. Filtering on rows that still need changing, as in the example, makes a rerun pick up where the failed attempt stopped.

When is RunPython.noop an acceptable reverse? When reversing genuinely requires nothing — for example, a forward migration that populates a new column, where rolling back the schema migration before it will drop that column anyway. Do not use it just to silence IrreversibleError.

Should very large backfills run inside migrations at all? Often not. A migration that runs for hours blocks the deploy pipeline. For tables with tens of millions of rows, have the migration add the column and let a background job perform the backfill under throttling, then tighten constraints in a later release.