Adding a new column should be simple. In reality, the smallest schema change can break production, lock tables, or slow queries. The difference between success and rollback is in how you plan and execute it.
A new column changes more than just the table definition. It affects indexes, query plans, application code, and downstream systems like ETL jobs or caches. Before adding it, audit all queries that touch the table. Look for SELECT * patterns. Identify any ORM mappings that could fail when the schema changes.
When altering large tables, avoid blocking operations. Use tools or database features that allow online schema changes. In PostgreSQL, ALTER TABLE ... ADD COLUMN without a default runs fast; adding a default and NOT NULL will rewrite the table. Split these steps. For MySQL, consider pt-online-schema-change or gh-ost to keep tables writable during the operation.
If the new column needs backfilling, load the data in batches. Limit transaction size to reduce lock times and replication lag. Use monitoring to catch slow queries triggered by the altered schema.