Adding a new column sounds simple. In reality, it can break queries, slow migrations, and crush performance if handled wrong. The risk grows with table size, traffic load, and replication lag. Done right, it’s a clean schema evolution. Done wrong, it’s downtime in production.
Start by defining the new column in your schema code before touching production. Use your migration tool—whether it’s Flyway, Liquibase, Rails migrations, or plain SQL—to create the column with the correct type, null constraints, and default values. Avoid defaults that write to every row during creation; use nullable columns first, then backfill in controlled batches.
On high-load databases, use ADD COLUMN operations that are metadata-only when possible. PostgreSQL and MySQL both support this for certain types that don’t rewrite the whole table. If your column needs a default or a constraint, apply those in separate, lightweight steps.
Always backfill with an idempotent script. Batch updates with small LIMIT runs and periodic commits to avoid long locks. Monitor replication lag closely; in multi-region setups, you may need feature flags to hide the column until all replicas are ready.