Adding a new column is one of the most common schema changes in any database. Done right, it’s fast, safe, and sets up future work. Done wrong, it can lock tables, slow queries, or stop deployments cold. The key is choosing the right method for your system’s size, load, and uptime requirements.
First, define exactly what the new column will store. Set the data type, nullability, and default value based on real usage, not guesses. This matters because a full-table rewrite will impact performance. On large datasets, avoid defaults that trigger a backfill unless necessary.
Second, pick the migration strategy. For small tables, a direct ALTER TABLE ADD COLUMN works. For large or high-traffic tables, consider an online migration tool or a phased rollout. Phase one: add the new column as nullable. Phase two: backfill in batches. Phase three: enforce constraints. This minimizes lock contention and reduces risk during peak usage.