Adding a new column to a database table seems simple, but it can break production if done without care. It changes storage, indexes, and query plans. It can lock tables, block writes, and cascade into unexpected downtime. The right approach depends on schema size, database type, and traffic patterns.
When adding a new column in PostgreSQL, consider whether it is NULLable and if it has a default. Adding a column with a default value in older versions can rewrite the entire table, causing long locks. In MySQL, adding a column with ALTER TABLE may rebuild the table, which can stall large datasets. Modern versions offer online DDL features, but they are not always safe for high-load systems.
Use explicit migration steps. First, add the new column as NULLable without a default. Then backfill data in small batches. Afterward, enforce constraints or defaults in a separate migration. This avoids long-running locks and reduces impact on query performance.
Plan for index changes as well. Adding a column with a new index can be expensive. Build indexes concurrently when supported, or use tools like pt-online-schema-change to avoid full table locks. Test migrations in a staging environment using production-like data volume to measure actual impact.