About the project
mySugr is a Viennese startup, founded in 2012. In June 2017, it joined the Roche family. Due to the integration of the companies in 2018, the group faced many system challenges. Specialists from fireup.pro supplemented the team dedicated to their effective resolution. The mySugr application is a tool dedicated to individuals with diabetes, which the fireup.pro developers equipped with an external connection to physical tools measuring blood sugar levels, called Continuous Glucose Monitoring (CGM). As a result, more than 1.8 million users benefit from a reliable, worldwide diabetes diary, allowing them to conveniently review both self-entered application data and data retrieved from CGM devices.
Customer experience
Have you ever seen a Formula 1 race or the Disney movie "Cars"? Were you impressed by how quickly they can change tires?
We did something similar! But we like challenges, so we did it while the car was still on the racetrack — in motion! This "we" is actually a very small team of the most talented people I've ever met.
Karl Spies
Backend Developer at mySugr
From challenge
Key Challenges
A shared branch of the application with support for MySQL and PostgreSQL
A shared branch of the application with support for MySQL and PostgreSQL
We had to address issues such as MySQL taking a more lenient approach to SQL standards and query syntax. The default and recommended transaction isolation levels are also different for both systems. PostgreSQL is stricter and does not attempt to second-guess the user as MySQL does.
PostgreSQL as the preferred system
PostgreSQL as the preferred system
Consequently, we decided that the best solution would be to refactor the code to work correctly on both of the mentioned platforms.
Anticipated benefits of the migration process:
Reduction in overall maintenance costs of the target client application
Flexibility arising from IT system virtualization
Optimization of resource utilization of the employed hardware
Increase in system performance
Enhancement of system availability
Improvement in data security
Through the solution
Through the solution
Verification
Theoretical proof of concept for the use of PostgreSQL and creation of proof of concept and smoke testing.
- Creating an application that will run with both PostgreSQL and MySQL.
Migration
- Database schema migration.
- Creating tables in PostgreSQL with appropriate data types.
- Comparison of source and target data types.
Data transfer
- Transferring data from proof of concept to the real application.
- Verification of the risks associated with the difference between PostgreSQL and MySQL tools.
Proof of concept with AWS DMS
- Creating the infrastructure for the migration of the developer base.
- Based on the proof of concept point in the AWS DMS, creating migrations for the other environments.
Switching development environments
- 2 months of testing.
- Verification of migration on the QA environment.
Main phase
Possible after positive verification and lasting 2.5 weeks.
- QA switchover.
- Switch production environments and make the application available to the client.
To the success
Technological outcomes
Technology change – migration from MySQL to PostgreSQL
Customizing the system to the technology used within the Roche group.
Implementation of the new version Amazon Aurora
Upgrading to a newer version of the database that offers better performance and support.
Securing system support
Avoiding the risk of using unsupported components after 2024.
Simplified mechanism for switching databases
The ability to switch by changing a single parameter in the application.
Business benefits
Standardization of the technology stack with Roche.
Significant reduction in application maintenance costs after the implementation of Aurora.
Delivery of a consistent and ready product within the planned timeframe, in line with the client's vision.
Tech stack
Stage 1
Initial-load
We create a copy of the source database (snapshot), then import the data from the copy into the new database.
Stage 2
CDC continuous migration
Continuous data capture collects all changes to the source database data and then applies them to the target database. This ensures that the data in both databases is identical.
Database Risks for mySugr and Roche
Risk 1
Differences in datetime precision
The types of databases in the source and target systems were different. DateTime in MySQL (mySugr) has an accuracy of up to 1 second, while in PostgreSQL (Roche), it's accurate up to microseconds to 6 decimal places. From our perspective, it was necessary to verify whether these differences would impact the application's functionality and to consider rounding data to the nearest second.
Risk 2
Incompatibility in data types
The PostgreSQL tool handles returning floating-point data of type FLOAT double differently than MySQL, causing inconsistencies in data types.
Risk 3
Differences in handling character case
MySQL and PostgreSQL have different approaches to character case sensitivity. MySQL defaults to case-insensitive string comparison. Thus, it became necessary to apply manual text searching and unify tools to ensure both operate in a case-insensitive manner.
Risk 4
Data conversion and interpretation
Issues related to conversion and interpretation also arose. The transfer of data from the old database to the new one was in progress. Therefore, the team had to find a way to ensure that the data was the same in both cases.
Your success is our success
See how we can build a technological advantage for your company together.
We have a team that truly knows its stuff — we'll help you find a solution that works.
