Adding a new column in a database seems simple. It is not. A poorly executed DDL change can lock tables, cause downtime, or break integrations. Choosing the right method is the difference between a smooth deploy and a night spent chasing failed queries.
The process starts with schema design. Name the column with intent. Make its type match the expected data. Decide if it should allow NULL values or have a default. Every choice carries performance and storage implications.
In relational databases like PostgreSQL, ALTER TABLE ADD COLUMN is the core command. Used alone, it is fast for nullable columns without defaults. But adding a NOT NULL column with a default rewrites the table, which can block writes for large datasets. To avoid blocking, first add the column nullable, then backfill in batches, and only then add constraints.
In MySQL, adding a new column can also lock the table depending on storage engine and version. Modern releases with InnoDB and ALGORITHM=INPLACE or INSTANT can perform the operation with minimal impact, but column order changes still trigger a full rebuild. Understand the execution plan before running a migration in production.