The migration script had failed, and the production database was locked. All it needed was one new column—simple in theory, dangerous in practice.
Adding a new column to a live database is more than a schema change. It affects queries, indexes, and application logic. The wrong approach can lock tables, block writes, or cause downtime. The right approach combines careful planning, tested execution, and an understanding of the database engine’s behavior under load.
Start with a clear definition of the new column. Specify the data type, constraints, default values, and whether it can be nullable. Avoid defaults that force the database to rewrite every existing row. In many systems, adding a nullable column is instant. Adding a non-nullable column with a default can cause a full table rewrite, creating contention.
Check the ORM or migrations framework you use. Some tools generate ALTER TABLE commands that block operations by default. Consider online schema change tools like pt-online-schema-change for MySQL, pg_online_schema_change for Postgres, or native features in modern engines. These can create the new column without locking the entire table. Always validate execution plans before running in production.