Why database migrations are more complicated than they look

Modifying a database often seems quite simple at first glance. Add a column, create an index, rename a field, or change the type of a value. With a few lines of SQL, the operation can be completed in a few seconds on a development database.

But between a database containing a few thousand rows and one containing several tens of millions of rows, the situation can be very different.

A migration that takes a few seconds in development can sometimes block an application for several minutes, or even much longer, and see production go completely haywire 🙁

Adding a column is not always trivial

Let's take a table containing several tens of millions of rows. Adding a column may seem like one of the simplest operations:

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

And in this particular case, PostgreSQL can indeed perform the operation very quickly, because adding a column without a default value does not require rewriting the existing rows.

But not all schema changes are that simple. For example, changing the type of a column may require rewriting the table and its indexes. Adding a volatile default value can also trigger a full rewrite. Other operations, such as certain constraints or creating an index, may require a large amount of data to be scanned.

You also have to take locks into account. An operation that appears to be quick may still have to wait for another transaction to release a lock, or prevent certain queries from running while it is being executed.

On a small table, the difference is rarely noticeable. On a table containing several tens or hundreds of millions of rows that is constantly being used, it can become significant.

So you should avoid considering an ALTER TABLE as an operation that is necessarily fast. Before running a migration in production, you need to know exactly what the database engine is going to do behind that instruction, how much data it will have to process, and which locks will be required.

Locks can block the application

Another problem comes from locks. Some migration operations require a table or certain data to be locked while they are running. During that time, queries trying to access the same data may have to wait.

Imagine a table being accessed several thousand times per second. If a migration holds a lock for long enough, queries start piling up behind it. An operation that was supposed to take a few seconds can then cause a significant increase in latency.

And the problem can quickly become visible to users: pages loading very slowly, timeouts, HTTP errors, or even a completely unavailable application.

So it's not just the duration of the migration that matters. You also need to know what can continue to operate while it is running.

Indexes can be expensive to create

Creating an index is generally a good thing for query performance. But on a table containing several hundred million rows, creating the index can itself become an expensive operation.

The database has to scan the existing data and build the index, which can consume a lot of CPU, memory, and I/O. You also have to take the locks used during the operation into account. Depending on the database engine and the method used, creating the index may prevent certain write operations on the table.

PostgreSQL, for example, provides CREATE INDEX CONCURRENTLY, which allows an index to be created without blocking INSERT, UPDATE, and DELETE operations on the table. However, the creation takes longer and this method has its own constraints.

Once again, a command that seems harmless on a small database can have a significant impact when it is run on a large table in production.

The downtime problem

For a long time, a relatively simple solution was to stop the application, perform the migration, and then restart it.

With an application used only during office hours, this can sometimes be acceptable. But for a service available 24 hours a day, it's no longer really an option. Today, having to completely stop a service to perform a migration seems rather anachronistic.

A migration lasting a few minutes can mean a lot of failed requests or users being unable to use the application.

You therefore need to be able to perform these changes progressively, without completely blocking the application. The data can be migrated little by little, sometimes with a period during which the old and new formats have to work in parallel.

Old and new versions sometimes have to work together

This is where things start to get interesting. Imagine that an application currently uses a name column and we want to replace it with first_name and last_name.

We could simply delete name and add the two new columns. But during a deployment, several versions of the application may temporarily be running at the same time. Some instances may still try to use name, while others are already using first_name and last_name.

The database migration and the code deployment therefore sometimes need to be designed together. A common approach is to make the change in several steps.

First, we add the new columns without removing the old one. The new version of the code can then start writing to the new columns while continuing to handle the old format. The existing data is migrated progressively, and once all instances are using the new code, the old column can finally be removed.

This requires a few extra steps and several deployments, but avoids depending on a change happening instantly.

Existing data is often the real problem

And this is where migrating the database structure is often only the first step. Adding an empty column is one thing. Filling it with existing data is another.

Imagine a new column that needs to be calculated from several million rows. Doing the whole operation in a single query can take a long time and generate a significant load on the database.

It may be better to process the data in small batches. For example, instead of modifying 100 million rows in a single operation, you can process a few thousand rows at a time (to keep locks to a minimum), with perhaps a short pause between each batch.

It's less spectacular than a big UPDATE, but much easier to control.

You also have to think about rollback

A migration is not always as easy to reverse as you might think.

Adding a column is relatively easy to undo.

But if a migration transforms or deletes data, going back can be much more complicated.

A rollback script that technically works does not necessarily mean that it can restore lost data.

This is why a recent backup and a recovery strategy are still important before certain major migrations.

A migration should be tested under real constraints

Testing a migration on a development database is essential, but it isn't always enough if you want to limit unpleasant surprises.

A development database often contains much less data and much less traffic. A migration that takes one second with 50,000 rows can behave completely differently with 200 million rows.

So, when possible, it's a good idea to test migrations with a volume of data close to production and measure their duration, resource consumption, and any locks involved.

You should also test migrations while the application is running normally. A migration may run quickly on an inactive database and become much more problematic when hundreds of queries are being executed in parallel.

This is particularly important for migrations that modify heavily used tables. A few seconds of difference on a development database can sometimes mean several minutes of blocking or degraded performance in production.

A migration is also a deployment problem

Ultimately, a database migration is not simply a matter of SQL. The larger an application becomes, the more you need to think about the duration of the operation, locks, the amount of data, traffic, compatibility between different versions of the code, and the possibility of rolling back.

On a small application, you can sometimes afford to run an ALTER TABLE and see what happens. In production, with millions of rows and active users, it's a different story.

A good migration isn't necessarily the one that runs the fastest. It's above all the one that allows the database to evolve without disrupting the application.

And as is often the case in development, taking a few minutes to understand what is actually going to happen can save you from many more problems later.

Good luck with your next data migrations.