InnerWeb
Skip to main content

Database migrations during a system update: implementation notes

A system update is not only a new container image but also the database migrations that change the data structure. On an installation with a long version history, every migration has to cope with the state the installation actually has. Below is a checklist for the administrator: what to check before the run, how to calculate the length of the window and which rules the vendor applies.

6
installations whose copies were used to check the migrations
Source: karta
3-32 s
range of migration run time on these copies
Source: karta
380
migrations in the chain at start from an empty database
Source: karta

What a migration is and why it can be risky

A migration is a script that changes the structure of the database: it adds a column, creates a table or changes an index. A new application version assumes the migrations have been run, so interrupting one halfway leaves the system in a state where the software does not match the database. The most exposed are installations with a long version history, because they have gone through many changes and may differ from one another in small details.

That is why the vendor treats migrations as production code and checks them on copies of real installations, and the administrator has additional steps to take before the run.

Migration writing rules on the vendor side

We have adopted four rules. Migrations do not use an ORM inside the script, because object mapping depends on the current code, not on the historical state of the database. Each statement has a guard for installations with different histories, so that it does not assume a column or index already exists or does not yet exist. Exceptions are caught narrowly, so that a real error does not disappear in a general catch. Finally, a ban on skipping migrations: a skipped migration stays pending forever and comes back as a problem at every later update.

A CI job runs the whole migration chain on an empty database on every change and requires the database to match the code. This checks the path from scratch, but it does not replace a trial on a copy of a database with a long history.

  • no ORM inside migrations
  • a guard on every statement for installations with different histories
  • narrow exception catching
  • no skipping of migrations

Range of migration time on production copies

Range of migration time on production copiesBar chart: the shortest migration took 3 seconds and the longest 32 seconds on copies from six installations.010203040secondsShortest: 3 seconds3ShortestLongest: 32 seconds32Longest
Source: Source: trial on production copies from six installations (3-32 s)

Trials on production copies: the times we know

The migrations were checked on production copies from six installations running the code of the new branch. Each one finished successfully, and the run time varied from a few to several dozen seconds, precisely within the range of 3-32 s. These are the times for specific migrations and specific database sizes, so with a different change they may be different.

The practical conclusion: add to the maintenance window a reserve many times larger than the longest measured time, and measure the time on a copy on your own installation's preproduction.

What to check before running on production

The order of the steps matters more than the commands themselves. Start with the backup, then preproduction, then the production window.

  • A current backup of the database and the file store, taken just before the window.
  • The backup restored and checked on preproduction.
  • Migrations run on preproduction with the new code version, with the time recorded.
  • Free disk space larger than the size of the largest table.
  • The list of pending migrations compared with the list from the backup, with nothing skipped.
  • A rollback plan: the backup at hand and a person who restores it.

Pitfalls we have seen in practice

The first: a migration chain breaking at the seventh migration of a fresh database. It surfaced only when someone tried a start from scratch, so we added a CI job that watches for this with every change. The second: the assumption that all installations look the same, which fails with a long version history. The third: running heavy tasks at the same time as the migrations, because they compete for memory and disk.

For comparison, a clean environment started from an empty database to a working login took about 8 minutes in the test, mainly because of running 380 migrations, but that time applies to an installation without history and data, not to production.

Video

Frequently asked questions

How long do migrations take during an update?

On production copies from six installations, from 3 to 32 seconds. Measure the time on your own installation's preproduction, because it depends on the size of the database.

Can a migration that fails be skipped?

No. A skipped migration stays pending forever. The cause has to be found, fixed, and the migration run again.

Why run a trial on preproduction if CI checks the migrations?

CI checks the path from an empty database, while preproduction checks the state of an installation with a long history and the real size of the data.

Will an older installation pass the update?

The migrations have guards for installations with different histories, but the trial on a copy of the specific installation makes the decision.

Sources

Let's plan the update together with IT

Together we will set the order: backup, a migration trial on preproduction and the production window with a rollback plan.

Let's talk