Adding a new column to a database is one of the most common schema changes. Done right, it’s fast, safe, and invisible to users. Done wrong, it can lock tables, block writes, and grind production to a halt. The process is straightforward in principle but loaded with traps in practice.
Understand the current schema before you touch it. Inspect indexes, constraints, and triggers. Check row counts and query patterns. Every new column changes the shape of your data and the performance profile of your queries.
In relational databases like PostgreSQL and MySQL, adding a new column with a default value can cause a full table rewrite. On large datasets, that can take minutes or hours, holding locks the entire time. To avoid downtime, add the column without a default, then backfill values in small batches. Only after the backfill should you enforce defaults or constraints.
Use database migrations under version control. Track every schema change. Review and test migrations in staging with production-like data. Your migration scripts must be idempotent and reversible. If your ORM generates the migration, read it carefully. Never run raw ALTER TABLE on production without a rollback plan.