Adding a new column is one of the most common changes you make to a database, yet it’s also one of the easiest places to introduce risk. Done right, it preserves performance, avoids downtime, and keeps schema changes predictable. Done wrong, it can lock tables, block writes, or break application code.
The process starts with clear requirements. Define the column name, data type, default value, and constraints. Choose types that match how the data will be used. Avoid arbitrary varchar sizes. For numeric and timestamp fields, be precise.
Plan the migration. On large tables, adding a new column can be expensive. In PostgreSQL, ADD COLUMN typically rewrites metadata only, unless you set a non-null default, in which case it rewrites every row. MySQL can behave differently depending on the storage engine and version; older releases might copy the entire table, newer ones often do metadata-only changes. Test this in a staging environment with production-sized data.
Handle nullable vs non-null. Adding a non-null column to a populated table requires either a default value or a backfill procedure. Backfilling should be incremental, in batches, to avoid overwhelming IO and replication lag.