Each batch runs in its own transaction. If the migration dies at row 2,500,000, those rows keep their backfilled values. Restarting picks up where it stopped, because the batch query filters for rows where the new column IS NULL. No work is repeated and no data is lost.
Services
Backend Engineering, Infrastructure
Industry
B2B Marketplace
Year
2023
A four-million-row schema change during peak traffic
Every schema change at this B2B marketplace followed the same ritual: schedule it for 3 AM, keep one engineer awake, hope nothing breaks. Then the product team needed a new mandatory column on the listings table. Four million rows, read on every page view, and no quiet hour left in the traffic curve.
THE CHALLENGE
The 34-second blackout
Django's AddField with null=False and a default compiles to a single ALTER TABLE. PostgreSQL takes an ACCESS EXCLUSIVE lock on the table for the whole backfill, and on four million rows the backfill takes 34 seconds. For those 34 seconds every listing page, every search and every admin view queues behind the lock until it times out with a 504. The connection pool fills, the load balancer starts answering 502, monitoring fires, and the migration is not even halfway done. A 3 AM window would only have moved that outage, not removed it. The column had to land during peak traffic without anyone noticing.
THE SOLUTION
Four migrations instead of one
The single dangerous migration became four operations, none of which holds a table lock for more than ten milliseconds. AddField with null=True adds the column without rewriting a row. A data migration backfills the values in batches of 5,000 rows with a 100 ms pause between batches. AlterField then sets null=False as a constraint-only change, and a final RunSQL sets the database default. Online schema tools like gh-ost or pt-online-schema-change were the obvious alternative: they copy the table into a shadow copy and swap, which solves the lock but adds permissions, monitoring and rollback procedures to a PostgreSQL deployment that had none of them, and takes the change out of Django's migration history. Four plain migration files kept the history intact. The price is a longer wall time and one migration that has to tell the ORM what the database already knows.
The batched backfill, as a data migration:
Python
def forwards(apps, schema_editor):
Listing = apps.get_model('listings', 'Listing')
batch_size = 5_000
total = Listing.objects.filter(category_v2__isnull=True).count()
for start in range(0, total, batch_size):
batch = list(
Listing.objects
.filter(category_v2__isnull=True)
.order_by('pk')
.values_list('pk', flat=True)[:batch_size]
)
Listing.objects.filter(pk__in=batch).update(
category_v2=F('category') # copy from old column
)
time.sleep(0.1) # yield I/O to production queriesBatch size against lock time
Both lanes migrate the same four million rows. Switch the batch size and watch the lower lane: the row-lock length, the requests that queue behind it and, at 500,000 rows, the connection pool warnings. At 5,000 the locks are too short to draw.
Batch sizerows per batch
Ready
One migration
AddField(null=False, default=...) Table locked · 34 s
0 s34 s
Four migrations
AddField · backfill · AlterField · RunSQLAddField(null=True)< 10 msBackfill in batchesRow lock < 50 msAlterField(null=False)< 10 msRunSQL SET DEFAULT< 10 ms
0 sBatch 800 of 800: 4,000,000 of 4,000,000 rows backfilled42 s
800 row locks of under 50 ms each: too short to draw at this scale.
| Single ALTER TABLE | Four steps | |
|---|---|---|
| Downtime | 34 s | 0 s |
| Blocked requests | 400 | 0 |
| Longest lock held | 34 s | < 50 ms |
| Batches | 1 statement | 800 |
400 requests blocked behind a 34-second table lock. Four steps at 5,000 rows per batch: 0 blocked, longest lock < 50 ms.
Django tracks the database schema and its own model state separately. Step three sets the constraint in SQL and tells the ORM about it in the same migration, so the next migrate run does not try to add a column that already exists:
Python
class Migration(migrations.Migration):
operations = [
# Step 3: add NOT NULL constraint only
migrations.SeparateDatabaseAndState(
state_operations=[
migrations.AlterField(
model_name='listing',
name='category_v2',
field=models.CharField(max_length=64),
),
],
database_operations=[
migrations.RunSQL(
sql='ALTER TABLE listings_listing '
'ALTER COLUMN category_v2 '
'SET NOT NULL;',
reverse_sql='ALTER TABLE listings_listing '
'ALTER COLUMN category_v2 '
'DROP NOT NULL;',
),
],
),
]THE RESULT
Migrations moved to business hours
The backfill ran during peak traffic and request latency stayed inside its normal p99 band the whole time. No pool warnings, no 502s, no alerts. The product team learned that the migration had run when they read the changelog. Since then the same four-step decomposition has carried eleven further schema changes on tables between 500,000 and twelve million rows, all during business hours. The 3 AM window has not been booked again.
KEY METRICS
4MRows backfilled in one run
0sDowntime during the run
11Further schema changes, same pattern
CLIENT FEEDBACK
We used to schedule migrations for 3 AM and keep someone awake to watch the dashboards. Since this one they go out in the normal deploy during business hours. Nobody outside engineering has noticed a single one.
Lead Developer
B2B marketplace, engineering
FOR YOUR PROJECT
- When it applies
- Any PostgreSQL table where a lock of more than a few hundred milliseconds is visible to users: new NOT NULL columns, defaults, constraints. The same four steps carried eleven further changes here, on tables from 500,000 to twelve million rows.
- What to check
- Restore a staging database from a production snapshot and watch pg_stat_activity while the migration runs. Anything that holds ACCESS EXCLUSIVE for more than 50 ms gets redesigned. Index rebuilds and CHECK constraints are the usual surprises; both scan the whole table.
- What it needs
- Four migration files instead of one, a staging replica with production-shaped data, and a longer wall time per run. No new tooling, no extra permissions, no maintenance window.
FAQ
TECHNOLOGY STACK
Django
Python
PostgreSQL
Manuel Kasbarian - CEO, SophistiXWe have enjoyed working with Daniel for 10 years now. We highly appreciate his fast response times around the clock and his all-round knowledge. Whether server configurations or programming, he always has the right solution.
Follow in the footsteps of Manuel and bring your vision to life.
Open to new ProjectsGet In Touch