The migration was live, and the database locked for writes. You had one task: add a new column without breaking production.
A new column sounds simple, but in large systems, it can trigger schema changes that lock tables, spike CPU, or block traffic. In modern SQL databases, adding a column can mean zero downtime—if you do it right. The key is understanding how your database engine handles metadata changes, defaults, and constraints.
In PostgreSQL, adding a nullable column without a default is fast because it only changes the system catalog. Add a default with NOT NULL and the database may rewrite the entire table. MySQL behaves differently: some storage engines support instant column addition, others do not. Always check your version and storage engine capabilities before running migrations.
For high-traffic systems, avoid schema locks by splitting the change into steps. First, add the new column as nullable with no default. Then backfill it in batches to avoid load spikes. Finally, apply constraints or defaults once the data is ready. This approach keeps reads and writes flowing during the operation.