“Learning never exhausts the mind.”
One of our main customer services is a modular monolith built with Ktor on the backend side, being backed by a PostgreSQL as a DB Engine, using FlywayDB for migrating and performin changes on the database schema.
One of these schema changes (due to constant evolution of our codebase) led to a breaking change and for instance and error, causing an outage on our Development Environment.
The incident occurred due to a failure in the database schema migration process, where the schema change script included an attempt to create new foreign key constraints (FCK) on tables where the information was inconsistent. As a result, an ERROR was triggered due to the data in both tables being INCONSISTENT.
- We run a backup of the database.
- We put our Backend in Maintenance mode.
- An SQL Script was run via console to fix the inconsistent data.
There was no impact on the final users or business, since the issue affected our Development environment (fortunately), but our system was down for 5 days preventing our teams to test new features and perform demos.
When an schema change is performed, these main points should be considered:
- Breaking changes that could affect clients (frontend).
- Complexity of the change that could affect real data.
In order to succeed, a plan should be placed upfront, which should include 2 parts:
- A Schema Migration Script.
- A Data Migration Script (when applies).
Last but not least, apply and keep in mind the BEST PRACTICES when it comes to schema migrations:
- Do not manipulate any data directly inside a schema migration script.
- Always roll forward and in case something fails, create a new migration to rollback.
- Do not change existing scripts, since they are inherently static.