Adding a new column is not the hard part. Doing it safely, at scale, without breaking production, is. Schema changes are among the most dangerous operations in any database. A careless ALTER TABLE can lock writes, spike load, or trigger a cascade of failures.
Start with intent. Specify exactly what the new column must store, its type, constraints, and defaults. Avoid unnecessary NULLs if you can choose a deterministic default. Consider the future: indexes, foreign keys, or calculated values.
In PostgreSQL, a simple ALTER TABLE … ADD COLUMN with a nullable field is usually instant. But adding a column with a non-null default will rewrite the entire table. On large datasets, that can block queries for minutes or hours. MySQL behaves differently, but shares the same risk with full table locks depending on engine and version.
When the dataset is large, deploy in steps. First, add the column as nullable without a default. Second, backfill data in small batches. Third, set defaults and enforce constraints. Test each step in staging with production-like data and load. Roll forward where possible, but have a rollback plan.