SQL Migration Strategies: Evolving Schemas Without Downtime
Schema changes are inevitable. The challenge is applying them without locking the database or breaking the application. Here is the process I follow. Schema migrations are how a database evolves. Adding a column, changing a type, creating an index, renaming a table, each is a migration. The challenge is not writing the SQL. The challenge is applying the change without locking the database for so long that the application becomes unavailable, and without breaking the application by changing the schema underneath it. After performing hundreds of migrations on production databases, I follow a process that minimizes risk. The Problem with Migrations Many schema changes take locks. Adding a column with a default value, creating an index, or altering a type can lock the table for the duration of the operation. On a small table, this is invisible. On a table with millions of rows, a lock that lasts minutes causes application timeouts and cascading failures. The second problem is compatibility. If a migration removes a column that the application still reads, the application breaks. If a migration changes a column type that the application expects, queries fail. The migration and the application must…