I sped up the test suite by 2x with one simple change

Philippe Gaultier

Dark/Light

Body of work

Tags

Resume

⏴ Back to all articles

Published on 2026-06-12. Last modified on 2026-06-15.

I sped up the test suite by 2x with one simple change

Go SQL Optimization

Table of contents

Quick and dirty check

The implementation

Edge cases and dead-ends

Why not squash all migrations?

Conclusion

We have a giant test suite at work, mostly in Go. The test coverage is great, but it means that it's not that fast to run, and it only will get slower over time as new tests are added. Almost every test needs a pristine database. We are spending a ton of CPU time just applying again and again the same SQL migrations at the start of each test.

As mentioned in a previous article, thousands (!) of SQL migrations have accumulated over the years, and I had to fix a performance issue where we spent a lot of time simply gathering all migration files (not even applying them).

With that fix done, the next bottleneck was applying these migrations. A few reasons make this part very appealing to optimize:

Every test using a database runs this code

Applying the migrations is done serially (one at a time) and no test code can run until migrations are fully applied

It is entirely unnecessary to apply each migration one by one, nearly all tests are only interested in the latest database schema

And so I decided to optimize it. When doing performance optimizations, it's important to spend some time first deciding if it's worth your time on paper!

As always: the code is open-source.

Quick and dirty check

Optimization work can be very unrewarding: you spend a lot of time and at the end when you measure, to see no difference (or perhaps worse performance than before!).

So it's also very important, if possible, to do a quick and dirty check at the beginning, to see if the optimization has any legs.

In my case, here's what I wanted to see: let's assume that every test has access to a ready-made database, with an up-to-date database schema. What's the runtime of the test suite then? That's the upper-bound for this work, where I 'optimized' the database migration code to take no time at all.

Thus I did something very simple: I put a breakpoint in one test, right after all database migrations ran. This means the test stopped at a point where a pristine SQLite database was present on disk. I then copied this file with cp to my home directory: this is now my golden (immutable) database. I finally modified the migration code (that all tests start with), to never apply any SQL migrations, and instead just copy (using os.Copy) the golden database file, and use that.

And this is what I saw: a 7x speed-up!

Alright, it's confirmed that this optimization is worth it!

The implementation

We could do the same as in the prototype above: assume that a human or a tool maintains a golden database file up to date, and when a test starts, it copies this golden file, and uses it as its database.

However, that requires some out-of-band process, since new SQL migrations get added every few days, and there is a risk that this golden file gets out of sync.

So I went the other way: at the start of each test, we either use the golden file if it exists, or we lazily (on demand) create it otherwise, and then start using it.

Due to Go's test framework and the fact that we use a monorepo, running Go tests from many different and unrelated projects, there is no clear entry point for all tests, where we could run our logic1

That means that each test must run this logic at the start of the test, and we'll have a contention point when checking if the golden database file already exists, which we accept.

The approach is, if I dare say so, quite elegant:

In each test, at the start, call one function to create a new database and apply all database migrations to it.

In this function, first check if the golden database file exists. If it does, simply clone2<br>it to a new, uniquely named database file, and immediately return this name, so that the test can then use it, fully isolated from the other tests.

If the golden database file does not exist, we need to apply all SQL migrations to a new database (file):

Collect all SQL migrations files and sort them

Compute a SHA256 hash of their content

Create a new database file with a random name e.g. /tmp/123456

Apply all SQL migrations to this new database file.

Rename this file to /tmp/. We now have our golden database! This is using content addressing: a test can simply try to find the file using the SHA256 hash and be assured that the file has had all SQL migrations applied. The name is a hash of the content.

Clone 2<br>this golden database to a new, uniquely named file and return that name. The calling test can now use it, and all other subsequent tests will find the golden database and use it. This is the same as step 2.

A few points are critical to make it correct:

SQL migrations are not applied to...