Comparison
BackfillvsSchema migration
Backfill
you added a column and now have to populate it for eighty million existing rows without locking the table or saturating the disk.
Populating a new field or table for existing data, usually in batches with a delay between them. Doing it in one statement takes a long lock and a large amount of replication lag with it. The batch job also has to handle rows written after it started, which is why backfills are normally paired with dual writes.
Full entry →Schema migration
you have to change the shape of a table that is being read and written right now, without stopping either.
A versioned change to the database structure, applied in order and tracked alongside the code. The hard part is not the DDL but the overlap: old and new application versions run simultaneously during a deploy, so every migration must be compatible with both. A migration that takes a lock on a large table can be indistinguishable from an outage.
Full entry →