Do Database Migrations Always Require Downtime?

So recently i came across a backend issue which needed a existing db migration with existing production database. And ahhh my scary ass didnt wanna do the migration. But the issue needed a persistent state so i had to do it either way. Earlier i thought dealing with existing db migrations was scary but nah i was wrong it totally depends on the situation which is why i thought of writing this.

Can we avoid db migration?

Honestly i thought of this while working on that issue but again it entirely depends on various things such as -

1. Can existing columns represent the state?

2. Can application logic derive it?

3. Can a cache/external store work?

4. Can we use a separate table?

5. Is the schema change genuinely necessary?

Sometimes you can avoid the migration but if the new state fundamentally belong in the db persistent model then a schema change is unavoidable.

If we need a migration, does it always cause downtime?

No db migration does not always mean downtime, Its impact depends on what you're changing and how the database performs that operation and it also depends on various things such as -

• database engine

• exact migration

• table size

• indexes/constraints

• existing traffic etc.

Let's look at 3 common types of schema changes -

1. CREATE TABLE
   Its usually one of the safer operation in migration cause we are creating a new table that didnt existed earlier so there is no existing data that needs to be modified.

2. DROP TABLE
   This one comes with a great risk because dropping a table while some area of the application code still uses this can be risky. Dropping it could suddenly break something.
   This is where I found GitHub's approach pretty interesting. Instead of directly deleting a table, they rename it first.
   So intead of doing :

DROP TABLE repositories;

they rename it to something like:

RENAME TABLE repositories TO _repositories_DROP_20200101123456;

Now the table is basically out of the application's way, but the actual data is still there. And if something breaks, they can simply rename it back:

RENAME TABLE _repositories_DROP_20200101123456 TO repositories;

They keep these renamed tables around for a few days and only delete them later.
I found this approach good cause this way we can make a potentially destructive change reversible.

1. ALTER TABLE

Alter table can mean a lot of different things and not every alter table has the same impact. Some changes can be almost instant, while others can require a lot of work on the existing table.

ALTER TABLE users ADD COLUMN phone_number TEXT;

The above query might be pretty straightforward but an operation that needs to modify or rebuild a large amount of existing data can take significantly longer.
And this is where the size of the table starts to matter. Something that takes seconds on a table with a few thousand rows can become a completely different problem when you're dealing with millions or billions of rows.

And that's why we can't really say that DB mirgrations can lead to downtime. We first always need to understand what exactly we are changing and how DB is going to handle it.

What makes a production migration risky?

Why is it risky in the first place well most of the time it comes from ALTER TABLE because in production long running DDL locks the table, API pods can temporarily run with different expectations of the schema, and a rollback is no longer “revert the deploy” because the data has already moved. Ordering, lock-durations and mixed versions are likely the expensive failures instead of SQL syntax.

How do we safely perform the migration?

So the ques comes that can we run DB migrations while application is running?
well yes we can and this is where the zero-downtime migration comes from.

Zero-downtime migration is a contract b/w schema, application code and deployment cadence usually expressed as expand-contract pattern.
The basic idea is that instead of changing everything at once, we gradually move the application and database from the old state to the new state. It goes like this -

expand -> migrate data -> contract

Phase 1 : Expand

The term expand itself means add new thing to the database without removing or breaking old thing.

1. We should add nullable columns or new tables.

2. Add indexes concurrently where engine supports it. eg - CREATE INDEX CONCURRENTLY. It is effecient and do no blocks writes on larger tables.

3. Add constraints carefully. Adding a constraint to a large existing table can mean checking a lot of existing data. Instead of doing all of that at once some databases let us add the constraint first and validate the existing data separately. eg- NOT VALID.

The goal of expand is that old application code must still run correctly while we add new nullable columns and tables to have safe defaults.

Phase 2 : Migrate the data

1. Ship the application code logic that writes to both old and new representations or writes to the new column while still reading from the old one until the backfill is complete.

2. Now we also need to deal with the existing data. If the table already has millions of rows, we probably don't want to update everything in one huge query. Instead, we can backfill the data in smaller batches so that we're not putting too much load on the database or causing replication lag.

3. During this process the application can prefer the new value when it's available and fall back to the old value when it isn't.

And this is also where monitoring becomes important. We should know how much of the backfill is completed, whether replication is falling behind and whether the new application logic is actually working as expected.

Phase 3 : Contract

1. Now that we know that new representation is being used everywhere and we are sure that application no longer depends on the old representation, we can safely remove the old representation.

2. We can deploy code that no longer uses the old columns and tables.

3. Before removing we need to make sure all the existing data has been migrated and new representation is working correctly.

And we should not remove old paths too early cause once done there is no fallback.

Lets understand this with the help of an example.
Changing a 'name' field with first_name and last_name -

Phase 1 : Expand - We dont remove the 'name' field yet. We should add the new columns first.

ALTER TABLE users
ADD COLUMN first_name TEXT,
ADD COLUMN last_name TEXT;

Now the database will look like this -

users

id | name          | first_name | last_name
---|---------------|------------|----------
1  | John Doe      | NULL       | NULL
2  | Jane Smith    | NULL       | NULL

The key thing to notice is that name still exists and is used by old application code.

Phase 2 : Migrate - Now we need to deal with the existing data. We want to have the existing user's data name to be split into first_name and last_name.

For tiny databases we can backfill the existing rows but for large production databases we necessarily dont want to hit million of rows with one massive operation. Instead we do backfill in batches -

1k rows -> backfill -> 1k rows -> backfill ...

At the same time we can update the application so that new writes also populate the new columns.

And while the migration is happening, reads can use the new values when available and fall back to the old value when they're not.

Phase 3 : Contract - Now the application is being used up by new representation we can remove old representation -

ALTER TABLE users
DROP COLUMN name;

When is a maintenance window actually justified?

After learning about zero-downtime migrations, it's easy to start thinking that every migration should be done without downtime. But I don't think that's necessarily true.

Sometimes a maintenance window is actually the better and safer option.

For example - if the database is small, the migration is known to be quick, some downtime is acceptable, or the migration can't realistically be done safely while the application is running, then there is nothing wrong with taking the application down for a short and planned maintenance window.

And I think that's what I learned from all of this. DB migrations aren't scary It depends on what you are changing, how much existing data you have, how your application is running, and how you're performing the migration.

Sometimes you need zero downtime migrations. Sometimes a simple maintenance window is completely fine.

That's it for the blog more important things should have been there like how migration takes place accross DB shards but kept it short and simple maybe ill write it next time :p
hope you enjoyed reading this.