Why do shared-database integration tests get slow and flaky under parallelism?
Most monolith suites start with one test database: migrate it once, then reset state between tests. That works while tests run serially. Once you split the suite across parallel workers to cut CI time, each common reset strategy either slows down or starts producing flaky failures.
Truncation between tests. TRUNCATE takes an ACCESS EXCLUSIVE lock on every table it touches. If workers share one database, they block each other on those locks, or one worker wipes rows another worker's test is still reading. The cost also grows with schema size, because a monolith with hundreds of tables pays for every truncate, sequence reset, and cascade on every test.
Per-test transaction rollback. Wrapping each test in a transaction and rolling it back is cheap, but the test only works if all of its work happens on one connection. It breaks when application code commits explicitly, when a second connection can't see the uncommitted rows, or when a background job runs outside the test's transaction.
Per-worker migrations. Giving each worker its own database fixes sharing, but each worker now replays the full migration history before running a single test. That setup cost is multiplied by the number of workers.
| Strategy | Safe in parallel on one DB | Main cost | Where it fails |
|---|---|---|---|
| Truncation | No | Grows with table count | Lock contention, cross-worker data loss |
| Transaction rollback | Partly | Low | Commits, extra connections, background jobs |
| Per-worker migrations | Yes | Full migration run per worker | Slow startup, grows with worker count |
Stronger isolation usually means more setup work for each test or worker, and that setup work is exactly what you were trying to remove from CI. This is why isolation and CI speed pull against each other.
What is a Postgres template database and how does cloning work?
Every CREATE DATABASE in Postgres already copies another database. By default the source is template1. When you write CREATE DATABASE app_test_3 TEMPLATE app_template, Postgres copies the source's files instead of replaying DDL. Your schema, extensions, seed rows, indexes, and sequences all arrive in their current state. No migration runs.
Because this is a physical copy, cost scales with the template's on-disk size, not with the number of migrations that produced it. A schema built from 400 migrations clones as fast as one built from 4, as long as the data volume is similar. Large seed tables, bloated indexes, or leftover dead rows make every clone slower. That is the reason to keep the template lean and VACUUM it before freezing it.
Postgres refuses to clone a database while any other session is connected to it. A copy taken during writes would not be consistent. One stray psql session, or a migration tool that never closed its pool, makes every worker's CREATE DATABASE fail.
Two database-level settings guard against this:
IS_TEMPLATE truemarks the database as a template. Any role withCREATEDBcan then clone it, and Postgres will not let anyone drop it by accident.ALLOW_CONNECTIONS falseblocks new connections, so nothing can attach and block clones. It does not end sessions that are already open, so close those before you set it.
migrations run once
│
▼
┌───────────────────┐
│ app_template │ IS_TEMPLATE true
│ │ ALLOW_CONNECTIONS false
└─────────┬─────────┘
CREATE DATABASE … TEMPLATE app_template
┌─────────────┬───┴─────────┬─────────────┐
▼ ▼ ▼ ▼
app_test_w1 app_test_w2 app_test_w3 app_test_w4
worker 1 worker 2 worker 3 worker 4
-- Run with psql as a role that has CREATEDB.
-- 1. After migrations and seeding, compact the template while still connected to it.
\connect app_template
VACUUM (ANALYZE);
-- 2. Switch to a different database. You cannot alter or clone the template from inside it.
\connect postgres
-- End any lingering sessions, such as a migration tool's pool or a stray psql.
-- ALLOW_CONNECTIONS false only blocks new connections.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'app_template'
AND pid <> pg_backend_pid();
-- 3. Freeze the template.
-- IS_TEMPLATE lets CREATEDB roles clone it and makes DROP DATABASE fail.
-- ALLOW_CONNECTIONS false prevents new sessions from attaching and blocking clones.
ALTER DATABASE app_template WITH IS_TEMPLATE true ALLOW_CONNECTIONS false;
-- 4. Each test worker clones its own copy. No migrations are replayed.
CREATE DATABASE app_test_1 TEMPLATE app_template;
CREATE DATABASE app_test_2 TEMPLATE app_template;
-- 5. Tear down a worker's clone when its run finishes.
DROP DATABASE IF EXISTS app_test_1;
-- To rebuild the template with new migrations, unfreeze it first.
-- ALTER DATABASE app_template WITH IS_TEMPLATE false ALLOW_CONNECTIONS true;
A psql script that vacuums a migrated template, closes its open sessions, freezes it with IS_TEMPLATE and ALLOW_CONNECTIONS false, and clones one database per test worker.
How does template-cloned testing work end to end?
The approach has four parts. Each one is covered in detail in the sections that follow.
- Build once per schema version. A setup step creates a database, runs every migration, loads baseline seed data, vacuums it, and freezes it with
IS_TEMPLATE trueandALLOW_CONNECTIONS false. The template's name includes a hash of the migrations, seeds, extensions, and server settings. If a template with that name already exists, the build is skipped. - Clone per worker. When each test worker starts, it runs
CREATE DATABASE <clone> TEMPLATE <template>. Postgres copies the template's files, so the worker receives a fully migrated, seeded database without replaying any DDL. Clone creation is serialized or retried, because Postgres rejects a clone while another session is connected to the template. - Point the application at the clone. The worker writes the clone's name into the connection setting the application reads, such as
DATABASE_URL, before any pool or ORM initializes. From then on, every connection that worker opens goes to its private database. That includes connections opened by application code, background jobs, and listeners. - Clean up at two levels. Between tests inside a worker, transaction rollback or truncation resets data. When the worker finishes, it drops its clone, and a sweep step removes clones that crashed runs left behind.
Workers no longer contend for locks or rows, because each has its own database. Startup no longer scales with migration history, because cloning copies files instead of replaying migrations.
How do you build and cache the template database once per schema version?
Build the template in one setup step, before any worker starts. Create an empty database, run every migration against it, and load the baseline seed data that all tests assume exists, such as reference tables, feature flags, and a default tenant. Then run ALTER DATABASE to set IS_TEMPLATE true and ALLOW_CONNECTIONS false. Blocking connections matters because CREATE DATABASE … TEMPLATE fails if any other session is connected to the source. A stray connection from a leftover migration process or an IDE would break every clone.
Do the build under a temporary name and rename it to the final name only after it succeeds. A crashed or interrupted build then never leaves a half-migrated database where a later run will find and clone it.
To avoid rebuilding on every run, put a content hash in the template name, for example app_template_<hash>. Hash every input that shapes the resulting database:
- The migration files, sorted by path, with both names and contents included
- Seed scripts and any fixture files they read
- The list of required extensions
- The Postgres server major version and locale or collation settings, if your tests depend on them
If a database with that name already exists, skip the build and clone it.
The hash has to cover all of these inputs. If a seed script loads a CSV that isn't hashed, or a migration branches on an environment variable, you can change behavior without changing the name. The suite then quietly reuses a stale template, and tests pass or fail against a schema you no longer ship.
To clean up, list databases that match the prefix but not the current hash. For each one, set IS_TEMPLATE false and then drop it. Postgres refuses to drop a database that is still marked as a template.
#!/usr/bin/env bash
set -euo pipefail
ADMIN="psql -X -v ON_ERROR_STOP=1 -At -d postgres"
RUN="psql -X -v ON_ERROR_STOP=1 -q"
EXTENSIONS="pgcrypto citext" # required extensions, also part of the hash
HASH=$(
{ find migrations seeds -type f | LC_ALL=C sort | while read -r f; do echo "== $f"; cat "$f"; done
echo "extensions: $EXTENSIONS"
$ADMIN -c "SELECT current_setting('server_version_num')::int / 10000, datcollate, datctype
FROM pg_database WHERE datname = 'template1'"
} | sha256sum | cut -c1-16)
TEMPLATE="app_template_${HASH}"
BUILD="${TEMPLATE}_build"
if [ -z "$($ADMIN -c "SELECT 1 FROM pg_database WHERE datname = '$TEMPLATE'")" ]; then
# Build under a temporary name so a crashed build is never found under the final name.
$ADMIN -c "DROP DATABASE IF EXISTS $BUILD"
$ADMIN -c "CREATE DATABASE $BUILD"
for ext in $EXTENSIONS; do $RUN -d "$BUILD" -c "CREATE EXTENSION IF NOT EXISTS $ext"; done
for f in $(find migrations -name '*.sql' | LC_ALL=C sort); do $RUN -d "$BUILD" -f "$f"; done
$RUN -d "$BUILD" -f seeds/baseline.sql
# Publish only after everything succeeded, then freeze it so no session can block clones.
$ADMIN -c "ALTER DATABASE $BUILD RENAME TO $TEMPLATE"
$ADMIN -c "ALTER DATABASE $TEMPLATE WITH IS_TEMPLATE true ALLOW_CONNECTIONS false"
fi
for old in $($ADMIN -c "SELECT datname FROM pg_database
WHERE datname LIKE 'app\_template\_%' AND datname NOT LIKE '%\_build'
AND datname <> '$TEMPLATE'"); do
$ADMIN -c "ALTER DATABASE $old WITH IS_TEMPLATE false"
$ADMIN -c "DROP DATABASE $old"
done
echo "$TEMPLATE" # workers clone with: CREATE DATABASE test_w1 TEMPLATE <this name>
A setup script hashes migrations, seed and fixture files, extensions, the server major version, and locale into the template name. It builds and seeds the database under a temporary name, renames it and marks it as a connection-blocked template, and drops templates whose hash no longer matches.
How do you give each parallel test worker its own cloned database?
Give every worker a database name that is deterministic and unique among the workers running at that moment. Most runners expose a stable worker identifier, such as PYTEST_XDIST_WORKER in pytest-xdist or JEST_WORKER_ID in Jest. Use it in preference to a process ID, because a PID is not stable across runs and makes leftover databases harder to trace. Combine it with the template name, for example app_test_<hash>_w3. The name then records both the schema version and the worker that owns it.
At worker startup, before any application code reads its configuration:
- Run
DROP DATABASE IF EXISTSon the worker's clone name. This clears anything a crashed earlier run left behind. - Run
CREATE DATABASE <clone> TEMPLATE <template>. Clone creation fails if another session is connected to the template. Workers that start at the same moment can collide, so serialize the step with an advisory lock or retry it briefly. - Write the clone's name into the variable your application reads, such as
DATABASE_URL. The variable must be set before the connection pool or ORM initializes. A pool built earlier keeps pointing at the old database.
This gives each worker its own database, but it does not isolate tests from each other. Every test a worker runs, in sequence, still shares that one database. Rows left by one test can make a later test pass or fail depending on execution order.
You still need cleanup between tests:
- Transaction rollback is fast. It breaks when the code under test commits explicitly or opens its own connections. It also does not reset sequences.
- Truncation handles both of those cases, but it costs more per test.
Cloning removes contention between workers. Cleanup removes contamination between the tests inside each worker.
import os
import psycopg2
import pytest
from psycopg2 import sql
TEMPLATE = "app_test_3f9a1c" # template built from migrations; hash = schema version
ADMIN_DSN = "postgresql://test:test@localhost:5432/postgres"
CLONE_LOCK = 4242 # arbitrary advisory-lock key shared by all workers
def pytest_configure(config):
worker = os.environ.get("PYTEST_XDIST_WORKER") # "gw0", "gw1", ...
if worker is None and getattr(config.option, "numprocesses", None):
return # xdist controller process runs no tests, so it needs no clone
clone = f"{TEMPLATE}_{worker or 'main'}"
conn = psycopg2.connect(ADMIN_DSN)
conn.autocommit = True # CREATE/DROP DATABASE cannot run inside a transaction
with conn.cursor() as cur:
# Remove a clone left behind by a crashed earlier run
cur.execute(sql.SQL("DROP DATABASE IF EXISTS {}").format(sql.Identifier(clone)))
# Serialize cloning: CREATE ... TEMPLATE fails while another session uses the template
cur.execute("SELECT pg_advisory_lock(%s)", (CLONE_LOCK,))
try:
cur.execute(sql.SQL("CREATE DATABASE {} TEMPLATE {}").format(
sql.Identifier(clone), sql.Identifier(TEMPLATE)))
finally:
cur.execute("SELECT pg_advisory_unlock(%s)", (CLONE_LOCK,))
conn.close()
# Set this before the app builds its pool/ORM engine; import app modules lazily
os.environ["DATABASE_URL"] = f"postgresql://test:test@localhost:5432/{clone}"
@pytest.fixture(autouse=True)
def clean_tables():
# Tests in one worker share its clone, so reset data and sequences after each test
yield
conn = psycopg2.connect(os.environ["DATABASE_URL"])
with conn, conn.cursor() as cur: # commits on success
cur.execute("TRUNCATE users, orders RESTART IDENTITY CASCADE")
conn.close()
A pytest conftest.py that gives each xdist worker its own clone of the template database, serializes clone creation with an advisory lock, sets DATABASE_URL before the app connects, and still truncates tables between tests inside each worker.
Should you clone per worker, per test file, or per test?
Granularity determines how often you pay the clone cost and how much isolation you get for it. You are trading isolation against setup time.
| Granularity | Isolation | Clones per run | Cleanup inside the database |
|---|---|---|---|
| Per worker | Between workers only | One per worker | Required between tests |
| Per test file | Between files | One per file | Required between tests in a file |
| Per test | Complete | One per test | None; drop the database |
Cloning per test is the simplest to reason about, because no state can leak between tests. The cost is that CREATE DATABASE ... TEMPLATE copies the template's files, so clone time grows with template size, and you pay it on every test.
Illustrative estimate (assumed inputs, not measured): 2,000 tests across 200 files, 8 workers, 50 ms average test time, and 1.5 s per clone for a template with seed data.
- Test execution: 2,000 × 50 ms = 100 s, or about 12.5 s per worker.
- Per worker: 8 clones, about 1.5 s of wall time per worker.
- Per file: 200 clones = 300 s, or about 37.5 s per worker.
- Per test: 2,000 clones = 3,000 s, or about 375 s per worker.
At per-test granularity, cloning takes roughly 30 times longer than the tests themselves. These wall times are also optimistic. Concurrent clones compete for disk I/O, so each clone slows down as more workers run at once.
A larger template raises the clone time in every row, which pushes most teams toward per-worker clones with transaction rollback or truncation between tests. Per-file cloning is useful as an exception for test files that change schema, use advisory locks, or commit across connections, where in-database cleanup is unreliable.
How do you run template-cloned database tests in CI and keep them clean?
In CI, run Postgres as a service container next to the test job so every run starts from an empty, disposable server. Add a step before the tests that checks for a template matching the current schema hash. If it is missing, run migrations and seed data into it. The cache key is the schema hash. A real cache hit lets the job skip migrations entirely, which is where most of the savings come from.
Sizing workers to the database
Set the number of test workers based on what the database can handle, not on how many cores the runner has. Each worker holds connections and does its own I/O, so the limits that matter are the database's CPU, memory, disk throughput, and max_connections. Too many workers cause connection errors or I/O contention, and the suite gets slower overall even though more tests run at once.
On a throwaway CI server, you can turn off durability with fsync = off, synchronous_commit = off, and full_page_writes = off. This cuts a large amount of disk wait. It also means a crash can corrupt the data, which is fine only because the container is discarded after the run. Never apply these settings to a shared, staging, or long-lived database.
Hygiene
- Drop clones at teardown. Each worker should drop its own clone when it finishes.
- Sweep for orphans. Crashed workers skip teardown, so give clones a predictable prefix that includes the run ID. Add a sweep step that drops any matching database not owned by a live run.
- Terminate lingering connections first.
DROP DATABASEfails while any session is connected. Cloning also fails if anything is connected to the template. Before either operation, callpg_terminate_backendon the sessions listed inpg_stat_activityfor that database. These are often left behind by IDEs, poolers, or earlier runs.
-- sweep.sql: run before cloning and again at job end, for example:
-- psql -v ON_ERROR_STOP=1 -v run_id=r1234 -v tpl=tpl_3f9a2c -f sweep.sql
-- Clones are named test_<run_id>_<worker>, e.g. test_r1234_w3.
-- 1. Kick off sessions (IDEs, poolers, crashed workers) on clones from
-- other runs, so DROP DATABASE will not fail with "being accessed".
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname LIKE 'test\_%'
AND datname NOT LIKE 'test\_' || :'run_id' || '\_%'
AND pid <> pg_backend_pid();
-- 2. Drop orphaned clones. DROP DATABASE cannot run inside a function
-- or transaction block, so generate one statement per database and
-- let psql's \gexec execute each one separately.
SELECT format('DROP DATABASE IF EXISTS %I', datname)
FROM pg_database
WHERE datname LIKE 'test\_%'
AND datname NOT LIKE 'test\_' || :'run_id' || '\_%'
AND NOT datistemplate
\gexec
-- 3. CREATE DATABASE ... TEMPLATE fails if anyone is connected to the
-- template, so clear its sessions before workers start cloning.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = :'tpl'
AND pid <> pg_backend_pid();
-- Each worker then clones it, and drops its own clone at teardown:
-- CREATE DATABASE test_r1234_w3 TEMPLATE tpl_3f9a2c;
-- DROP DATABASE IF EXISTS test_r1234_w3;
A psql sweep script that terminates lingering sessions, drops orphaned clones from earlier or crashed runs using a run-ID naming prefix, and clears connections to the template before workers clone it.
How can template cloning save engineering time?
The savings come from four places.
- Shorter CI waits. Workers stop replaying migration history at startup. Setup cost depends on the template's size, not on the length of its migration history, and it is paid once per schema change instead of once per worker per run.
- Fewer flaky reruns. When workers share a database, failures from lock contention and from rows deleted by another worker show up intermittently. Engineers then rerun jobs or spend time investigating failures that have nothing to do with their change. Separate databases per worker remove that class of failure.
- Faster debugging of real failures. Each clone is named after its schema hash and worker. When a test fails, you know which database it ran against, and you can keep that clone after a failed run to inspect its state with
psql. You no longer have to reason about what other workers were doing at the same moment. - Safe scaling of parallelism. Adding workers no longer adds proportional migration time or more contention on a shared database. Teams can raise worker counts until the database server's limits are reached without rewriting their cleanup strategy.
Illustrative estimate (assumed inputs, not measured): a full migration replay takes 90 s, a clone takes 1.5 s, the suite runs on 8 workers, and the team triggers 40 CI runs per day.
- Per-worker migrations run in parallel, so each run's setup costs about 90 s of wall time and 8 × 90 s = 720 s of database work.
- With a cached template, setup costs about 1.5 s per worker. Clones are serialized by the advisory lock, so worst-case wall time is about 8 × 1.5 s = 12 s. Total database work is also about 12 s.
- Wall-time savings per run: about 78 s. Across 40 runs: about 52 minutes of engineer waiting per day, before counting any reduction in flaky reruns.
- Runs that change migrations pay the template build once, about 90 s, and then clone as usual.
Your own numbers depend on migration length, template size, disk speed, and how often the schema changes. Measure setup time before and after on your own pipeline rather than relying on this estimate.
Who should use template-database testing?
The approach fits best when most of the following describe your project:
- Postgres is the production database, and the tests need real Postgres behavior such as constraints, triggers, extensions, and query plans, which an in-memory substitute would not reproduce.
- The suite is large enough to run in parallel, and CI wall time is something engineers wait on every day.
- The migration history is long, or seeding is expensive, so replaying it per worker is a noticeable part of setup.
- Tests go beyond one connection. Application code commits explicitly, uses several connections, or runs background jobs, so transaction rollback alone is unreliable.
- Someone owns the test infrastructure. That person or team can maintain the template build, the hashing, and the orphan sweep, and explain them to new contributors.
A large monolith with a long migration history and a parallel CI suite is the typical fit. A small service with a handful of migrations, a fast serial suite, or tests that all run cleanly inside a rolled-back transaction gains little and takes on extra moving parts. Projects that need to run the same suite against several database engines should avoid it too, because CREATE DATABASE ... TEMPLATE is Postgres-specific.
When should you avoid template databases, and what are the alternatives?
A clone gives each worker its own database and nothing else. External caches, queues and object storage stay shared, so their keys need a worker prefix or tests will collide. The same applies to anything keyed by database name. Roles and server settings are cluster-wide, so a test that creates a role or changes configuration affects every worker. Advisory locks and LISTEN/NOTIFY are scoped per database and do separate cleanly, but only if every connection, including a background listener, points at the worker's clone and not at a hardcoded name.
Cloning per worker rather than per test also leaves ordering side effects. Tests that share a clone can pass or fail depending on what ran before them. CREATE DATABASE ... TEMPLATE is Postgres-specific, so this approach ties your test infrastructure to Postgres. The template build, the worker-to-database mapping and the orphan sweeper are also code that every new contributor has to understand before they can debug a failing pipeline.
| Approach | Isolation | Per-test cost | Main drawback |
|---|---|---|---|
| Transaction rollback | Strong, within one connection | Very low | Fails for code that commits, opens its own connections, or runs work in background threads |
| Truncation | Data only | Grows with table count | Leaves sequences and schema state unless reset explicitly |
| Per-worker migrations | Full database | Paid once per worker | Slow startup when there are many migrations |
| Instance per worker | Full cluster | High memory and startup cost | Heavy for CI runners |
| Template clone | Full database | Low | The maintenance burden described above |
Start with transaction rollback. Move to template clones when the suite has outgrown rollback and wall-clock CI time is costing engineers real waiting. That usually happens in a large monolith with long migration history and many workers. Small services with fast migrations rarely recover the setup cost.
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 postgres template database tests, 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
.envby 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.