During a rolling deploy, old and new application code run simultaneously against the same database. Any schema change that only works with the new code will break the old one for the duration, and any change that locks a large table will block everything. Both are avoidable, and the technique is mechanical once you have seen it.
01Expand, migrate, contract
Additive changes first: add the new column, deploy code that writes to both old and new, backfill existing rows in batches, deploy code that reads from the new column, and only then remove the old one. Each step is independently deployable and independently reversible.
It feels like a lot of ceremony for a rename, and it is the difference between a change that ships on a Tuesday afternoon and one that requires a maintenance window. Once the team has done it twice it stops feeling like overhead.
| Step | Schema | Application code |
|---|---|---|
| 1 | Add new nullable column | Unchanged |
| 2 | Unchanged | Write to both, read from old |
| 3 | Backfill in batches | Unchanged |
| 4 | Unchanged | Read from new, still write both |
| 5 | Unchanged | Write to new only |
| 6 | Drop old column | Unchanged |
02Know which operations take locks
Adding a nullable column without a default is instant in modern Postgres. Adding a NOT NULL column with a default used to rewrite the entire table and now generally does not. Adding an index without CONCURRENTLY blocks writes for the duration of the build, which on a large table means an outage.
The dangerous part is that these all look like similar one-line statements. Know which ones lock, and always add indexes concurrently in production even though it is slower and cannot run inside a transaction.
03Backfill in batches, with a brake
A single UPDATE across ten million rows holds locks, generates enormous write-ahead log volume and can stall replication. Batch it - a few thousand rows at a time, with a pause between batches - and the same work becomes invisible to production traffic.
Make the job resumable, because it will be interrupted. Track progress by primary key, and monitor replication lag while it runs with an automatic pause if lag exceeds a threshold. A backfill that outruns your replicas turns a maintenance task into a read-replica outage.
| Operation | Lock | Safe approach |
|---|---|---|
| Add nullable column | Brief | Safe directly |
| Add column with default | Brief on modern Postgres | Check your version |
| Add NOT NULL constraint | Full table scan | Add as NOT VALID, then validate |
| Create index | Blocks writes | CONCURRENTLY |
| Drop column | Brief | Safe, but only after code stops using it |
| Change column type | Table rewrite | Expand-contract with a new column |
04Rollback has to work at every step
Each deploy in the sequence must be safe to reverse without the previous one having been undone. That is why the dual-write phase exists: if you roll back the read change, the old column is still current because you never stopped writing to it.
The step that cannot be rolled back is dropping the old column, which is why it comes last and only after the new path has run in production for long enough to be trusted. Some teams wait a full release cycle before contracting, which is a reasonable amount of caution for an irreversible change.
05Making it routine
Teams that do this well have a migration checklist in the pull request template, a policy that migrations and code changes ship separately, and a staging environment with enough data volume that lock behaviour is visible before production.
The alternative - migrations bundled with the code that needs them, tested against a database with a hundred rows - works fine until the table is large and the deploy is at a busy hour. That is a lesson most teams learn once.
Topics
Priya Iyer
Staff Engineer · SyncTrix
Writes about the engineering decisions behind production systems - architecture, delivery and the trade-offs that only show up at scale.
Building something like this?
SyncTrix engineers AI, SaaS, platform and cloud systems for enterprises and high-growth teams. Tell us what you're shipping and we'll scope it with you.
Talk to an engineer