Adding a new column is one of the most common schema changes in relational databases. Yet it can break production systems if done without planning. The operation affects storage, query performance, indexes, and application code. Before pushing it live, you need to understand how your database engine handles schema evolution.
In PostgreSQL, ALTER TABLE ADD COLUMN is straightforward. By default, the column is added with null values unless a default is specified. Adding a default with a constant value can trigger a rewrite of the table, locking it during the operation. On large tables in a live system, this can cause downtime. Using ADD COLUMN ... DEFAULT ... with NOT NULL should be handled with care. For zero-downtime migrations, create the column nullable, backfill data in batches, then set defaults and constraints.
MySQL handles ALTER TABLE ADD COLUMN differently depending on storage engine and version. Older versions can rebuild the table even for a simple add. Newer versions support instant DDL for certain column types, avoiding table copies. Check ALGORITHM=INSTANT availability before assuming it will be fast.
Indexes on a new column can create locks and slow writes. Often it is safer to add the column first, populate data, then add the index in a separate step. This allows monitoring of load and rollback if needed.