Adding a new column is one of the most common schema changes in databases, yet it’s also one of the most dangerous if done without care. The wrong approach can lock writes, stall queries, or cause downtime. The right approach keeps production stable and performance sharp.
First, decide if the new column should be nullable, have a default value, or be computed. In high-traffic systems, setting a default on a large table can trigger a full table rewrite. On MySQL and PostgreSQL, this can mean minutes or hours of blocking, depending on size. In those cases, add the column as nullable, then backfill in small batches before adding constraints.
For PostgreSQL, ALTER TABLE ... ADD COLUMN is fast if the column is nullable without a default. Adding DEFAULT with a non-null value will rewrite the table. Avoid triggering that unless the rewrite is acceptable. Use UPDATE ... WHERE ... LIMIT in transactions or background jobs to populate the new column. Then add the NOT NULL constraint in a separate migration.
For MySQL, online DDL support depends on the storage engine and version. In InnoDB with modern versions, many column additions can be instant. Always check ALGORITHM=INSTANT or ALGORITHM=INPLACE support for your operation. If not supported, apply a rolling migration or use a tool like pt-online-schema-change.