Migrate to a New Database Without Impacting Your Users
by Martin Führlinger, Software Engineer Backend
The need to change databases
In the Runtastic Portfolio, we have plenty of entities. Most of them are stored in databases, while others are calculated on the fly. Examples of these are a sports activity, a heart rate measurement or a weight measurement. In this blog post, we’ll talk about the weight measurement entity. Each measurement a user enters is stored in the database and visualized for the user as a graph on Runtastic.com.
The need to move that entity into another database is easy to explain. We have millions of users with hundreds of millions of entities. When a company grows as fast as Runtastic is, it is just a matter of time before it becomes difficult to scale in an SQL-based database like Oracle or MySQL. Therefore, we decided to move that data into our MongoDB cluster, which is much easier to scale horizontally than an SQL-based database.
At first glance, moving data from one database to another seems to be easy. Dump it in the old one and import it into the new one. But dumping hundreds of millions of entities would take quite a long time, and then you still have to import them into the new database. Also, the format is completely different between most of the databases.
At Runtastic, we cannot accept downtime. Therefore, we need to migrate the data in our production environment, without the user noticing the change. And as we live in a micro-service environment with multiple clients, we would have to update all micro-services at once, which is hardly possible and very error-prone.
This leads us to the following requirements:
- No downtime
- No client or user should notice the migration
- Even our services should not notice the migration
- Keep everything working as before
- Do not lose data while migrating
Obviously, we have to prepare some stuff before we can really start moving entities to the new database.
We have multiple services using their “built-in” capabilities to access the entity in the shared database. Like ActiveRecord or Sequel in Ruby services or Hibernate in our Java Enterprise (J2EE)-based application server. All of these usages of that entity are spread over the whole code in each service and accessing the database directly.
As having shared database access is an anti-pattern, and we want to move away from that in our services anyway, we decided to separate the business logic from the database access using the repository pattern. This allows us to change where and how the data is stored without affecting the business logic.
The second big thing we are currently about to change for all our entities is to have only one service which is responsible for it. This helps in various ways. For instance, it is possible to lock the entity when writing it, monitor access, or start a migration, all in one place. This pattern, which we try to apply for all our services, is simply named Database per Service.
This leads to the first steps:
1. Use the repository pattern for data access
2. Create a single responsibility service for that entity
3. Use that service through the repository pattern
4. Change the storage of the entity
Thus, the first step is to write a repository for the weight measurement which is able to do everything as before, inside a MySQL strategy. An easy way to include the repository pattern into your Ruby application is to use the receptacle gem.
Having prepared that, the second step is to create the single responsibility service which is able to do everything with that entity as the spreaded code before. This can basically be reduced to being able to create, read, update, and delete it, which is simply a CRUD interface. In an intermediate state, it still uses the old database, while also using a repository pattern, as you can see in the following picture.
Then it is time to write another strategy for the same repository. This strategy has to have the same interface as the MySQL strategy but uses the service via HTTP to access the entity.
When all services which use that entity are adapted to use the repository pattern and are adapted to use the new responsible service for accessing that entity, we are able to change the storage to one place. As mentioned above, we cannot do a big bang and export/import all the data at once. We have to migrate it step by step.
As we now know that all accesses to that entity are routed through our service, and no one is accessing the database directly anymore, we can start moving the entities between the databases. Depending on the entity, it may be done differently, but in this case, it is a highly user-related data, so we do it per user.
Our new responsible service has some code prepared to check if a user is already migrated or not. If the user is migrated, it accesses the new database. Otherwise, it accesses the old one. This can easily be done by storing the migration state in a Redis database, with the “set” datatype.
This check is performed each time a weight measurement is accessed and our service accesses the correct database. Additionally, we start a delayed asynchronous migration job for that user if he/she is not migrated yet. This helps to keep the load from the servers and only do those jobs on easily scalable workers. Each job then migrates all of these entities from one user to the new database. As soon as the job is finished, the user is marked as migrated and in future that user’s accesses will no longer hit the old database. This flow helps to get active users migrated fast.
When the number of jobs is decreasing and we have migrated most of the active users, we usually do a “run-over-all-not-migrated-users-and-migrate-them” job. This could take a few weeks, depending on when we start it, how many resources we have, and how many entities are left. But as this whole process is completely hidden from our users, it doesn’t really matter that much.
Below are the steps we at Runtastic recommend for migrating entities from one database to a new one without interrupting any services:
1. Use the repository pattern to separate business logic from data storage in all services.
2. Make sure only one service takes care of the storage of one entity.
3. Connect the old database to the new service and test.
4. Connect the new database.
5. Now move data to the new database without interruption
a) Migrate active users first.
b) Migrate asynchronously.