Parallel Database Tests in Postgres with Template Databases

Is database setup really what makes your test suite slow?

Before restructuring your test infrastructure, find out where the time goes. A slow test suite in a monolith can come from several places, and template databases only help with one of them: the cost of producing a clean, migrated, seeded database for each worker or test.

Split wall-clock time into four buckets:

• Migrations: applying the schema from scratch, usually once per run but sometimes per worker.
• Seeding: loading reference data and fixtures the tests expect to exist.
• Per-test cleanup: truncating tables, deleting rows, or resetting sequences between tests.
• Test bodies: the code under test, including the queries it issues.

You can collect this data without new tooling. Most test frameworks have suite-level and per-test setup and teardown hooks. Record a monotonic timestamp at the start and end of each hook, tag it with its bucket, and append it to a file or log. Sum per bucket after one full run. Do the same for the time between per-test setup and teardown to get test-body time.

This approach is for backend and platform engineers whose numbers show setup and cleanup taking a large share of the run. That pattern is common when every test truncates dozens of tables or when parallel workers each rerun migrations. Cloning a pre-built database replaces that repeated work with a fast file-level copy.

Skip it if most of the time is in test bodies. Faster setup won't fix slow queries or heavy application logic. Skip it too if the suite already finishes quickly enough that nobody waits on it.

Copy# conftest.py: time the existing hooks without changing what they do
import time
from collections import defaultdict
from contextlib import contextmanager

import pytest

from myapp.testing import run_migrations, load_seed_data, truncate_all_tables  # your existing helpers

TIMINGS = defaultdict(float)  # bucket -> seconds, per process (each xdist worker reports its own)

@contextmanager
def timed(bucket):
    start = time.perf_counter()  # monotonic, unaffected by wall-clock changes
    try:
        yield
    finally:
        TIMINGS[bucket] += time.perf_counter() - start

@pytest.fixture(scope="session", autouse=True)
def database():
    with timed("migrations"):
        run_migrations()
    with timed("seeding"):
        load_seed_data()
    yield

@pytest.fixture(autouse=True)
def clean_tables(database):
    yield  # the test runs here
    with timed("cleanup"):
        truncate_all_tables()

@pytest.hookimpl(hookwrapper=True)
def pytest_runtest_call(item):
    with timed("test_bodies"):  # only the test function, excluding fixture setup and teardown
        yield

def pytest_sessionfinish(session, exitstatus):
    total = sum(TIMINGS.values()) or 1
    for bucket, seconds in sorted(TIMINGS.items(), key=lambda kv: -kv[1]):
        print(f"{bucket:12} {seconds:8.2f}s {100 * seconds / total:5.1f}%")

A pytest conftest.py that wraps existing migration, seeding, and cleanup fixtures and the test call itself in a timer, then prints how the run's time splits across the four buckets.

What is a Postgres template database and how does cloning work?

A Postgres template database is an ordinary database that CREATE DATABASE uses as its source. When you run CREATE DATABASE test_7 TEMPLATE app_template, Postgres copies the source's storage directly instead of re-executing SQL. Tables, indexes, sequences, functions, and seed rows arrive already built. Replaying migrations has to parse and plan every DDL statement, build each index, and insert seed data row by row. A storage-level copy skips all of that, which is why it is usually much faster.

You already rely on this mechanism. Every CREATE DATABASE without a TEMPLATE clause clones template1, so anything you install there appears in every new database. template0 is a pristine fallback that you are not meant to modify. Postgres keeps it connection-proof so it stays clean.

The no-connections rule

Postgres refuses to clone a template while any other session is connected to it, because a concurrent writer could leave the copy inconsistent. In practice this means a stray psql session, an IDE, or a leftover migration process will make every worker's CREATE DATABASE fail. To prevent this, mark the template the same way template0 is marked. Use ALTER DATABASE app_template IS_TEMPLATE true to set datistemplate, and ALTER DATABASE app_template ALLOW_CONNECTIONS false to set datallowconn to false. If you need to rebuild the template later, re-enable connections first.

Why template size matters

The copy is cheaper than migrations, but it still costs I/O proportional to the template's on-disk size. That cost is paid once for every database you create. Keep the template to schema plus the minimal reference data every test needs, and have each test create its own fixtures.

Copy-- Run from a maintenance connection (e.g. to the 'postgres' database),
-- never from inside app_template itself.

-- 1. Kick out any leftover sessions (IDE, psql, migration runner)
--    so the template is idle before we lock it down.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'app_template'
  AND pid <> pg_backend_pid();

-- 2. Mark it as a template and refuse new connections, mirroring template0.
ALTER DATABASE app_template IS_TEMPLATE true;
ALTER DATABASE app_template ALLOW_CONNECTIONS false;

-- 3. Confirm the flags and see how much each clone will have to copy.
SELECT datname,
       datistemplate,
       datallowconn,
       pg_size_pretty(pg_database_size(datname)) AS clone_size
FROM pg_database
WHERE datname IN ('template0', 'template1', 'app_template');

-- 4. Each test worker clones the template instead of running migrations.
CREATE DATABASE test_7 TEMPLATE app_template;

-- Later, to rebuild the template: allow connections again, apply
-- migrations, then repeat steps 1-2. To drop it instead, clear the
-- template flag first, because Postgres will not drop a template database.
-- ALTER DATABASE app_template ALLOW_CONNECTIONS true;
-- ALTER DATABASE app_template IS_TEMPLATE false;
-- DROP DATABASE app_template;

Locking down app_template with IS_TEMPLATE and ALLOW_CONNECTIONS, checking its flags and on-disk size against template0 and template1, and cloning it for a test worker.

How do you implement parallel database tests in Postgres with one template per run?

Treat the template as a build artifact. At the start of each run, hash the contents of every migration file plus the seed data, and store that fingerprint on the template itself, for example with COMMENT ON DATABASE. If the stored value matches, skip setup entirely. If it differs, drop the template, recreate it, run migrations and seeds, and write the new fingerprint. A fingerprint only covers what you feed into it. Extensions installed outside migrations, database-level settings applied with ALTER DATABASE, and seeds whose output depends on the environment or the clock can all change while the hash stays the same. Include them as inputs, or the template will go stale without any error.

CREATE DATABASE ... TEMPLATE fails if any other session is connected to the template. Before cloning, close your setup connection and terminate any remaining sessions with pg_terminate_backend. A connection pool that stayed open, a database IDE, or a setup step that failed without closing its connection will cause clone failures that look random, because they depend on timing.

Give each worker its own database, named from a fixed prefix plus the worker index, such as app_test_w3. At startup, each worker runs DROP DATABASE IF EXISTS on its own name, clones from the template, and overrides its connection URL to point at that database. Because the names are deterministic, a crashed run leaves behind a known, bounded set of databases, and the next run removes them. In CI, cache the template on a persistent database service or rebuild it once per job.

If your ORM normally creates and destroys the test database for you, you will have to turn that off and maintain this lifecycle code yourself.

Copy#!/usr/bin/env bash
# Run once per test run, before workers start. Placeholder credentials only.
set -euo pipefail
export PGHOST=localhost PGPORT=5432 PGUSER=app_test PGPASSWORD=example-password
ADMIN="psql -v ON_ERROR_STOP=1 -d postgres -tAq"   # never connect to the template itself
TEMPLATE=app_test_template
WORKERS=${WORKERS:-4}

# Fingerprint every input that shapes the template: migrations, seeds,
# extensions installed outside migrations, and env values the seeds read.
FP=$( { find db/migrations db/seeds db/extensions.sql db/database_settings.sql -type f | sort | xargs sha256sum
        echo "SEED_LOCALE=${SEED_LOCALE:-}"; } | sha256sum | cut -d' ' -f1 )

# Kick out lingering sessions (pools, IDEs, failed steps) that would block DROP/clone.
terminate() {
  $ADMIN -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
             WHERE datname = '$TEMPLATE' AND pid <> pg_backend_pid()" >/dev/null
}

STORED=$($ADMIN -c "SELECT shobj_description(oid, 'pg_database') FROM pg_database WHERE datname = '$TEMPLATE'")
if [ "$STORED" != "$FP" ]; then
  terminate
  $ADMIN -c "DROP DATABASE IF EXISTS $TEMPLATE"
  $ADMIN -c "CREATE DATABASE $TEMPLATE"
  for f in db/extensions.sql db/database_settings.sql db/migrations/*.sql db/seeds/*.sql; do
    psql -v ON_ERROR_STOP=1 -q -d "$TEMPLATE" -f "$f"   # each psql exits, closing its session
  done
  $ADMIN -c "COMMENT ON DATABASE $TEMPLATE IS '$FP'"   # store fingerprint on the artifact
fi

terminate   # cloning fails if anything else is connected to the template

# Deterministic names: a crashed run leaves only app_test_w1..wN, removed here next time.
for i in $(seq 1 "$WORKERS"); do
  DB="app_test_w$i"
  $ADMIN -c "DROP DATABASE IF EXISTS $DB"
  $ADMIN -c "CREATE DATABASE $DB TEMPLATE $TEMPLATE"
  echo "worker $i -> DATABASE_URL=postgres://app_test@localhost:5432/$DB"
done

A bash setup script that rebuilds the template only when the fingerprint of migrations, seeds, extensions, database-level settings and seed-relevant environment changes, terminates stray sessions on the template, and clones one deterministically named database per worker.

How should you isolate individual tests inside each worker?

Within a worker's database, wrap each test in a transaction and roll it back at the end. The test fixture opens one connection, begins a transaction, and hands that connection to the application code under test. When code under test opens its own transactions, map them to savepoints. Rollback undoes everything, including sequence-free row changes, with almost no I/O, which is why it should be the default.

Rollback isolation never reaches COMMIT, so some behavior is never exercised:

• Deferred constraints are checked only at commit. A foreign key violation can pass silently. Running SET CONSTRAINTS ALL IMMEDIATE before the rollback forces those checks to run.
• Deferred constraint triggers fire at commit time, so they never fire in a rolled-back test.
• Advisory locks taken with transaction scope are held by the single shared connection, so contention between callers never appears. Session-scoped locks also leak into the next test unless you release them.
• Multi-connection code, such as background workers, connection pools, or LISTEN/NOTIFY, cannot see uncommitted rows. These tests fail or hang.

To opt out, tag the test, for example with a marker or attribute that your fixture reads. A tagged test runs on a normal autocommit connection. Afterward the fixture truncates all application tables with RESTART IDENTITY CASCADE, or drops the database and re-clones it from the template.

Copyimport psycopg
import pytest
from psycopg import sql

# Register the marker in pytest.ini:  markers = commits: test needs real COMMITs

def truncate_app_tables(conn):
    rows = conn.execute(
        "SELECT schemaname, tablename FROM pg_tables "
        "WHERE schemaname = 'public' AND tablename <> 'schema_migrations'"
    ).fetchall()
    if rows:
        tables = sql.SQL(", ").join(sql.Identifier(s, t) for s, t in rows)
        conn.execute(sql.SQL("TRUNCATE {} RESTART IDENTITY CASCADE").format(tables))

@pytest.fixture
def db(request, worker_dsn):  # worker_dsn points at this worker's cloned database
    if request.node.get_closest_marker("commits"):
        # Opt-out path: real autocommit connection, so commit-time checks,
        # triggers, and other connections behave as they do in production.
        conn = psycopg.connect(worker_dsn, autocommit=True)
        try:
            yield conn
        finally:
            truncate_app_tables(conn)
            conn.close()
        return

    # Default path: one connection, one outer transaction per test.
    # Code that calls conn.transaction() inside it gets a SAVEPOINT.
    conn = psycopg.connect(worker_dsn)  # autocommit=False
    try:
        yield conn
        # Surface deferred FK/constraint violations that COMMIT would raise.
        conn.execute("SET CONSTRAINTS ALL IMMEDIATE")
    finally:
        conn.rollback()  # discard all row changes and release xact advisory locks
        conn.close()     # closing also drops any session-level advisory locks

# @pytest.mark.commits
# def test_worker_sees_committed_job(db): ...

A pytest fixture using psycopg 3 that wraps each test in a rolled-back transaction, forces deferred constraint checks before rollback, and lets tests marked commits run on an autocommit connection followed by a table truncation.

What are the alternatives to template databases for test isolation?

Each alternative trades speed, isolation and setup effort differently.

Shared database with truncation breaks once workers run concurrently. One worker's TRUNCATE wipes rows another worker is still reading, which produces intermittent failures that are hard to reproduce. It only works if the suite runs serially.

Locking or serializing keeps tests correct but gives up the parallelism you wanted. Use it for the few tests that touch global state, not as the default.

Running migrations per worker gives the same isolation as cloning. Its cost grows with the length of your migration history. With a small schema that migrates in a second or two, it is simpler and good enough.

A container per worker isolates server-level state too, such as roles, extensions and configuration. That makes it the right choice when tests change those things. You pay for it with startup time and orchestration work.

Framework-managed test databases are the right choice when you target several database engines. The template approach depends on Postgres's CREATE DATABASE ... TEMPLATE, so it does not carry over to other engines.

How many workers can you run, and how do you tune a throwaway Postgres for speed?

Each worker holds its own database and connections, so four limits cap the worker count:

• CPU. Test workers and Postgres backends share the same cores. Past roughly one worker per core, they compete for CPU and every query takes longer.
• Connections. Workers × pool size per worker must stay under max_connections, with headroom for the cloning connection. Set each worker's pool to 1–2 connections. A test touches one database at a time, so a larger pool reserves connections that sit idle.
• Disk. Every clone is a full copy of the template. Ten workers mean ten copies on disk.
• I/O during cloning. CREATE DATABASE ... TEMPLATE copies files. If every worker clones at startup, those copies hit the disk simultaneously.

Adding workers beyond these limits makes the suite slower, not faster. Cloning queues up behind disk I/O, and tests time out waiting for CPU.

Illustrative estimate (assumed inputs, not measured): migrations and seed take 40 s, done once to build the template. Each clone takes 0.5 s. Tests total 1,200 s when run serially. With 8 workers, the run takes about 40 + 0.5 + 1,200 / 8 ≈ 190 s. Running migrations per test instead would cost far more.

On a disposable instance you can relax durability:

• Set fsync = off, synchronous_commit = off, and full_page_writes = off.
• Optionally, place the data directory on a RAM-backed filesystem such as tmpfs.

These settings are acceptable only because the data is thrown away after the run. A crash can corrupt the cluster. The tests will also not exercise crash recovery or any behavior that depends on commit latency, so catch those problems somewhere else.

Why do parallel tests still fail with isolated databases, and what does the switch cost?

Isolated databases remove one shared resource, but a test process touches others. Each row is state that workers can still collide on:

Expect the first parallel runs to produce flaky tests. These tests are not new bugs. They passed before only because of execution order. Fix each one instead of adding retries. A retry hides the dependency and turns a deterministic failure into an intermittent one, and intermittent failures cost far more to diagnose.

The cost of the switch has two parts. The first is a one-time migration: wiring per-worker clones and fixing the order-dependent tests the switch exposes. The second is ongoing maintenance of the template fingerprint and the cleanup of orphaned databases. Weigh that against recurring savings:

Illustrative estimate (assumed numbers, not a benchmark): monthly hours saved = (serial run minutes − parallel run minutes) × runs per month ÷ 60

If developers wait on CI, count their waiting time as well as the runner time.

When to avoid it

Template cloning is a poor fit in a few specific situations. Check for these before you build the harness.

The database isn't the bottleneck. If profiling shows most time going to application boot, asset compilation, HTTP calls to sandboxes, or a few slow end-to-end tests, faster database provisioning won't help much. A warning sign is a suite where the slowest tests barely touch Postgres. Fix those tests first, because parallel workers will still wait on the longest one.

State lives outside Postgres. Separate databases isolate only what is inside each database. If tests share a Redis instance, a fixed temp directory, a hardcoded port, or one object-storage bucket, workers will still interfere with each other. You will see the same intermittent failures as before, now harder to reproduce. Partition that state per worker before adding databases.

Tests touch cluster-wide objects. Roles, tablespaces, and server settings belong to the Postgres cluster, not to a single database. Tests that create users, grant privileges, or change configuration with ALTER SYSTEM will collide across workers no matter how many clones you make.

You can't create databases. Some managed or shared environments don't grant CREATE DATABASE, or they make creating a database slow. In that case, transaction rollback or schema-per-worker is the practical option.

You're testing migrations themselves. A pre-migrated template skips the migration path entirely. Keep a separate serial job that runs migrations from an empty database.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to parallel database tests postgres, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.