Adding a new column is one of the most common schema changes in relational databases. It looks simple but carries real consequences for performance, indexing, and deployment strategy. When done without care, it can lock tables, block writes, or trigger expensive full-table rewrites.
A new column changes your contract with the data. First, define the column name and data type with precision. Choose NULL or NOT NULL based on actual data requirements, not assumptions. Avoid default values that force the engine to rewrite every row. For large datasets, add the column with NULL first, then backfill in controlled batches.
In MySQL and MariaDB, ALTER TABLE ... ADD COLUMN can be disruptive. Use ALGORITHM=INPLACE or ALGORITHM=INSTANT where supported to reduce locking. In PostgreSQL, adding a column with a default value once triggered a full table rewrite; newer versions optimize this, but verify your version's behavior before running the migration in production.
If indexes or constraints are needed for the new column, apply them after data backfill to prevent unnecessary overhead during the initial alteration. If the column will be queried frequently, assess whether a partial index or expression index is more cost-effective.