One column, a week of work: migrating a huge table in Django

Created: May 2018 Updated: Sept. 29, 2026

from work

Python, Django, PostgreSQL

A naively simple migration on a table whose rows nobody counted any more ended up as a week of work and four migrations instead of one.

In a table of events we had to fix a small thing in the data and add one field while we were at it. A naively simple migration, makemigrations, migrate, done. Except the table had, as a tribe that only counts to three would say, wenga wenga rows, which just means a lot. Locally the migration went through in a second, and in production it went on and on, the transaction hung, locked the table, and eventually failed and everything rolled back.

What's going on

Django generates a single migration that adds the column straight away with a default value and as NOT NULL, and the data fix, added as RunPython, runs in the same transaction. In PostgreSQL adding a column with a default means rewriting the whole table, and meanwhile nobody reads or writes it. If something fails after an hour, rolling back takes as long again.

Why not just wait it out

We could have switched the system off overnight, except there was no guarantee one night would be enough, and a rollback halfway through means a second night. We could have built a table alongside, copied the data and swapped, but at this size that's also a long copy plus keeping track of changes coming in meanwhile. That left splitting it into pieces, none of which holds a lock for long.

Four migrations instead of one

The first adds the column as null=True, with no default, which in PostgreSQL is just a change to the table definition and takes a moment. The second fixes and fills in the data in batches, each batch in its own transaction, so locks are short, and if something fails you just run the migration again and it picks up where it stopped. The third sets NOT NULL once every row has a value, which is only a scan of the table, without a rewrite. The fourth creates the index with CONCURRENTLY, so without blocking writes, via RunSQL in a non-transactional migration.

Most depends on the second one, without a transaction and in batches:

python

from django.db import migrations

BATCH = 10000


def backfill(apps, schema_editor):
    Record = apps.get_model("data", "Record")
    last_id = 0
    while True:
        ids = list(
            Record.objects.filter(id__gt=last_id, status__isnull=True)
            .order_by("id")
            .values_list("id", flat=True)[:BATCH]
        )
        if not ids:
            break
        Record.objects.filter(id__in=ids).update(status="new")
        last_id = ids[-1]


class Migration(migrations.Migration):
    atomic = False  # each batch is a separate transaction

    dependencies = [("data", "0042_record_status_nullable")]
    operations = [migrations.RunPython(backfill, migrations.RunPython.noop)]

Without atomic = False Django would wrap the whole loop in one transaction and we'd be back where we started. Instead of a single command it took a week of work, including trial runs on a copy of the database, but none of the migrations stopped the system.

Machine-translated from Polish (original).