Adding a new column sounds simple, but the wrong approach can block queries, lock tables, or cause downtime. The right approach depends on your system’s scale, your database engine, and the read/write demands on your production environment.
In PostgreSQL, ALTER TABLE ADD COLUMN runs fast for empty columns with default NULL. But adding a new column with a non-null default rewrites the whole table. On a large dataset, that can take minutes or hours. The safe method is to add the column without a default, backfill data in batches, then alter again to set NOT NULL constraints once complete.
MySQL behaves differently. Adding a new column may trigger a table copy unless you use ALGORITHM=INPLACE or ALGORITHM=INSTANT in supported versions. INSTANT avoids table rebuilds entirely, but only for changes that don’t modify existing rows.
In high-load systems, schema changes must be planned. Test the migration on a clone of production data. Measure execution time. Stage the deployment to avoid peak traffic. Consider tools like pt-online-schema-change or gh-ost to add a new column online without locking writes.