The migration failed after 300,000 rows. The cause was simple: the new column was missing an index.
Adding a new column sounds trivial, but it can bring risk to production systems if done without care. In modern relational databases like PostgreSQL or MySQL, a new column changes the table’s schema. That change can lock writes, impact replication, or break downstream apps if defaults and constraints are wrong.
To add a new column safely, start by defining its type and nullability. If the data model allows, make it nullable at first. This avoids heavy table rewrites. Next, backfill data in controlled batches. For high-traffic systems, use migration tools that run without full-table locks.
When the column needs an index, create it in a separate step. In PostgreSQL, use CREATE INDEX CONCURRENTLY to prevent blocking writes. In MySQL 8+, use ALGORITHM=INPLACE where available. Avoid adding indexes inline with the ALTER TABLE that adds the column.
For columns with defaults, consider whether the default is static or derived. Static defaults are set instantly in metadata for many databases. Derived defaults require triggers or computed columns, which may alter performance. Always benchmark before deploying changes to production.