# Postgres template databases: build once, copy per test

> Migrating a fresh database per integration test was most of a 25-minute suite. Template databases, xUnit fixture lifetimes, and one out-of-space failure.

- Author: Simon Wimmesberger
- Published: 2026-09-17
- Canonical: https://blog.wimmesberger.dev/posts/speed-up-integration-tests-with-postgres/
- Tags: postgresql, testing, performance, dotnet

---
Our integration suite took about 25 minutes to run locally. It had 888 tests, used Testcontainers, and talked to a real PostgreSQL instance. Every test started from a clean database with the application's migrations applied. That is the right idea, and it was also where most of the 25 minutes went.

Before the first assertion, a fixture creates a database, applies the migrations, imports some data and starts the application, and the next test does all of that again. Every test needs a known starting point, and we were spending most of the run constructing the same one over and over.

What we needed was a different lifetime for the starting point, not a faster database. **Build the starting point once, copy it per test, and drop the copy the moment its owner is done.** PostgreSQL does the first two steps natively through template databases, and xUnit's fixture scopes give the third step a home. The third step turned out to matter more than the other two: moving the data directory into memory on CI ran out of space and showed that our disposable databases had never been disposed at all.

This post shows the fixtures we ended up with and the numbers along the way. It ends with a rule for what belongs in a template and what has to stay in the test.

*The post assumes you already run integration tests against a real database. The samples use xUnit v3, EF Core migrations and Testcontainers for .NET. On another test framework, the two fixture lifetimes in the third section are the part to translate; everything else is PostgreSQL.*

## CREATE DATABASE is already a copy

Every `CREATE DATABASE` in PostgreSQL copies an existing database. By default the source is `template1`, which is why a "fresh" database already contains the standard catalog. You can [name a different source](https://www.postgresql.org/docs/18/manage-ag-templatedbs.html), and the new database starts as a copy of it, schema and data included:

```sql
CREATE DATABASE test_example TEMPLATE integration_template;
```

Run that from the administrative `postgres` database, not from the template. `CREATE DATABASE` and `DROP DATABASE` cannot run inside a transaction, and PostgreSQL refuses to copy a source database while any other session is connected to it. Both facts shape the fixture below.

For us, a fresh database meant about 80 migrations across seven EF Core contexts, roughly 700 milliseconds locally. That is easy to overlook in one test and expensive when every fixture pays it. A copy of the migrated template takes a few tens of milliseconds, and the application's own migration check still runs afterward and finds nothing to do.

## The session fixture: one container, one template

The whole setup lives in one assembly fixture. xUnit creates it before the first test and disposes it after the last, so the container and the template exist exactly once per test run:

```csharp title="PostgresFixture.cs"
using Npgsql;
using Testcontainers.PostgreSql;

[assembly: AssemblyFixture(typeof(PostgresFixture))]

public sealed class PostgresFixture : IAsyncLifetime {
    private const string TemplateName = "integration_template";

    // Fixtures cannot depend on other fixtures in xUnit, so class fixtures
    // reach this one through a static. It is set before any test runs.
    public static PostgresFixture Current { get; private set; } = null!;

    private readonly PostgreSqlContainer _container =
        new PostgreSqlBuilder("postgres:18.6")
            .WithDatabase("postgres")
            .Build();

    private NpgsqlDataSource _admin = null!;

    public async ValueTask InitializeAsync() {
        await _container.StartAsync();
        _admin = NpgsqlDataSource.Create(_container.GetConnectionString());

        await ExecuteAsync($"CREATE DATABASE {TemplateName}");
        // The application's own migration runner, all seven contexts.
        var bootstrap = ConnectionStringFor(TemplateName, pooling: false);
        await MigrationBootstrap.RunAsync(bootstrap);

        Current = this;
    }

    public async ValueTask DisposeAsync() {
        await _admin.DisposeAsync();
        await _container.DisposeAsync();
    }

    public async Task<TestDatabase> CopyAsync(string source = TemplateName) {
        var name = $"test_{Guid.NewGuid():N}";
        await ExecuteAsync($"CREATE DATABASE {name} TEMPLATE {source}");
        var connectionString = ConnectionStringFor(name, pooling: true);
        return new TestDatabase(name, connectionString, this);
    }

    internal Task DropAsync(string name) =>
        ExecuteAsync($"DROP DATABASE IF EXISTS {name} WITH (FORCE)");

    private string ConnectionStringFor(string database, bool pooling) =>
        new NpgsqlConnectionStringBuilder(_container.GetConnectionString()) {
            Database = database,
            Pooling = pooling,
        }.ConnectionString;

    private async Task ExecuteAsync(string sql) {
        await using var command = _admin.CreateCommand(sql);
        await command.ExecuteNonQueryAsync();
    }
}
```

Two details in that file matter.

The administrative data source stays on `postgres`. It creates and drops databases without ever being connected to the one it is replacing. Each command runs on its own, outside any transaction, which is what those two statements require.

The template is built with `Pooling=false`. This is the first thing that bites. Disposing a `DbContext` does not end the server session, because [Npgsql returns the connection to a pool](https://www.npgsql.org/doc/basic-usage.html#pooling) for reuse. From the fixture's point of view, setup is finished. From PostgreSQL's point of view, somebody is still there, and the next copy fails with:

```text wrap
55006: source database "integration_template" is being accessed by other users
```

Npgsql hides the server's detail line unless the connection string sets `Include Error Detail=true`; with it, the error also tells you how many sessions are in the way.

Turning pooling off for the bootstrap connection means the session really closes when the bootstrap disposes it. Test connections keep pooling on. We tried disabling it more broadly once, and a parallel suite that opens and closes connections constantly can exhaust the client's ephemeral ports. The one-off template build and the running application need different treatment.

## Two lifetimes: a copy per test, a copy per class

A copy is a small handle whose disposal drops the database:

```csharp title="TestDatabase.cs"
public sealed class TestDatabase(
    string name,
    string connectionString,
    PostgresFixture owner) : IAsyncDisposable {
    public string Name { get; } = name;
    public string ConnectionString { get; } = connectionString;

    public ValueTask DisposeAsync() => new(owner.DropAsync(Name));
}
```

Who owns the handle decides how long the database lives, and xUnit gives you two natural owners.

The test class instance owns a per-test database. xUnit constructs a new instance of the class for every test, so a copy taken in `InitializeAsync` and dropped in `DisposeAsync` exists for exactly one test:

```csharp title="OrderRepositoryTests.cs" {5-9}
public sealed class OrderRepositoryTests(PostgresFixture postgres)
    : IAsyncLifetime {
    private TestDatabase _db = null!;

    public async ValueTask InitializeAsync() {
        _db = await postgres.CopyAsync();
    }

    public ValueTask DisposeAsync() => _db.DisposeAsync();

    [Fact]
    public async Task Stores_an_order() {
        await using var context = new OrdersContext(_db.ConnectionString);
        // ...
    }
}
```

A class fixture owns a per-class database. Some tests share a running application: starting the host, its background workers and its connection pools is itself expensive, and several tests can exercise one instance. xUnit creates an `IClassFixture<T>` once before the first test in the class and disposes it after the last. The database has to live as long as the application on top of it, and it has to be dropped after the application stops, not before:

```csharp title="AppFixture.cs" {14-15}
public sealed class AppFixture : IAsyncLifetime {
    private TestDatabase _db = null!;

    // Wraps a WebApplicationFactory<Program> and its data source.
    public TestApp App { get; private set; } = null!;

    public async ValueTask InitializeAsync() {
        _db = await PostgresFixture.Current.CopyAsync();
        App = new TestApp(_db.ConnectionString);
        await App.StartAsync();
    }

    public async ValueTask DisposeAsync() {
        await App.DisposeAsync();   // stops hosted services, disposes the pools
        await _db.DisposeAsync();   // only then drop the database
    }
}

public sealed class CheckoutApiTests(AppFixture fixture)
    : IClassFixture<AppFixture> {
    [Fact]
    public async Task Rejects_an_empty_cart() {
        using var client = fixture.App.CreateClient();
        // ...
    }
}
```

The class fixture reads the session fixture from the static property because xUnit does not inject fixtures into other fixtures. Assembly fixtures are initialized before anything else runs, so the static is always set by then.

The order in the disposal is the part people get wrong. `DROP DATABASE ... WITH (FORCE)` [terminates the remaining sessions](https://www.postgresql.org/docs/18/sql-dropdatabase.html), but it cannot stop a hosted service that is still running from reconnecting a moment later. Stop the application, let it dispose its data source, then drop.

## Classes run in parallel, tests inside a class don't

With the database state separated per class, the classes no longer have a reason to wait for each other. xUnit's default parallel mode runs test collections concurrently and the tests within one collection serially, and every class is its own collection unless you say otherwise. That matches the two lifetimes exactly: a per-class application sees its tests one at a time, while other classes run alongside on their own databases.

The only knob we set was the worker count:

```json title="xunit.runner.json"
{
  "$schema": "https://xunit.net/schema/current/xunit.runner.schema.json",
  "maxParallelThreads": 7
}
```

Separate databases still share one server, so there is a limit. Copies compete for the same CPU and memory inside the container, and past a point more workers only add contention. The early local runs, all on ordinary disk storage:

| Setup, 888-test suite | Wall time |
| --- | ---: |
| Original serial fixtures | ~25 min |
| Templates and cheaper application setup, still serial | 12 min 06 s |
| Concurrent classes, 4 workers | 3 min 03 s |
| Concurrent classes, 7 workers | 2 min 18 s |

The roughly elevenfold improvement belongs to the combination. The serial row shows how much the templates and cheaper boots gained on their own before any parallelism, and parallelism took it from there. One unrelated change landed in the same round: the application launched a browser on every boot, and test boots stopped doing that.

On hosted CI, wall time is also a line on the bill. A private repository on GitHub Actions draws from a monthly quota of minutes that depends on the plan, and everything beyond it is [billed per minute](https://docs.github.com/en/billing/managing-billing-for-your-products/about-billing-for-github-actions): at the time of writing $0.006 for a standard 2-core Linux runner, $0.010 on Windows and $0.062 on macOS. (Public repositories and self-hosted runners are free.)

Take the local numbers as a stand-in and assume a team on the Team plan with its 3,000 included minutes, running the suite on every push to every pull request, say 20 runs per working day. The 25-minute suite burns 10,500 minutes a month, 7,500 of them beyond the quota, which is $45 a month or $540 a year on Linux runners. The faster suite needs about 970 minutes and never leaves the quota. More pushes or Windows runners widen the gap, and at some point that difference decides whether the integration tests run on pull requests at all or get demoted to a nightly job.

The improvement was useful beyond the stopwatch and the bill. A fixture could ask for a clean, current database without carrying its own collection of migration calls. Preparing the starting point had become one piece of shared test infrastructure instead of something every fixture reimplemented.

## Imports are setup too

Once migrations were cheap, the fixture imports were much easier to notice. Many tests needed a populated application, so they copied the empty template and then ran the same import into it. We had removed one repeated setup step and left the next one in place.

A template can contain data as well as schema. The session fixture builds a populated template on first request and every later request for the same key gets a copy of the result:

```csharp title="PostgresFixture.cs (data templates)"
private readonly ConcurrentDictionary<string, Lazy<Task<string>>>
    _templates = new();

public Task<string> GetTemplateAsync(string key, Func<string, Task> seed) =>
    _templates.GetOrAdd(key, k => new Lazy<Task<string>>(async () => {
        var name = $"template_{k}";
        var sql = $"CREATE DATABASE {name} TEMPLATE {TemplateName}";
        await ExecuteAsync(sql);
        await seed(ConnectionStringFor(name, pooling: false));
        return name;
    })).Value;
```

The `Lazy` matters: `GetOrAdd` may run its factory more than once under contention, and two concurrent attempts to create the same database would fail on the second. The seed runs against a non-pooled connection string for the same reason the migrations do, and it has to dispose whatever it opens before the template is copied. A class fixture then asks for its populated template and copies that instead of the empty one:

```csharp title="AppFixture.cs (populated)" {2-4}
public async ValueTask InitializeAsync() {
    var template = await PostgresFixture.Current.GetTemplateAsync("orders_full",
        cs => Importer.RunAsync(cs, ImportMode.Full));
    _db = await PostgresFixture.Current.CopyAsync(template);
    App = new TestApp(_db.ConnectionString);
    await App.StartAsync();
}
```

Before that change, 131 tests had been running an import. Afterward, each distinct `(fixture, import mode)` pair ran it once per session. By then the suite had grown to 1,278 test cases, and a local comparison went from about 2 minutes 17 seconds to 1 minute 55 seconds. Those are later runs of a larger suite and don't belong in the earlier table.

Before sharing an import through a template, check what it leaves behind. Ours wrote its durable output to PostgreSQL only, and the application could rebuild its in-memory state from the copied rows, so the database was a complete starting point. An import that also creates files or notifies an already-running service leaves part of its result outside the copy, and a template would silently miss it.

Two kinds of test keep their expensive step. Tests of the importer still run the import, because the import is what they verify. Migration tests still execute the migration under test; the template can help them too, stopped at the preceding schema version, so each test inserts its legacy rows and runs only the forward migration itself.

## Durability off, data directory in memory

Templates leave PostgreSQL with real work: it still copies files and handles every write the tests make. Under seven workers, our CI runner's disk became the bottleneck for that remaining work. Two changes to the container address that. Both trade durability for speed, which is the right trade for a database that is thrown away after the run.

The first is a set of server flags. `fsync`, `synchronous_commit` and `full_page_writes` are [separate settings](https://www.postgresql.org/docs/18/non-durability.html) that all protect the data directory against a crash: the first forces writes to reach the disk, the second makes a commit wait for its WAL record to do so, and the third guards against torn pages. With all three off, a crash can corrupt the cluster, and we don't care. Tests of crash recovery would need a different container.

The second is where the files live. The data only has to survive one test session, so on CI the data directory moves into memory:

```csharp title="PostgresFixture.cs (container setup)" {4-6,8}
private static PostgreSqlContainer BuildContainer(bool useTmpfs) {
    var builder = new PostgreSqlBuilder("postgres:18.6")
        .WithDatabase("postgres")
        .WithCommand("-c", "fsync=off",
                     "-c", "synchronous_commit=off",
                     "-c", "full_page_writes=off");
    if (useTmpfs) {
        builder = builder.WithTmpfsMount("/var/lib/postgresql");
    }
    return builder.Build();
}
```

The `_container` field in the session fixture becomes a call to this method, with the flag read from an environment variable. The image's entrypoint prepends `postgres` when the first argument starts with a dash, so the command is only the flags. The tmpfs switch is on in CI and opt-in locally.

This is Testcontainers for .NET 4.14 with the PostgreSQL 18 image, which keeps its data directory under `/var/lib/postgresql`. [The 17-and-earlier images use `/var/lib/postgresql/data`](https://hub.docker.com/_/postgres#pgdata), so the mount target depends on the major version you run.

## The 600 databases nobody dropped

We don't have an isolated before/after figure for tmpfs on its own, since it landed together with other changes. What we measured very precisely was how much space it needs.

Our first tmpfs run ended with `No space left on device`. At that point the only cleanup was the reset-by-name logic from the pitfalls section: a database was dropped only when its name was about to be reused. Isolated tests generated unique names, so most databases were created, used, and then left sitting there until the whole session ended.

On disk they had room to accumulate. On the local tmpfs mount they hit a limit of about 3.9 GB, and a full run had been growing the cluster to roughly 6.3 GB with around 600 databases left behind. Moving the files into memory made the lifetime mistake impossible to miss.

Dropping databases when their owning class fixture finished brought the peak down to about 2.9 GB and roughly 220 live databases. Then a later run exposed a second assumption. Seven workers doesn't mean seven classes are alive. xUnit hands the worker threads to whichever test is next, so it can run one test from a class, move on to other classes, and come back much later. A class whose tests are interleaved with the rest stays open for most of the run, and every database it owns stays with it.

That is why the per-test lifetime from the third section exists. A fixture that owns nothing beyond a single test releases its database immediately, in the test class's `DisposeAsync`. Only fixtures that keep an application alive across tests get the longer lifetime. Once both lifetimes were in place, memory tracked the tests actually executing instead of the tests that had merely started.

[tmpfs has a size limit and counts against the container's memory budget](https://docs.docker.com/engine/storage/tmpfs/), and its pages can still be swapped. The number to measure is the peak across a whole run, templates and retained copies included. One small test tells you nothing about that, and neither did our developer machines, which had fast SSDs and a smaller Docker VM, so tmpfs stayed off there.

## The pitfalls that actually happen

### Settings and grants are not copied

`CREATE DATABASE ... TEMPLATE` copies files. Database-level configuration set with `ALTER DATABASE` and database-level `GRANT`s [are not part of that](https://www.postgresql.org/docs/18/sql-createdatabase.html). If your application relies on either, apply them to each copy after creating it, or move them to the role instead.

### Copies inherit the template's timestamps

Every copy carries the rows exactly as the template had them, including `created_at` values from whenever the template was built. A test about "data created in the last five minutes" passes for the first minutes of the run and fails afterward. Such tests arrange their own rows.

### Clear the pool that owns the connections

Most copies get a unique name and are dropped once, so there is nothing to clear. The exception is a fixture that reuses a fixed database name across tests or runs, for example so a developer can inspect the database afterward. Resetting it means dropping the database and creating it again from the template, and the drop runs into the same wall as the template copy did, this time because of the test's own pool:

```text wrap
55006: database "integration_dev" is being accessed by other users
```

`WITH (FORCE)` gets the drop through by terminating those sessions on the server. The client never hears about that. Npgsql keeps one pool per `NpgsqlDataSource`, and the connection it took back after the previous test is still sitting in that pool, ready for reuse. The next test rents it and the first query dies on a socket the server has already closed:

```text wrap
Npgsql.NpgsqlException: Exception while reading from stream
 ---> System.IO.IOException: Unable to read data from the transport connection: ...
```

The inner message depends on the operating system, the outer one does not. The obvious fix, `NpgsqlConnection.ClearPool(new NpgsqlConnection(connectionString))`, does nothing here. A connection created from a bare connection string belongs to a pool Npgsql keeps per connection string. A data source that you or EF Core created with `NpgsqlDataSource.Create` has a pool of its own, and clearing the first leaves the second untouched, with the dead connection still first in line. Clear [the data source itself](https://www.npgsql.org/doc/api/Npgsql.NpgsqlDataSource.html#Npgsql_NpgsqlDataSource_Clear) before the drop, and the next rent opens a fresh physical connection:

```csharp {1}
dataSource.Clear();   // the instance EF Core is actually using
await ExecuteAsync($"DROP DATABASE IF EXISTS {name} WITH (FORCE)");
await ExecuteAsync($"CREATE DATABASE {name} TEMPLATE {TemplateName}");
```

### A failed test needs the same teardown

The drop lives in `DisposeAsync`, which xUnit runs whether the test passed or failed. Anything that only happens at the end of a happy path, such as a cleanup call at the bottom of the test method, leaks a database on every failure. Under tmpfs those leaks are what fills the mount.

## What belongs in a template

| The step is... | Put it |
| --- | --- |
| Applied by every test and identical each time (migrations) | In the schema template, once per session |
| Shared by many tests with a small number of variants (imports) | In a data template per variant, built on first request |
| The operation the test verifies (the importer, the migration) | In the test; migration tests start from a template stopped one version earlier |
| Producing output outside the database (files, other services) | In the test, or make it produce database rows only |
| Dependent on the current time | In the test, never in a template |

## Build once, copy per test, drop on dispose

We ended up with the same PostgreSQL behavior to test against and a much shorter wait. The migrations and the imports still run, but once per session, and their results last exactly as long as the suite needs them.

The habit worth keeping is to look closely at how tests reach their starting state. Ours hid migrations repeated hundreds of times and imports whose results could have been reused. Then the tmpfs failure showed databases living long after their tests had finished. All of it was ordinary fixture code, easy to overlook beside the application being tested. Build the starting point once, copy it per test, and drop the copy the moment its owner is done.