Adding a new column to a database table is one of the most common schema changes. It can also be one of the most disruptive. The wrong approach locks tables, blocks writes, and slows queries under load. The right approach keeps your application online without missing a beat.
Start by defining the type, constraints, and default value for the new column. If you add a non-null column with a default in one step, many databases will rewrite the entire table. On large tables, this can mean hours of downtime. Instead, add the column as nullable without a default. Then backfill values in small batches, using indexed lookups to avoid full scans. Once complete, apply constraints and defaults in separate, fast metadata changes.
In PostgreSQL, ALTER TABLE ... ADD COLUMN is metadata-only for nullable columns without defaults. In MySQL, version and engine matter—some can handle instant column adds; others will copy the entire table. With distributed stores, check how schema changes propagate node-to-node, and stage changes to avoid cluster-wide stalls.