PostgreSQL · Rails · Production
Changing data on a live database without fear
Backfills and data migrations on a live PostgreSQL database. The checklist I follow every time.
RRRafael Race1 min read
A wrong backfill does not break one page. It changes data that real customers see. So I follow the same checklist every time I change data in production.
Before the run
- Read the live schema before you write a production query.
- Assert each required column at the start of the script.
- Read the script itself, not the ticket, to learn what it writes.
- Check the dry-run setting, every time.
- Resolve explicit IDs, print them, then act. Never use a wildcard delete.
During the run
- Back up the rows, and count them before and after any delete.
- Process long runs in small batches.
- Write a checkpoint after each batch, so a stopped run can continue.
After the run
A running process can keep its old view of the schema until it restarts. So I restart long-running processes after a manual migration.
A backfill that nobody runs changes nothing in production. Plan the run before the pull request is ready.
Decide with numbers
When a change has more than one option, I measure each option against production first. I show the impact as a table: today against each option. The person who decides can then see the cost of each choice.