Risks and benefits
Schema migration allows for fixing mistakes and adapting the data as requirements change. They are an essential part of software evolution, especially in agile environments (see below). Applying a schema migration to a production database is always a risk. Development and test databases tend to be smaller and cleaner. The data in them is better understood or, if everything else fails, the amount of data is small enough for a human to process. Production databases are usually huge, old and full of surprises. The surprises can come from many sources: * Corrupt data that was written by old versions of the software and not cleaned properly * Implied dependencies in the data which no one knows about anymore * People directly changing the database without using the designated tools * Bugs in the schema migration tools * Mistakes in assumptions how data should be migrated For these reasons, the migration process needs a high level of discipline, thorough testing and a sound backup strategy.Migration strategies
In the steady state, one version of an application only understands one version of a schema. So the most basic strategy is to shut down the application, execute the schema migration, and then start the newer version of the application. While simple, this strategy causes a downtime. Depending on the criticality of the system and its usage patterns, downtimes of various durations may be tolerated, but in some cases none may be tolerated at all. In those cases, one of the following zero-downtime strategies may be used.Dual writing
These are the general steps of dual writing (also called double writing): # Prepare the schema so that it can hold data in both the old and new formats. This might mean adding a new version of a column or a table, without affecting existing data. # Deploy a new version of the application which writes data in both the old and new formats (hence the name dual writing). It's important to ensure consistency of these writes. After this point, all newly written data will exist in both old and new formats. # Execute a backfill in the database: copy data from the old format to the new format that existed previously, and hasn't been updated recently, so it's not dual written yet. After this point, the database has a complete replica of the data in both the old and new formats. # Deploy a new version of the application which switches to reading data in the new format, and stops dual writing. In distributed systems, it's important to switch the reading path before stopping dual writing, so this step may be divided into two. # Remove the old format data from the schema.Dual reading and writing
In this combined approach, the application is changed to both dual read and dual write. Since both individual strategies ensure that the database can remain online without interruption, the combined approach achieves the same as well. This strategy allows for more fine grained control over the backfill, which can be divided into smaller batches, and feature flags may be used to toggle both the reading and writing paths more freely and separately from each other. This can also be useful when regular dual writing alone can't be guaranteed to happen in consistent transactions.Comparison
* All strategies above achieve a zero downtime migration. * Dual writing has the advantage that the old and new version of the data live side by side, so comparisons can be run between them to ensure consistency before committing to the new format. But this comes at the cost of doubling the storage requirements. * With dual reading only one version of every piece of data exists at any point in time, so there's no increase in storage requirements. * The combined approach allows doing the backfill in smaller batches, so the storage increase can be controlled, but this comes at the cost of added complexity.Schema migration in agile software development
When developing software applications backed by a database, developers typically develop the application source code in tandem with an evolving database schema. The code typically has rigid expectations of what columns, tables and constraints are present in the database schema whenever it needs to interact with one, so only the version of database schema against which the code was developed is considered fully compatible with that version of source code. InRelation to revision control systems
Teams of software developers usually use version control systems to manage and collaborate on changes made to versions of source code. Different developers can develop on divergent, relatively older or newer branches of the same source code to make changes and additions during development. Supposing that the software under development interacts with a database, every version of the source code can be associated with at least one database schema with which it is compatible. Under goodRelation to schema evolution
Schema migration tooling could be seen as a facility to track the history of an evolving schema (i.e. schema evolution).Advantages
Developers no longer need to remove the entire test database in order to create a new test database from scratch (e.g. using schema creation scripts from DDL generation tools). Further, if generation of test data costs a lot of time, developers can avoid regenerating test data for small, non-destructive changes to the schema.References
Links