We want to automate our database deployments using Jenkins and Liquibase. However, whenever we run a "heavy" migration—like adding a column with a default value to a table with 10 million rows—it locks the table and causes downtime. How do modern teams handle schema changes without killing the production app?
3 answers
Zero-downtime migrations require a "Two-Step" or "Expand and Contract" pattern. You never rename or delete a column in one go. First, you add the new column (Expand). Then, you update the application code to write to both the old and new columns. Once you've migrated the data in the background, you update the app to read from the new column. Finally, you drop the old column (Contract). For heavy operations on MySQL, tools like gh-ost or pt-online-schema-change are essential. They create a ghost table, copy data in small chunks, and then flip the tables. This prevents long-held metadata locks that take your site offline.
Are you currently using a "Blue-Green" deployment strategy for your application nodes, and how do you handle the version mismatch when the DB is at version N+1 but the app is still at N?
The key is to make every migration "Add-only." If you never delete or change existing structures, the old version of the app will never break when the new DB schema arrives.
Steven is 100% correct. Deletion should always be a separate, late-stage cleanup task once you are sure the new version of the code is stable.
Brian, that's our biggest pain point. Currently, we just hope the migration is fast. We don't have a formal way to ensure "Backward Compatibility" in our SQL scripts. I love the "Expand and Contract" idea Karen mentioned, but it seems like it would double the number of deployments we have to do for every feature. Is there a way to automate those intermediate steps in a single CI/CD pipeline, or is it always a manual multi-step process for the developers?