About

Why Migrations Can Cause Downtime?

One common reason for downtime is the application of bad RDBMS migrations during releases.

This issue is related to RDBMS architecture, which needs to know the schema for each query you perform and uses a locking mechanism to ensure only one valid schema is in use.

A common scenario is when a migration query locks an actively used table, causing business logic queries such as SELECT, INSERT, UPDATE, and DELETE to wait until the migration is finished. Even if no popular table is migrated, queries to it keep the connection active. If you have a significant number of stuck connections, no other queries to any other table can be performed in the meantime. This can take minutes, hours, or even longer, resulting in considerable downtime.

Can Unsafe Migrations Be Prevented?

Yes, unsafe migrations can be prevented. Let’s consider two common approaches.

Create a New Table, Fill It, and Switch to It

The safest approach is to create a new table, start writing to both the old and new tables using triggers or application logic, and then switch to the new table.

This method works perfectly but has a few drawbacks:

• it takes engineers’ time to write this logic.
• for large tables, backfilling old data can take days and increase IOPs.
• it limits parallel releases with similar backfilling.

Apply Safe SQL Instead

Safe SQL alternatives allow using an exclusive lock for a short period and less restrictive locks for heavy operations like index creation or constraint validation.

For example:

• create an index CONCURRENTLY.
• create constraints NOT VALID and then validate them.

So Why Does Downtime Still Happen?

Let’s consider some cases.

ORM Is Simple

If you use ORM, it tries to provide unified usage for different databases, which can use different syntax for migrations. Adding safe SQL to ORM increases implementation complexity.

Another reason is that different use cases exist, and applying migrations in a non-safe way can be simpler and faster.

Raw SQL Migrations

If you write migrations manually, you need to consider safe alternatives and locking operations. However, only a small amount of engineers have this knowledge and can apply it without human error.

Linters

If you use linters to find unsafe SQL, they can identify common issues, but you still have to correct your SQL manually according to the process, which can still involve human error.

Process

Moreover, for safe migration, you sometimes need to split some operations into a few steps that combine migrations and releases.

What Can Make Migrations Safe

• use safe SQL.
• follow a strict migration process.
• avoid human error and automate everything possible.