The staging database slowed to a crawl. You knew what had to happen: a new column was the only way out.
Adding a new column is one of the most common schema changes in production databases. It looks simple. It isn’t. A poorly executed migration can lock tables, block writes, or spike latency for every user on the system. Done right, it’s seamless. Done wrong, it’s a public outage.
When planning a new column, start by defining its type, nullability, and default value. Use explicit data types, avoiding implicit conversions that can cause hidden performance costs. In PostgreSQL or MySQL, adding a nullable column without a default is typically fast, even on large tables. Adding a non-nullable column with a default can rewrite the full table — a dangerous operation in a live environment.
Break risky changes into phases. First, add the new column as nullable without a default. Then backfill it in the background using controlled batch updates. Finally, add constraints or defaults after the data is consistent. This staged rollout prevents heavy locks and keeps your system responsive.