A new column in SQL starts at the migration. In Postgres, ALTER TABLE ADD COLUMN is straightforward. In MySQL, the syntax is similar, but both engines can trigger table locks depending on size and engine configuration. On large datasets, an online schema change is critical to avoid blocking writes. Tools like pg_online_schema_change or gh-ost handle this at scale.
When adding a new column, define constraints early. Decide if it should be nullable or have a default value. Adding a non-nullable column without a default will fail if existing rows don’t match the constraint. A default value needs careful thought: setting it on giant tables can backfill data at migration time, causing long locks and spikes in CPU and I/O. A safer path is to add the column as nullable, backfill in small batches, then apply the NOT NULL constraint.
Indexing a new column immediately can saturate resources. Delay indexing until after the data is in place. For computed or derived values, consider a generated column if supported. This reduces application logic and keeps values consistent. Monitor migration performance in a staging environment with realistic data volume before running in production.