Adding a new column is one of the most common schema changes in modern application development. Done right, it’s simple. Done wrong, it’s a trap that can lock indexes, block writes, and trigger downtime. The key is to plan the change, choose the correct type, and deploy with minimal risk.
Start by defining the column’s purpose. Pick a clear, descriptive name. Use the smallest data type that can hold the required values. Smaller types take less space and reduce load on queries. Avoid defaulting to TEXT or large VARCHAR lengths unless absolutely necessary.
Before altering the table, check the database engine’s behavior for ALTER TABLE operations. In MySQL, adding a column to the end of a table may still lock the table unless online DDL is enabled. In PostgreSQL, adding a column with a DEFAULT and NOT NULL can rewrite the entire table, which is dangerous for large datasets. When possible, first add the column without the NOT NULL constraint and fill it in with background jobs before enforcing constraints.
Always add indexes carefully. A CREATE INDEX on a massive dataset can block writes without CONCURRENTLY in PostgreSQL or ONLINE in MySQL. Measure the impact of any index before deploying to production.