The danger
Changing a schema on a live database is risky. Some operations lock the table, blocking reads and writes while they run. On a large table that lock can last minutes, which is an outage. And old and new application code run at the same time during a deploy, so the schema must satisfy both.
Expand and contract
The safe approach is expand and contract, done in stages so each step is backward compatible.
- Expand add the new column or table without removing anything. Old code ignores it.
- Migrate backfill data and deploy code that writes to both old and new shapes.
- Contract once everything reads from the new shape, stop writing the old one and finally drop it.
Avoid the lock
Make each change small and non blocking. Add nullable columns rather than columns with a heavy default rewrite, create indexes concurrently where supported, and backfill in batches so you never hold a long transaction over the whole table.
Key idea
Migrate live schemas with expand and contract so old and new code coexist, and keep each step small and non blocking to avoid long table locks.