The migration had to run before morning. The product team pushed a new feature, and the database schema needed a new column. No one could ship until it was done.
Adding a new column sounds simple. In production, it is not. Schema changes can lock tables, block writes, and slow queries. The wrong ALTER TABLE at the wrong time can halt an entire system.
The safe way to add a new column depends on the database engine. In PostgreSQL, adding a nullable column without a default is fast because it updates metadata only. Adding a column with a default rewrites the entire table, which can be dangerous for large datasets. MySQL behaves differently. In many versions, adding any column may copy the table to a new file, blocking concurrent writes. Online schema change tools like pt-online-schema-change or gh-ost can avoid downtime for MySQL.
For Postgres:
- Add the column as nullable without a default.
- Backfill data in small batches to avoid long transactions.
- Add constraints or defaults in a separate statement after the backfill.
For MySQL:
- Check if the server version supports instant add column for the planned change.
- If not, run an online schema change with a copy table method in controlled traffic conditions.
- Monitor replication lag and error rates during the change.
In both systems, test migrations against a production-like dataset. Measure the execution time and resource usage before touching live data. Use feature flags to switch application code to the new column only after the data has been verified.
A new column is not just a schema difference in source control. It is a change that can disrupt APIs, caches, and analytic pipelines. Coordinate across systems, and keep rollback steps defined. The fastest changes are the ones you never need to undo.
You can handle a new column without downtime, data loss, or risk—if the process is right. See how to run safe schema changes and ship a live database migration in minutes at hoop.dev.