Estimating a database migration
How to estimate a database migration: backfill, lock duration, rollout and rollback. 'Add a column' is one line of SQL; the estimate is about the second clock.
“Add a column” is a one-line migration. The estimate isn’t about the column.
Schema migrations run two clocks. One is the SQL — usually fast, usually mechanical. The other is the operational clock: how long does it lock the table, what does it do under concurrent writes, how do you back it out if it goes wrong, and what’s the contingency if it runs longer than the deploy window. The team votes on the SQL because that’s what’s on the ticket. The work happens on the second clock.
On a small table, the two clocks are the same and the story is genuinely small. On any table that matters, they aren’t. The estimate has to account for the backfill, the rollout strategy, and the rollback that someone needs to have rehearsed before the deploy goes anywhere near production.
What gets said in the room
Backend: “It’s just an ALTER, the migration runs in a second.”
SRE: “On the orders table? With locks held? At 3pm?”
DBA: “How are we backfilling existing rows?”
SRE: “What’s the rollback if the deploy fails halfway?”
Lead: “Does the read path tolerate the column being null for an hour?”
Questions worth asking before voting
- How big is the table — thousands, millions, hundreds of millions of rows?
- Online migration tool, or in-place ALTER?
- Backfill strategy: synchronous, batched job, dual-write?
- What does the read path do during the rollout window?
- Rollback: forward-only, or can we revert the schema?
- Has someone walked the on-call playbook for this migration?
If half the room is voting “the SQL” and the other half is voting “the rollout,” you don’t have a number problem; you have two stories pretending to be one. Split it: the schema change is one ticket, the backfill and rollout plan is another.
Estimate the rollout and the rollback, not the ALTER. The second clock is where the risk lives.
See estimating a data migration for the cross-system variant, and the other worked estimation examples. Open a free planning poker session once the rollout is sketched.