Database Migration Patterns
Moving a database might not sound glamorous, but it’s one of the most high-stakes operations in evolving modern complex software systems at scale. Whether you’re shifting from a monolithic SQL database to a distributed NoSQL store or simply relocating from a datacenter to the cloud, the strategy and approach you choose can mean the difference between a seamless transition and a catastrophic outage.
This is not a how-to guide. Instead, this is an exploration of the different archetypal patterns and the associated trade-offs involved in database migration approaches. The discussion will stay platform, tool, and technology agnostic, not focusing on any particular flavor, favoring an overarching guide to migration approaches.
Objective
Let’s begin by outlining our goal: migrating an existing database to a new location or platform. Sometimes this simply means hosting the same engine on a different server or in another data center or cloud provider. When keeping the same database engine, the migration tends to be more straightforward, as existing tooling is usually available and only configuration updates are required, with no code changes needed. Our primary focus is on migrations that involve transitioning to a different database technology.
Data is an application’s most valuable asset, so migrations tend to be high stakes efforts. Additionally, applications that interact with the database will require updates to varying degrees to support the new technology. Migrations with these additional considerations are what we will focus on.
As an aside, one of the critical patterns of microservices is the database per service pattern (keeping each microservice’s persistent data private to that service and accessible only via its API). If you are looking for the practical consequences of not following this pattern (multiple services accessing the same database), it becomes obvious in the case of a database migration. When multiple microservices all read/write to the same database, it becomes a major pain point during a migration. A cutover that would have required updating a single application /microservice now involves updating code and coordinating deployments across multiple running services. This necessitates increased analysis, implementation, and coordination time, which directly increases the overall migration risk.
The Hard Cutover
Let’s start with the simplest approach, which centers around the concept: incurring an outage to complete the migration. If your business requirements are compatible with this approach, then look no further. The steps are: shut down the application, extract the data from the old database, load the data to the new database, and start the application with the updated code and configuration pointing to the new database.
This is the simplest option, and its export-and-import step also appears in other migration patterns acting as a building block represented in the “Initial Load” in subsequent database migration strategies, although in those approaches a hot backup is acceptable. The main point to be aware of is understanding how long the actual export and import steps will take. For a small database, the process may take minutes, but for large databases it can take hours or even days.
All subsequent approaches add complexity to specifically avoid the shortcoming of this technique, the outage. Make sure you ask yourself before utilizing any other approach, and be honest, is the effort of avoiding an outage really worth the additional time and difficulty?
Replicate The Database
Many database technologies support creating a change stream that allows for the changes to database records to be processed by another application. (One of my favorites being DynamoDB + DynamoDB Streams.) This stream or change log can then be consumed and written to another database by an application, with optional transformations performed as part of this step, to keep the databases in sync after an initial data load.
This then allows for the first phase to send read load to the new database immediately, assuming the replication delay is acceptable for the use case. The final phase is to cut over write traffic to the new database. However, once writes are sent only to the new database, the old one becomes stale. This makes rolling back more difficult or impossible depending on the use case.
Get Jared Hatfield’s stories in your inbox
Join Medium for free to get updates from this writer.
One key aspect here is ensuring the databases are fully in sync, but the goal may be to do this without incurring any type of outage. This can be accomplished by sequencing the events carefully, specifically turning on the change stream, but not consuming it, before taking and restoring a full backup of the database. The changes that occur between turning on the stream and taking the full backup can be safely re-applied to the records allowing the database to converge to the correct state. This approach works by accomplishing a zero-downtime way to synchronize the databases, even if it takes hours or even days to complete some of these steps.
While the goal here is to explore database migrations, this specific pattern has another use case: ongoing replication between multiple databases. This approach doesn’t make sense when using multiple instances of the same database engine, since engine-specific, purpose-built read replicas already accomplish this. The approach here is for when you are utilizing multiple database technologies together for their unique benefits. When using the CQRS pattern or when needing multiple database technologies to support different query capabilities, this approach can be particularly powerful. For example, having a primary database replicated to OpenSearch for flexible querying, to Valkey for high-performance reads, or to a data warehouse for large analytical queries. In these cases, replication runs through a pub/sub layer, providing an independent channel to consume the changes as they occur.
Bi-Directional Database Replication
The problem with replicating in one direction for a migration is that it makes rolling back, once writes have been cutover to the new database, difficult if not impossible. To address this limitation bi-directional replication, while more complicated, can be implemented as a means to mitigate this risk. This approach involves additional processing that introduces its own set of unique challenges, but it comes with a key benefit to the migration: both reads and writes can be sent to both the old and new database at the same time. There are still important limitations such as replication delay and race conditions that can make transactions no longer atomic when performed on the separate systems.
The main practical challenge in implementing bi-directional replication is avoiding replication loops, where a change written to one database is replicated to the other and then triggered back to the original. You need a reliable way to prevent these “echos” from reverberating between the databases, and there are a few options. These include implementing:
- Conditional writes that use timestamps. An echo might return to the original database, but it can be ignored if its timestamp is not newer than the existing data.
- A write attribute that tags the origin of the data. When the replication consumer sees a change originating from itself, it can filter that change out and avoid re-applying it.
Application Dual Writes
Shifting away from database centric solutions, the alternative is to build the logic into the application itself. After an initial load, transition to have the application perform database updates to both databases. This will allow the databases to be kept in sync. Additionally, this allows validation to be performed before transitioning reads over to the new database and finally completing the migration by turning off writes to the old database.
One improvement is to use feature flags to control the cutover. By wrapping the application logic with feature flags, you can select which database to read from and which to write to, including writing to both simultaneously.
In cases where data quality is of utmost importance, an additional validation step can be performed by having the application read from both the old and new database. The old database response will be used to service the request, but the response from the new database can be compared and discrepancies logged allowing for validation of the response in the production environment without impacting end users.
Incremental User Migration
Another approach to application migration is to incrementally migrate user data one user at a time, or one tenant at a time instead of moving everything at once. This pattern is an advanced use of feature flags, where the flag is set on a per-user basis instead of for the entire application and is coupled with migrating one user's data at a time. This incremental approach further de-risks the migration process avoiding a singular large data transfer and instead favors an incremental approach that can be performed by an administrator at a desirable time or even given to the user to choose the time either on demand or scheduled.
This migration process can still take time depending on the amount of data associated with a user, and it may result in an isolated outage for that individual, but not for all users of the application. A downside of this approach is that some users will be in the old system and others in the new. While multi-tenant or sharded systems will likely have no issues with this constraint, systems that require frequent data access between users may not be able to utilize this pattern.
Other Considerations
When undergoing a migration there are some other important aspects to consider:
- Schema evolution: If your new database needs a different schema driven from technological or business requirements, this adds complexity to the migration.
- Performance considerations: Both database performance and application server performance play a role, especially in cases involving dual writes or even just taking a full export of the existing database, so planning and testing are vital.
- Data validation: The top priority is the data; therefore, validation checks are critical, not just to ensure all data is properly migrated but that no data loss or corruption will occur during the transition itself.
- Rollback strategies: The objective is to move forward, but sometimes you may encounter issues not seen during testing. Having a rollback plan is vital if you run into a major issue, and even if you don’t, the preparation is time well spent.
Conclusion
Database migrations are complicated, but success depends on deliberately picking an approach that matches your business and technical limitations. The key is to be deliberate. Find the simplest option that meets your requirements and intentionally add complexity only as requirements demand it. Regardless of the path you choose, remember that this is not just a technical exercise… the ultimate goal is to serve the application’s end users.
