Parallel Tests on a Shared Database: Transactions vs. Templates

Why the monolith suite runs serially today

Parallelizing against a shared database means running several test workers at once, all pointed at the same Postgres server, without any test seeing another's writes. Most runners will happily start eight workers if you set the parallelism flag. What they won't do is stop those workers from colliding in the database, and the collisions surface as flaky failures that don't reproduce when you rerun a single file.

The causes are almost always the same few habits that were harmless while tests ran one at a time:

• Shared mutable rows. A seeded admin user or settings row gets updated by one test while another test reads it.
• Fixed primary keys. Fixtures that insert id = 1 collide with each other and raise unique-constraint violations.
• Truncate-between-tests cleanup. One worker's TRUNCATE wipes tables while another worker is still mid-test.
• Global sequences. Sequences are not rolled back and are shared across sessions. Any assertion on an exact generated ID breaks as soon as another worker consumes values.
• Table-wide count assertions. "Expect 3 orders" passes only if no other test is inserting orders at the same moment.

Each of these assumes the database belongs to the current test. Raising the worker count only makes that assumption fail more often. The real decision is the isolation scope: whether each test gets its own transaction, each worker gets its own database, or both. The rest of this article works through that choice. It is aimed at backend and platform engineers whose suites are slow mainly because they spend their time waiting on the database.

How per-test transaction rollback works and where it breaks

The mechanism is simple. Before each test, the harness checks out one connection, issues BEGIN, and hands that connection to everything the test touches. When the test finishes, the harness issues ROLLBACK, and the database discards every insert, update, and delete. No cleanup scripts or truncation are needed. Because nothing is ever committed, parallel workers on the same database never see each other's rows. If the application code opens its own transactions, the harness wraps them in savepoints so that an inner "commit" becomes RELEASE SAVEPOINT and the outer rollback still wins.

For this to work, the application's connection pool must return that single pinned connection instead of a fresh one. Otherwise app code reads through a different session and cannot see the test's uncommitted fixtures.

The model breaks wherever the code assumes that commits are real:

• Explicit commits that bypass the savepoint wrapper make data permanent and leak it into other tests.
• Separate connections, such as background job threads, async workers, or a second pool for read replicas, cannot see uncommitted data and will fail or hang waiting on locks.
• DDL takes locks that block other workers on a shared schema, and some statements, such as CREATE INDEX CONCURRENTLY, refuse to run inside a transaction block.
• Commit-time behavior never fires. This covers deferred constraints, deferred constraint triggers, and after-commit hooks that send email or enqueue jobs. Tests pass even though the code would violate a constraint at a real commit.

Pinning the pool also hides real connection-handling bugs, such as leaked checkouts, missing transactions, and code that relies on session state. Production will hit those bugs, and the test suite will not.

Copy# conftest.py: per-test rollback with pytest + SQLAlchemy 2.0 on PostgreSQL
import pytest
from sqlalchemy import create_engine, text

from myapp.db import SessionLocal  # the sessionmaker the application uses


@pytest.fixture(scope="session")
def engine():
    eng = create_engine("postgresql+psycopg://test:test-password@localhost:5432/app_test")
    yield eng
    eng.dispose()


@pytest.fixture
def db(engine):
    # Pin one connection per test and open the outer transaction on it.
    connection = engine.connect()
    outer = connection.begin()

    # Route the app's sessionmaker to the pinned connection so app code sees
    # the test's uncommitted rows. With create_savepoint, app-level
    # session.commit() releases a SAVEPOINT and does not COMMIT.
    saved_kw = dict(SessionLocal.kw)
    SessionLocal.configure(bind=connection, join_transaction_mode="create_savepoint")
    session = SessionLocal()
    try:
        yield session
        # Deferred constraints are checked only at a real COMMIT, which never
        # happens here. Force the check now so a violation fails the test.
        session.execute(text("SET CONSTRAINTS ALL IMMEDIATE"))
    finally:
        session.close()
        outer.rollback()  # discard every write made during the test
        connection.close()
        # Restore the app's original configuration. Threads, workers, or other
        # engines never used this connection, so they could not see the test data.
        SessionLocal.kw.clear()
        SessionLocal.kw.update(saved_kw)

A pytest fixture pins one connection, routes the application's SQLAlchemy sessionmaker to it in savepoint mode so app-level commits become savepoint releases, forces deferred constraint checks, and rolls back the outer transaction after each test.

How per-worker databases from a Postgres template work

Per-worker databases avoid the trade-off entirely. Each worker gets a real database with real commits, so the application uses its normal pool and transaction boundaries.

Build the template once

Before any worker starts, run migrations (and any shared seed data) into a dedicated database such as app_test_template. Then make sure nothing is still connected to it, including your migration tool's pool, an open psql session, or an IDE. Postgres refuses CREATE DATABASE ... TEMPLATE while the source has other active sessions, because it copies the database at the file level and needs a consistent snapshot. Close your own connections explicitly. A forced pg_terminate_backend is a reasonable fallback in CI.

One clone per worker

Each worker reads the ID its runner exposes, such as PYTEST_XDIST_WORKER under pytest-xdist or JEST_WORKER_ID under Jest. It then creates app_test_<id> from the template, dropping it first if it already exists. The worker rewrites the database name in its connection string, usually the DATABASE_URL environment variable, before the application or ORM initializes. Workers never touch each other's data, so no coordination is needed.

Keeping the template fresh

A stale template passes tests against an old schema. Name the template after a hash of the migration files, for example app_test_tpl_3f9a1c. A change to any migration produces a new name, which triggers a rebuild, and old templates can be dropped.

Costs

Every clone is a full physical copy, so disk usage scales with worker count times template size. Each worker also holds its own backend connections, which consume server memory. Clone time grows with template size. A small schema clones quickly, but a large seeded template can take long enough per worker to cancel out the parallelism gains on a short suite.

Copy# conftest.py: one cloned database per pytest-xdist worker
# Assumes a CI step already ran migrations into the template named below.
import hashlib, os, pathlib
import psycopg
from psycopg import sql
import pytest

ADMIN_URL = os.environ.get(
    "TEST_ADMIN_URL", "postgresql://postgres:postgres@localhost:5432/postgres")

def template_name() -> str:
    # Any change to a migration file yields a new template name, forcing a rebuild
    h = hashlib.sha256()
    for f in sorted(pathlib.Path("migrations").glob("*.sql")):
        h.update(f.name.encode()); h.update(f.read_bytes())
    return f"app_test_tpl_{h.hexdigest()[:6]}"

@pytest.fixture(scope="session", autouse=True)
def worker_database():
    worker = os.environ.get("PYTEST_XDIST_WORKER", "gw0")  # unset without -n
    db, tpl = f"app_test_{worker}", template_name()
    # CREATE DATABASE cannot run inside a transaction block, hence autocommit
    with psycopg.connect(ADMIN_URL, autocommit=True) as conn:
        # CI fallback: evict stray sessions (IDE, psql, migration pool) on the template
        conn.execute(
            "SELECT pg_terminate_backend(pid) FROM pg_stat_activity"
            " WHERE datname = %s AND pid <> pg_backend_pid()", (tpl,))
        conn.execute(sql.SQL("DROP DATABASE IF EXISTS {}").format(sql.Identifier(db)))
        conn.execute(sql.SQL("CREATE DATABASE {} TEMPLATE {}").format(
            sql.Identifier(db), sql.Identifier(tpl)))
    # Route this worker before the app or ORM reads its config
    os.environ["DATABASE_URL"] = ADMIN_URL.rsplit("/", 1)[0] + "/" + db
    yield
    # Clone is left in place; the next run drops and recreates it

A session-scoped pytest fixture that clones a migration-hash-named template into app_test_ and points DATABASE_URL at it before the application initializes.

Combining both layers in one setup

That cost is paid once per worker, not once per test, and this is why the two techniques combine well. Cloning gives each worker its own database, and transactions keep individual tests cheap inside it.

The layers

• One shared Postgres server hosts everything. Workers never compete for the same rows because they never share a database.
• One template database holds the migrated schema and the baseline seed data. It is built once per run, or reused when the migrations haven't changed.
• One cloned database per worker, created from the template with CREATE DATABASE ... TEMPLATE. This is what isolates workers from each other.
• One transaction per test inside each worker's database, rolled back at the end. This is what isolates tests from each other within a worker.

Lifecycle

1. Run migrations and seeds into the template, then close every connection to it. Postgres refuses to clone a database that has active sessions.
2. When each worker starts, it creates its clone under a predictable name such as app_test_w3, so leftovers from a crashed run are easy to find and drop.
3. For each test, the worker opens a transaction, runs the test, and rolls back. The clone returns to its baseline in milliseconds.
4. When the worker exits, it drops its clone. A cleanup step at the start of the next run removes any clones that a killed worker left behind.

Keep a serial commit suite

Rollback hides anything that only happens at commit. That includes deferred constraint checks, NOTIFY delivery, and code that runs after commit. Keep a small, tagged suite that commits for real against a fresh clone and runs serially.

Copy# conftest.py: needs pytest-xdist and psycopg 3. Run: pytest -n 4 -m "not commits"
import psycopg
import pytest
from psycopg import sql

ADMIN_DSN = "postgresql://test:test@localhost:5432/postgres"
TEMPLATE = "app_test_template"  # migrated + seeded earlier; no open sessions

def _admin(stmt, *names):
    # CREATE/DROP DATABASE cannot run inside a transaction block
    with psycopg.connect(ADMIN_DSN, autocommit=True) as admin:
        admin.execute(sql.SQL(stmt).format(*map(sql.Identifier, names)))

@pytest.fixture(scope="session")
def worker_conn(worker_id):
    # worker_id comes from pytest-xdist: "gw0", "gw1", ... ("master" when serial)
    name = f"app_test_{worker_id}"  # predictable, so crash leftovers are easy to find
    _admin("DROP DATABASE IF EXISTS {}", name)  # leftover from a killed run
    _admin("CREATE DATABASE {} TEMPLATE {}", name, TEMPLATE)
    conn = psycopg.connect(f"postgresql://test:test@localhost:5432/{name}")
    yield conn
    conn.close()  # the clone cannot be dropped while a session is open
    _admin("DROP DATABASE IF EXISTS {}", name)

@pytest.fixture
def db(worker_conn):
    # Each test runs in one transaction that is never committed
    yield worker_conn
    worker_conn.rollback()  # clone is back at the template baseline

# Commit-time behavior (deferred constraints, NOTIFY, after-commit hooks).
# Mark these tests with @pytest.mark.commits and run them serially: pytest -m commits
# Register the marker in pytest.ini so pytest does not warn about it.
@pytest.fixture
def committing_db(worker_id):
    name = f"app_test_commit_{worker_id}"
    _admin("DROP DATABASE IF EXISTS {}", name)
    _admin("CREATE DATABASE {} TEMPLATE {}", name, TEMPLATE)  # fresh clone per test
    conn = psycopg.connect(f"postgresql://test:test@localhost:5432/{name}")
    yield conn  # the test calls conn.commit() itself
    conn.close()
    _admin("DROP DATABASE {}", name)

A pytest conftest.py in which each pytest-xdist worker clones the template into its own database and rolls back after every test, while a separate serial fixture gives commit-tagged tests a fresh clone where they can commit for real.

Choosing an approach: alternatives and when to avoid each

The combined approach is the usual default. Use a separate server per worker only when tests need state that exists at the server level.

Don't run these tests on a shared server. Put them in a serial group instead:

• Roles and grants. Roles exist across the whole cluster, so one worker's CREATE ROLE or DROP ROLE is visible to all the others.
• Server settings. ALTER SYSTEM and reloading configuration change the server for every database on it.
• Extension installs and upgrades. Extensions are installed per database. However, extensions loaded through shared_preload_libraries share one copy of the library across the server.
• Cross-database behavior. This covers dblink, postgres_fdw loopbacks, and code that creates or drops databases. These tests touch other workers' databases or the template itself.

Advisory locks and LISTEN/NOTIFY are scoped to one database. Per-worker databases already keep them apart. Under transaction rollback, though, they are a problem in two ways:

• Session-level advisory locks are not released when a transaction rolls back.
• Notifications are delivered only on commit.

Move those tests into the commit suite rather than the serial group.

Making tests order-independent and sizing the shared server

Rules for order independence

• Never hard-code primary keys. Fetch the ID from the insert you just made. A test that assumes id = 1 passes alone and fails once another worker's sequence calls have advanced the counter. Sequences are not rolled back with transactions.
• Assert on rows the test created. "Orders count is 3" breaks when a parallel test inserts an order. Filter by the IDs or a tag you own.
• Generate unique values for unique-constrained columns. Emails, slugs, and external references should include a worker ID or random suffix. Otherwise two workers collide and one blocks or fails on the constraint.

Sizing the shared server

Peak connections are roughly workers × pool size, plus migrations and your own sessions. Compare that number against max_connections and leave headroom for superuser-reserved slots. Twelve workers with a pool of 10 need 120 connections, which exceeds a default-sized server before the suite starts.

Setting fsync = off and synchronous_commit = off cuts disk waits. This is acceptable only on a disposable instance. A crash can corrupt the cluster, so never apply it to a server that also holds anything you would need to keep.

Each backend uses its own memory, and work_mem applies per sort or hash operation. Budget for all workers running at once, not for one.

Illustrative estimate (assumed: 8 cores, a 20-minute serial suite, 70% of the work parallelizable):

Past the core count, runtime gets worse instead of better.

Copy-- 1) Sizing check: read the limits on the shared test server.
--    Usable client slots = max_connections - superuser_reserved_connections.
--    Compare against workers x pool size + migrations + your own sessions.
SELECT name, setting
FROM pg_settings
WHERE name IN ('max_connections', 'superuser_reserved_connections',
               'work_mem', 'fsync', 'synchronous_commit');

-- 2) During a parallel run, see who is actually holding connections.
SELECT application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY count(*) DESC;

-- 3) Order-independent test data: no hard-coded IDs, a unique email per
--    test, and an assertion only on rows this test created.
WITH new_customer AS (
  INSERT INTO customers (email)
  VALUES ('w3-' || gen_random_uuid() || '@example.test')  -- w3 = worker ID
  RETURNING id                                            -- use the real ID
),
new_orders AS (
  INSERT INTO orders (customer_id, total_cents)
  SELECT c.id, t.cents
  FROM new_customer c,
       (VALUES (1000), (2500), (400)) AS t(cents)
  RETURNING customer_id
)
-- Expect 3: counts only this test's orders, not the global table.
SELECT count(*) AS created_orders
FROM new_orders;

SQL that checks the server's connection limits and durability settings, shows live connection usage during a parallel run, and creates test data using returned IDs, a per-worker unique email, and an assertion scoped to the test's own rows.

Rolling it out: verification, cost, and engineering time

Don't switch the whole suite in one change. Do one parallel run to find the tests that fail. Tag those as serial and run them as a separate single-worker group after the parallel phase finishes. Treat the quarantine list as a backlog. Each entry should name the shared state it depends on, such as a hard-coded email address, a global counter, or a cached settings row. Remove the entry once the dependency is fixed, so the list shrinks over time.

Verifying isolation

Run the parallel group in random order at several worker counts, for example 2, 4, and your target. Each count changes which tests share a worker database and in what sequence, so each one exposes different collisions. Log the random seed so any failure can be reproduced. If a test passes serially but fails in parallel, it has a shared-state bug, not a flake. Adding a retry hides the bug and teaches the team to ignore red builds.

Cost versus time saved

The costs are:

• Rewriting quarantined tests.
• Rebuilding the template whenever migrations or seed data change. Key the template cache on a hash of both so a stale template is never reused.
• A database server sized for N concurrent workers.

Illustrative estimate (assumed numbers, not a benchmark)

hours saved per week = devs × runs per dev per week × minutes saved per run × recovery factor ÷ 60

Assumptions:

• 20 developers
• 10 runs each per week
• 10 minutes saved per run (20 → ~9.5 at 4 workers)
• Recovery factor of 0.5, because some waiting time was already spent on other work

That gives 20 × 10 × 10 × 0.5 ÷ 60 ≈ 17 hours per week. If the retrofit takes 120 engineer-hours, it pays back in about 7 weeks. Fewer flaky reruns would shorten that further.

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 tests shared database, 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.