Migrating to Cockroach - Part 2

[Version en Français ici]

In the first part, we discussed how to transform and adapt your schema or JSON documents to be used in CockroachDB.
Depending if you're coming from a NoSQL database like Cassandra, MongoDB, Couchbase, etc., or from a relational DB like Oracle, Postgres, MySQL, etc. you have made some changes to your schema. Now it's time to adapt the application to ensure a smooth migration for your customers or users.

From this page, you can find a lot of information about migrating data from Oracle, SQL Server, Postgres, and MySQL.
This will help you import your data from your existing database into CockroachDB. But it won't help you with live migration. What can you do to prevent downtime for your application?
Here, I will present a way to manage this by implementing a concurrency pattern in your repository.

First things first

It's important to clarify a bit about what we'll do here.

This implementation works better when you're using the CQRS pattern, as you have clear segregation between creating and reading data. It's not mandatory by the way, and I'll present an example without CQRS.

The idea is to implement a repository with two different databases. You can imagine a single repository accessing two databases or two repositories managed by an abstraction layer. This is up to you to define what's the best solution to implement in your case. Here we'll use a single repository.

So, when your application will call the data, you will request two databases, wait for the result and check everything is ok. We'll only do that on Query (hence the interest in the CQRS pattern usage), as all Commands must be directed to the Cockroach cluster. Again, you'll have to make a choice. Do I trust by default Cockroach or not? If you trust CockroachDB, the implementation will be easier. If not, you'll have to manage a conflict resolution for the data you received from both sides and push the resolution back to CockroachDB.

The TODO example!

I'll take the easy path with a TODO application example. Imagine we have this table in a Postgres SQL:

I can decide to change it in that way for CockroachDB

I simply changed the id column to a UUID. This will require adapting the DTO to ensure we have the correct definition of objects.

The application looks like this:

The interesting part is the DAO where all database calls are made

In this case, we have simple queries directed to our Postgres database. In the beginning, the table is empty in CockroachDB and contains 400 rows in Postgres.

Save is a command operation and needs to be directed to CockroachDB. But every method using a SELECT needs to be adapted to ask the two databases.

Simple example

Let's focus on this method:

To adapt the method we need to query CockroachDB AND Postgres, compare the results, and decide on which we want to use.
In my repo, I simply added elements for CockroachDB, with a URL configuration and a client ready to accept queries.

The new version looks like this

We use a waitGroup to have the two queries running in parallel and we log the results for migration management. Here is an example output

The more complex example

As I haven't imported data already, we can see a lot of differences in counts. Now it's time to manage this properly!

To do this, I'll use the Get method

In this method, we're retrieving all todos from the table. The idea is to implement the same strategy and to add a migration tooling to push non-existing data to CockroachDB.

First I add a method to manage migration on the fly

I receive the Todos from Postgres and then use the Save method to ensure a proper insert in CockroachDB.

We need to adapt the Get method to manage two concurrent calls and decide if we need to migrate datas or not. I reuse the exact same implementation to manage the two calls

For this example, I'm not doing a deep inspection of the objects to avoid duplicates, I'm simply counting the elements in each array

We just add a log line saying we don't need to access Postgres anymore.

Here is an example of the output

Conclusion

As you can see, this migration process can be really efficient to avoid application downtime. Obviously, it'll require a double run, but depending on your use case, it can be pretty easy to manage, especially in the micro-services era.

Also, this is example is obviously really simple without a lot of data. But it's a starting point to have a reflection on how to manage a live migration.