Splitting the data of the monolith – Because who needs to sleep anyway…

In this article, I would like to share our twisted journey about the data migration from our old monolith to the new “micro” databases. I would like to highlight the specific challenges we encountered during the process, present potential solutions for them, and outline our data migration strategy.

• Background: summary and the necessity of the project
• How to migrate the data into the new applications: describe the options/strategies how we wanted and how we did the migration
• Implementation
  • Setting up a test project
  • Transforming the data: difficulties and solutions
  • Restoring the database: how to manage long running sql scripts with an application
  • Finalising the migration and preparing for go-live
  • DMS job hiccup
• Going live
• Learnings

If you find yourself knee-deep in technical jargon or it is too long, feel free to skip for the next chapter—we won't judge.

Background

Our goal was during the last two years to replace our old monolithic application with microservices. It's responsibility was to create customer related financial fulfillments, and ran between 2017 and 2024, soit collected extensive information about logistical events, shop orders, customers, and VAT.

Financial fulfilment is a grouping around transactions and connects trigger events, like a delivery with billing.

The data:

Why do we need the data at all?

Having the old data is crucial:including everything from history of the shop orders like logistical events orVAT calculations. Without them, our new applications cannot process correctly the new events of the old orders. Consider the following situation:

1. You ordered a PS5 and it is shipped– The old application stores the data and sends a fulfilment
2. The new applications go live
3. You send back the PS5, so the new apps need the previous data to be able to create a credit.

The size of the data:

Since the old application had been started: it had collected 4 terabytes from which we still would like to handle 3T in two different microservices (in a new format):

• shop order, customer data andVAT: ~2T
• logistical events: ~1T

Handle history during development:

To manage historical data during development, we created a small service, which reads directly from the old app database and provides information through REST endpoints. This way can see what has already been processed by the old system.

How to migrate the data into the new applications?

We worked on a new system and by early February, we had a functional distributed system running in parallel with the old monolith. At that point, we considered three different plans:

• Run the mediator app until the end of the Fiscal Period (2031):
  PRO: it is already done
  CON: we would have one extra "unnecessary" application to maintain.
• Create a scheduled job to push data to the new applications:
  PRO: We can program the data migration logic in the applications and avoid the need for any unfamiliar technology.
  CON: Increased cloud costs. The exact duration required for this process is uncertain.
• Replay ALL logistical events and test the new applications:
  PRO: We can thoroughly retest all features in the new applications.
  CON(S): Even higher cloud costs. More time-consuming. Data-related issues, including the need to manually fix past data discrepancies.

Conclusion:

Because the tradeoff was too big for all cases I asked for help and opinions from the development community of the company and after some back and forth, we setup a meeting with couple of experts from specific fields.

The new plan with the collaboration:

Current state of the system(s): Setting the scene

Before we could go ahead, we needed a clear picture of where we stood:

• Old application runs on datacenter
• Old database already migrated to the cloud
• Mediator application is running to serve the old data
• Working microservices in the cloud

The big plan:

After the discussion (and a few cups of strong coffee), we forged a totally new plan.

• Use off-the-shelf solution to migrate/copy database: use Google’s open source Data Migration Service (DMS)
• Promote the new database: Once migrated, this new database would be promoted to serve our new applications.
• Transform the data with Flyway : Utilising Flyway and a series of SQL scripts, we would transform the data to the schemas of the new applications..
• Start the new applications: Finally, with the data in place and transformed, we’d start the new applications and process the piled-up messages

The last point is extremely important and sensitive. When we finish the migration scripts, we must stop the old application, while we are collecting messages in the new applications to process everything at least once either with the old or the new solution.

Difficulties -the roadblocks ahead:

Of course, no plan is without its hurdles. Here’s what we were up against:

• Single DMS job limitation: The two database migration jobs must run sequentially
• Time-consuming jobs:
  • Each job took around 19-23 hours to complete
  • Transformation time: the exact duration was unknown
• Daily fulfilment obligations: Despite the migration, we had to ensure that all fulfillments were sent out daily - no exceptions.
• Uncharted territory: To top it off, nobody in the company had ever tackled something quite like this before, making it a pioneering effort. Also, the team are mainly Java/Kotlin developers using basic SQL scripts.
• Go live date promise with other dependent projects in the company

Conclusion:

With our new plan in hand, with the help provided by our colleagues we could start working on the details, building up the script execution, and the scripts themselves. We also created a dedicated slack channel to keep everybody informed.

Implementation:

We needed a controlled environment to test our approach—a sandbox where we could play out our plan, also to develop the migration scripts themselves.

Setting up a test project

To kick things off, I forked one of the target applications and added some adjustments to fit our testing needs:

• Disabling the tests: all existing tests except for the context loading of the Spring application. This was about verifying the structure and integration points, also the flyway scripts.
• New Google project: ensuring that our test environment was separate from our production resources.
• No communication: all inter-service communications - no messaging, no REST calls, and no BigQuery storage.
• One instance: to avoid concurrency issues with the database migrations and transformations.
• Remove all alerts to skip the heart attacks.
• Database setup: Instead of creating a new database on production, we promoted a “migrated” database created by DMS.

Transforming data: Learning from failures

Our journey through data transformation was anything but smooth. Each iteration of our SQL scripts brought new challenges and lessons. Here’s a closer look at how we iterated through the process, learning from each failure to eventually get it right.

Step 1: SQL stored functions

Our initial approach involved using SQL stored functions to handle the data transformation. Each stored function took two parameters - a start index and an end index. The function would process rows between these indices, transforming the data as needed.

We planned to invoke these functions through separate Flyway scripts, which would handle the migration in batches.

PROBLEM:

Managing the invocation of these stored functions via Flyway scripts turned into a chaotic mess.

Step 2: State table

We needed a method that offered more control and visibility than our Flyway scripts, so we created a: State table, which stored the last processed id for the main/leading table of the transformation. This table acted as a checkpoint, allowing us to resume processing from where we left off in case of interruptions or failures.

The transformation scripts were triggered by the application in one transaction, which also included updating the state table state.

PROBLEM:

As we monitored our progress, we noticed a critical issue: our database CPU was being underutilised, operating at only around 4% capacity.

Step 3: Parallel processing

To solve the problem of the underutilised CPU, we created a lists of jobs concepts: where each list contained migration jobs, which must be executed sequentially.

Two separate lists of jobs have nothing to do with each other, so they can be executed concurrently.

By submitting these lists to a simple java ExecutorService, we could run multiple job lists in parallel.

Keep in mind all job calls a stored function in the database and updates a separate row in the migration state table, but it is extremely important to run only one instance of the application to avoid concurrency problems with the same jobs.

This setup increased CPU usage from the previous 4% to around 15%, a huge improvement. Interestingly, this parallel execution didn’t significantly increase the time it took to migrate individual tables. For example, a migration that initially took 6 hours (when it runs solely) now took about 7 hours, when it was executed with another parallel thread - an acceptable trade-off for the overall efficiency gain.

PROBLEM(S):

One table encountered a major issue during migration, taking an unexpectedly long time—over three days—before we ultimately had to stop it without completion.

Step 4: Optimising the long-running script(s)

To make this process faster, we required extra permissions to the database and our database specialists stepped in and helped us with the investigation.

Together we discovered that the root of the problem lay in how the script was filling a temporary table. Specifically, there was a sub select operation in the script that was inadvertently creating an O(N²) problem. Given our batch size of 10,000, this inefficiency was causing the processing time to skyrocket.

Figure 1: Example analyse script

To address the issue, we rewrote the script with the updated approach:

• Instead of relying on the subselect, we created a temporary table that performed a join between the two necessary tables upfront. This way, the heavy lifting was done once, reducing the need to repeatedly scan the data.
• After creating the temporary table, we then inserted the rows into the real table. This change effectively transformed the operation from O(N²) to O(2N).
• Finally, we started to merge similar jobs together to handle them in one execution and creating temporary tables only once.

RESULT:

The results were immediate and impressive. With the new approach, the previously unmanageable table transformation now completed in 10-12 hours a significant improvement, and most importantly, a predictable and stable time frame.

Restoring database constraints and indexes:

Once the data was transformed and migrated successfully, our next task was to restore the database constraints and indexes. However, as with many things in the world of data migration, it wasn’t as straightforward as one might hope.

Time-consuming index creation

Creating certain indexes had taken more than an hour, and because we planned to create them via flyway: the application failed to start, rollback the flyway transaction, so, on the next application start it would try to create the index again from the beginning starting an endless loop.

A Practical solution: concurrent index creation

Using PostgreSQL’s CREATE INDEX CONCURRENTLY allows the database to build the index without locking the table.

However, there’s an important consideration: CREATE INDEX CONCURRENTLY operates outside of the usual transaction mechanisms. This means that if the application failed to start due to a timeout, the creation process will continue in the database. Once the index was finally built, the next time the application attempted to start, the IF NOT EXISTS clause in our script would gracefully avoid any further attempts to create the index, allowing the Flyway migration to proceed smoothly.

Ensuring continuity

This solution, while not the most traditional, was highly effective. By ensuring that the index creation process continued even if the application startup failed, we allowed the migration to complete successfully. Flyway’s version history was updated once the indexes were in place and the application started, ensuring that our database was in a consistent state.

The key takeaway here is that sometimes, a practical and flexible approach is the best way to overcome challenges.

Finalising the migration and preparing for go-live

After the initial phases of data transformation and index management, our last major task was to restore the database to its original state. This involved carefully reverting any temporary changes, cleaning up our Flyway migration scripts, and preparing for a full-scale test run to ensure everything was ready for production.

The three merge requests

The entire process was divided into three key merge requests/releases, each playing a critical role in the migration:

• Preparation: (1-2 hours)
  • scale down to 1 instance
  • dropped unnecessary indexes and constraints.
  • created new necessary indexes
  • add stored functions/migration scripts(but without executing them)
• Execution of data transformation (1-2 days)
  • added execution code
  • ran table transformation
• Restoring the database state (3-6 hours)
  • reinstated all original indexes, constraints
  • cleaning up any temporary changes, classes

The DMS job hiccup: The case of the missing foreign key

As with any complex migration project, unexpected issues can arise, and in our case, the data migration service (DMS) threw us a curveball. After about 18 hours of running smoothly, our DMS job suddenly failed, multiple times:because of a missing foreign key.

Investigating the issue

The error message indicated that a foreign key was missing, causing the DMS job to fail. However, when we inspected the database, the foreign key was present. The confusion deepened as we realised that the row in question had been created around the time the migration started. It seemed that the DMS job’s sinking process—where it catches up with ongoing changes - had somehow missed this update.

Our database team took the lead in investigating the issue, also reached out for external help, escalating the problem through multiple channels:

Resolution: DMS parallelism option

After about two weeks of investigation and back-and-forth with support, we finally got the solution from the Bol/Google shared slack channel. It turned out that DMS has(or introduced) a parallelism option and by setting this option to "minimal," the job started working correctly, and the foreign key issue was resolved, also we were able to continue the go live plan.

Going live: The final countdown

At this point, the bulk of the heavy lifting was behind us. We had meticulously planned, tested, and overcome numerous challenges in preparation for this moment. The final step was to execute the go-live process, bringing all our efforts to fruition.

Rather than detailing every step once again, it's safe to say that everything we've covered so far led us to this moment. The process is straightforward, although it is critical to keep the dates and sequence in order.

Learnings:

It was quite a journey, so I would like to highlight again a couple of learnings in the end:

• Talk through everything multiple times with different people and reach out for help in case you have concerns.
• When you try to explain something try to be as clean/specific as possible and ask for feedback, make sure the other understands the situation as you wanted. Illustrations always welcome!
• Try to be humble and patient, when you are asking for help. The priorities can be different, you may not know the other parties’ priorities.
• Manage your process in a project management tool to track the processes: to not forget any detail, small task which can kill your project.
• For us the full process took 2-3 months. A lot of unforeseen issues happened during the migration, which could have failed our project on many points. Always try to take small steps/problems on an unfamiliar journey, which gives you the confidence and the positive feedback when you achieve a small success.

Thank you for reading this post I hope you enjoyed and learnt a bit from it, because it is always good, faster to learn from other’s mistakes.