DEV Community

Libme
Libme

Posted on

pytest-xdist Tests Pass Serially but Fail in Parallel: One Postgres Database per Worker

If your suite is green with pytest and flaky with pytest -n auto, the workers are almost certainly sharing one database and stepping on each other's rows. The durable fix is to give each xdist worker its own database, cloned from a pre-migrated template, and to roll back each test inside its worker. That costs a few seconds of setup per worker and removes the entire class of cross-test interference.

What does the failure look like?

The symptoms are inconsistent, which is what makes them expensive to debug. These are the ones I see most often when a suite moves to -n auto:

psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(test@example.com) already exists.

AssertionError: assert 3 == 1   # count of rows your own test just inserted

psycopg.errors.DeadlockDetected: deadlock detected
Enter fullscreen mode Exit fullscreen mode

Each test is correct in isolation. Two workers run at once against the same tables, one inserts test@example.com, the other inserts the same address a millisecond later, and whichever loses fails. Rerunning "fixes" it, so the failure gets blamed on flakiness and the retries get added to CI. A shared mutable database is the cause, not flakiness.

Takeaway: if failures vanish with -n 0, stop suspecting the tests and start suspecting shared state.

Why doesn't transaction rollback alone fix it?

Wrapping every test in a transaction that rolls back is a good habit, and it does help with leftover rows. It does not solve everything in parallel:

  • Rolled-back inserts still hold unique-index entries until the transaction ends, so two workers inserting the same key can block or fail each other.
  • Code under test that opens its own connection (a background job, a second pool) commits for real, outside your rollback.
  • Tests that truncate tables, run DDL, or reset sequences affect every worker at once.

The dead ends developers typically try first are random email suffixes (works until a fixture hardcodes something), --dist loadscope (reduces collisions but does not remove them), and marking tests serial (gives back the speedup you wanted). Per-worker databases beat all three.

Takeaway: rollback isolates tests inside one worker; it cannot isolate workers from each other.

How do I give each xdist worker its own database?

xdist gives every worker a name through the built-in worker_id fixture: gw0, gw1, and so on, or master when you are not running distributed. Use it to name a database. The pattern below assumes PostgreSQL 13 or newer (for DROP DATABASE ... WITH (FORCE)) and psycopg 3.

First, build a migrated template database once, before pytest starts. A cloned template is much faster than running every migration in every worker, and doing it outside pytest avoids a race between workers.

#!/usr/bin/env bash
set -euo pipefail
psql "$TEST_ADMIN_DSN" -c "DROP DATABASE IF EXISTS app_test_template WITH (FORCE)"
psql "$TEST_ADMIN_DSN" -c "CREATE DATABASE app_test_template"
# Run your migrations against the template, e.g. with Alembic:
DATABASE_URL="$(python -c "from psycopg.conninfo import make_conninfo as m; import os; print(m(os.environ['TEST_ADMIN_DSN'], dbname='app_test_template'))")" \
  alembic upgrade head
Enter fullscreen mode Exit fullscreen mode

Then a session-scoped fixture clones the template for each worker:

# conftest.py
import os

import psycopg
import pytest
from psycopg import sql
from psycopg.conninfo import make_conninfo

ADMIN_DSN = os.environ["TEST_ADMIN_DSN"]  # points at the "postgres" maintenance DB
TEMPLATE = "app_test_template"


@pytest.fixture(scope="session")
def database_dsn(worker_id):
    name = f"app_test_{worker_id}"
    with psycopg.connect(ADMIN_DSN, autocommit=True) as admin:
        admin.execute(
            sql.SQL("DROP DATABASE IF EXISTS {} WITH (FORCE)").format(sql.Identifier(name))
        )
        admin.execute(
            sql.SQL("CREATE DATABASE {} TEMPLATE {}").format(
                sql.Identifier(name), sql.Identifier(TEMPLATE)
            )
        )
    yield make_conninfo(ADMIN_DSN, dbname=name)
    with psycopg.connect(ADMIN_DSN, autocommit=True) as admin:
        admin.execute(
            sql.SQL("DROP DATABASE IF EXISTS {} WITH (FORCE)").format(sql.Identifier(name))
        )


@pytest.fixture
def db(database_dsn):
    with psycopg.connect(database_dsn) as conn:
        yield conn
        conn.rollback()  # discard whatever the test did
Enter fullscreen mode Exit fullscreen mode

Run it with pytest -n auto. Each worker gets app_test_gw0, app_test_gw1, and so on, and no two workers ever touch the same rows.

One gotcha cost me an afternoon: CREATE DATABASE ... TEMPLATE fails with source database "app_test_template" is being accessed by other users if anything is connected to the template. Nothing in your tests may connect to it, and neither should the migration step once it finishes. If you cannot guarantee that, run migrations per worker instead and accept the slower start.

Takeaway: name the database after worker_id, clone it from a template nobody connects to, and drop it with FORCE on teardown.

Which isolation strategy fits which suite?

Strategy Isolation between workers Startup cost Where it breaks
One shared DB + rollback None Lowest Unique keys, deadlocks, code that commits
Randomized test data Partial Low Hardcoded fixtures, global counts
Per-worker DB from template Full Seconds per worker Template must stay unconnected
Per-worker DB, migrations each time Full Grows with migration count Slow on large schemas
Container per worker (for example Testcontainers) Full, including extensions and config Highest Needs a container runtime in CI

If your tests need a different Postgres version, extensions, or server settings per run, the container approach is the one to look at; Testcontainers is the library that handles starting and stopping those containers from test code, though it requires Docker access on the CI runner. For everything else, the template approach is the cheapest full isolation I have found.

Takeaway: pick per-worker databases first, and reach for containers only when the server itself must differ.

What about CI runners and teardown?

Two details matter on CI. First, make sure the Postgres service has enough max_connections for workers times your pool size; -n auto on a many-core runner can open more connections than a default install allows. Second, leaked databases from a killed job pile up on a long-lived server, which is why the fixture drops the database up front as well as at teardown. On ephemeral service containers this does not matter, but on a shared test server it keeps names from colliding after a crash.

Redis, S3-compatible storage, and search indexes have the same shared-state problem. Prefix keys, buckets, or index names with worker_id for the same reason.

Takeaway: anything stateful that your tests write to needs a worker_id in its name, not just the database.

FAQ

Why do my pytest tests fail with pytest-xdist but pass without it?

Parallel workers are sharing state, most often a single database or fixed filenames, so one worker's writes collide with another's. Give each worker its own database using the worker_id fixture.

How do I get a unique database per pytest-xdist worker?

Read the worker_id fixture (gw0, gw1, or master) in a session-scoped fixture and create a database named after it, ideally cloned with CREATE DATABASE ... TEMPLATE. Drop it on teardown.

Is --dist loadscope enough to stop parallel test collisions?

No. It keeps tests from the same module or class on one worker, which reduces collisions, but different modules still run concurrently against the same database.

Bottom line

If your suite has a real database and fails only under -n auto, per-worker databases cloned from a migrated template are the fix to adopt first. Keep transaction rollback inside each worker for per-test cleanup. Move to a container per worker only when you need different server versions or configuration. And if the suite is small enough to run serially in a minute or two, skip parallelism entirely, because the setup above is only worth it once test time is the bottleneck.

Related reading

Top comments (0)