Adding a new column should be simple. In practice, the wrong approach can stall deployments, break queries, or trigger downtime. The key is choosing a migration method that handles both data integrity and performance at scale.
Most teams face three scenarios: adding a nullable column, adding a column with a default, or adding a required non-null column. Nullable columns are usually safe, even on large tables, because they avoid full table rewrites. Adding a column with a constant default can be more dangerous; on some databases like MySQL prior to 8.0, this can lock the entire table. Postgres handles it better by storing defaults in metadata until a non-default value is written.
For required non-null columns, a two-step migration is safest. First, add the column as nullable. Backfill in small batches to avoid locking and I/O spikes. Then, apply the NOT NULL constraint when all rows are populated. This approach keeps write and read operations responsive during deployment.