Adding a new column is one of the most common schema changes in modern applications. It sounds simple, but done wrong it can stall deployments, lock tables, or cause downtime. The right approach depends on the database engine, the size of the table, and the operational constraints of your system.
In PostgreSQL, ALTER TABLE ADD COLUMN is straightforward for small or medium datasets. It’s usually instant if you provide a default of NULL and don’t backfill immediately. Avoid setting non-null defaults in the same command on large tables; it forces a full rewrite. Add the column, deploy the code that starts writing to it, and fill the data in a background process.
MySQL and MariaDB can handle ALTER TABLE ADD COLUMN online in some configurations with InnoDB, but large tables may still cause a lock. Use ALGORITHM=INPLACE or ALGORITHM=INSTANT when available to skip rebuilding. If you need to backfill, pace it in batches to keep your primary workload responsive.
In distributed systems like CockroachDB, adding a new column is typically online, but watch for the extra storage layer operations. When working with ORMs, generate migrations carefully; some tools default to blocking operations. Always verify the DDL before running in production.