Adding a new column is one of the most common changes in database development. It looks simple, but doing it right demands precision. The wrong step can lock tables, freeze queries, or break production code. The right step makes it seamless, with zero downtime.
Define the column name with clarity. Use lowercase with underscores for consistency. Avoid reserved keywords. Choose the correct data type on first pass—changing it later can mean painful migrations.
If the database is relational, run the ALTER TABLE statement in a transaction when supported. In PostgreSQL and MySQL, most new column additions are fast if they have a default value that can be applied without rewriting all rows. Avoid adding NOT NULL with a default on giant tables without testing; it can force a full rewrite.
For systems with high traffic, deploy with a two-step migration. First, add the nullable column. Second, backfill data in batches. Finally, enforce constraints once the data is ready. This approach avoids race conditions and locks.