Which Test Data Isolation Strategy Fits a Shared Database?

A suite that stayed green because tests ran serially breaks once workers go up, shards are added or E2E tests drive a running server: the same few tests co-fail, yet each passes alone. Test data isolation comes down to three questions, in order: does the code under test use the test's own connection and never commit, does more than one process commit, and how faithful must the engine be (SQLite, or the production engine via Testcontainers)? Picture the shapes in the regression testing post: a PR suite sharded 4 ways and a nightly run of 6 shards across 3 browsers (18 jobs) on one database. This post is for SDETs and Python backend developers facing runs like that.

This post assumes you know how to isolate data for a single test (unique records, fixture cleanup or rollback, fixture scopes) and how your runner splits tests across workers and CI shards. The PostgreSQL recipe also assumes you run schema migrations and can create a database. Catch up with the four-step triage and pytest fixtures guide, the Docker and workers post and Step 6 of the GitHub Actions guide, and the PostgreSQL CREATE DATABASE page. Examples were tested with PostgreSQL 16, pytest 9.1, pytest-xdist 3.8, psycopg 3 and Playwright 1.64 unless labeled snippet or linted.

Table of contents

• Five suite shapes and the isolation strategy each one needs
• Question 1: does the code under test share the test's connection?
• Question 2: shards commit at the same time, even at one worker each
• Per-worker databases in pytest-xdist: clone a migrated template
• What the template database needs before the first clone
• Worker-keyed data in Playwright when a running server owns the connections
• Shards restart the indexes, so the key needs the job as well
• Question 3: engine fidelity, CI time, and what Testcontainers supplies
• Common questions about test data isolation with workers and shards
• Settle reset, owner and source before adding workers

Five suite shapes and the isolation strategy each one needs

Find your row first. Each row after the first adds one layer to the row above or swaps a layer's answer: the reset (rollback, delete-all or unique data), the owner (a database or a data key per worker and job) and the source (in-memory SQLite, or the production engine through Testcontainers, a library that starts a throwaway database in a container). Rows 1 and 2 answer question 1, rows 3 and 4 add question 2, and row 5 adds question 3.

The questions run from the choice hardest to change later to the easiest (this post's argument, not a documented rule): the reset lives in test code and fixtures everywhere, the owner in fixtures and CI configuration, the source in a URL and a container runtime.

Question 1: does the code under test share the test's connection?

Rollback works when the code under test uses the test's own connection and never commits it, as with an in-process repository, session or framework test client. It can't reach anything that opens its own connection: a running app server called over HTTP, a browser-driven E2E test, a queue consumer.

Here, isolation means tests can't see or change one another's data, not a SQL isolation level and not Playwright's browser-context isolation.

A transaction is a group of statements that commit or roll back together, and it belongs to one connection. A rollback undoes only what its own connection did: another connection can't see the test's uncommitted rows, and its commits survive the test's rollback. Two sessions on one database show both effects:

-- PostgreSQL 16. Two sessions, one database.
CREATE TABLE accounts (id bigserial PRIMARY KEY, email text NOT NULL UNIQUE);

-- Session A
BEGIN;
INSERT INTO accounts (email) VALUES ('a@example.com');

-- Session B (autocommit, its own connection)
SELECT count(*) FROM accounts;                          -- 0: A's row is not committed yet
INSERT INTO accounts (email) VALUES ('b@example.com');  -- commits immediately

-- Session A
ROLLBACK;

-- Session B
SELECT count(*) FROM accounts;                          -- 1: B's row survives A's rollback

The limit isn't DDL: in PostgreSQL a CREATE TABLE inside a rolled-back transaction leaves no table (reproduced on 16.15; the PostgreSQL wiki documents its transactional DDL).

The triage post holds both starting shapes: the db_session rollback fixture, which needs the test's connection, and the api_client-based test_user fixture, which creates unique data through an API. The test-isolation-patterns skill catalogs more.

Question 2: shards commit at the same time, even at one worker each

Once anything is committed by more than one process, something must own it: a database or a data key per worker, and per job when jobs share one database server.

Workers are processes inside one job, and a shard is a separate job: "Each shard is like a separate job that can run independently". The regression post's PR suite, sharded 4 ways at workers: 1 (as in the Docker and workers post), is therefore 4 jobs writing at once, and its nightly run is 18. Shards share data only when their jobs point at one database server; if each job starts its own, only the workers inside a job need an owner.

A rollback-only suite whose code never commits needs no owner, because open transactions are invisible to other connections by default. The cost is in row 1: on PostgreSQL 16.15 a second open transaction inserting the same unique key waits for the first to end, then proceeds after a rollback or fails after a commit.

Identity isn't isolation until it keys a name, a schema or a database:

Per-worker databases in pytest-xdist: clone a migrated template

A per-worker database helps only if the code under test connects to it: the code runs in the test process, or a server starts per worker with that worker's URL. It earns its cost when the code commits (row 3).

Start with a template database, an ordinary database that CREATE DATABASE ... TEMPLATE copies, and migrate it once before any worker starts: in a prepare job when jobs share a server, or in each job before pytest. Tests never connect to the template.

Each worker clones the template from an autocommit connection to the maintenance database (postgres), since CREATE DATABASE can't run in a transaction block. The name combines testrun_uid and worker_id. In xdist 3.8.0 testrun_uid is a 32-character hex string, shared by one run's workers and new for each pytest invocation (also at -n0), unless CI sets --testrunuid for a run with workers. Each worker runs its own session, so a session-scoped fixture runs once per worker.

# conftest.py (pytest 9.1, pytest-xdist 3.8, psycopg 3, PostgreSQL 16)
import psycopg
import pytest
from psycopg import sql

SERVER = "postgresql://postgres@localhost:5432"  # stands in for your test server
TEMPLATE = "app_template"  # migrated before the run; no test connects to it


@pytest.fixture(scope="session")
def worker_database(testrun_uid, worker_id):
    """Clone the template once per worker and yield the clone's URL."""
    name = f"app_{testrun_uid[:8]}_{worker_id}"
    with psycopg.connect(f"{SERVER}/postgres", autocommit=True) as admin:
        admin.execute(
            sql.SQL("CREATE DATABASE {} TEMPLATE {}").format(
                sql.Identifier(name), sql.Identifier(TEMPLATE)
            )
        )
        try:
            yield f"{SERVER}/{name}"
        finally:
            admin.execute(
                sql.SQL("DROP DATABASE {} WITH (FORCE)").format(sql.Identifier(name))
            )


@pytest.fixture(autouse=True)
def accounts_db(worker_database):
    """Stands in for code that commits: autocommit connection, delete-all before each test."""
    with psycopg.connect(worker_database, autocommit=True) as conn:
        conn.execute("TRUNCATE accounts RESTART IDENTITY")
        yield conn

The code under test must be handed the URL that worker_database yields: an app factory or engine that takes it as an argument (accounts_db stands in for that), or a per-worker server that reads it from config at start.

Four tests insert the same email:

# test_accounts.py: four tests that insert the same email
import pytest


@pytest.mark.parametrize("attempt", range(4))
def test_signup_stores_one_row(accounts_db, attempt):
    accounts_db.execute("INSERT INTO accounts (email) VALUES ('ada@example.com')")
    count = accounts_db.execute("SELECT count(*) FROM accounts").fetchone()[0]
    assert count == 1

The clone belongs to the worker, not the test file, because under the default --dist load xdist sends tests to "any worker that is available, without any guaranteed order". Inside the clone any reset works: rollback if the code never commits, delete-all if it does.

On a toy suite on one machine (PostgreSQL 16.15), the same four tests at -n 4 against one fixed database failed in 20 of 20 runs (three of four tests each time). Against per-worker clones all 20 runs passed, each with four distinct databases and no clone left behind.

What the template database needs before the first clone

The template must have no connected session when a clone starts, and the code under test must connect to the clone, never the template. PostgreSQL's docs say "no other sessions can be connected to the template database while it is being copied" and that CREATE DATABASE "will fail if any other connection exists when it starts".

-- PostgreSQL 16, psql. Terminal 1: build the template, then clone it while it is idle.
CREATE DATABASE app_template;
\c app_template
CREATE TABLE accounts (id bigserial PRIMARY KEY, email text NOT NULL UNIQUE);  -- stands in for migrations
\c postgres
CREATE DATABASE app_gw0 TEMPLATE app_template;  -- succeeds: no session is connected to the template

-- Terminal 2: connect to the template and stay connected.
--   psql -d app_template

-- Terminal 1: clone again. The clone waits for that session to leave and fails if it stays.
CREATE DATABASE app_gw1 TEMPLATE app_template;
-- ERROR:  source database "app_template" is being accessed by other users
-- DETAIL:  There is 1 other session using the database.

The docs describe a failure; on PostgreSQL 16.15 the clone waited first, then failed, and succeeded if the other session left during the wait. Close the migration connection before any worker starts. Through the earlier fixture, a held template failed all four workers at setup with psycopg.errors.ObjectInUse and left no clone behind: a leaked connection to find, not a reason to retry.

Four concurrent clones from one template succeeded on 16.15 (20 of 20 runs at -n 4, and 10 of 10 with a template of about 28MB) with no file lock or retry logic. The CREATE DATABASE documentation does not describe concurrent clones, so treat that as observed on 16.15, not guaranteed. Keep the template schema-only: since PostgreSQL 15 the default WAL_LOG strategy is "the most efficient strategy in cases where the template database is small".

Worker-keyed data in Playwright when a running server owns the connections

A running app server keeps the database it was started with, so a per-worker database needs one server per worker. The default is one shared database where each worker owns data keyed by name, as for pytest tests that call a running API.

Playwright's docs do this with a worker-scoped fixture that creates user-${workerIndex}: "All tests run by the worker reuse the same user".

Which index to key on is reasoning, not a documented rule. workerIndex is unique per worker process, and "the new worker process gets a new unique workerIndex" after a restart, so a restarted worker starts fresh. parallelIndex stays within 0 to workers - 1 and survives a restart, so it fits a fixed pool of N databases or schemas, at the price that a restarted worker meets the previous one's leftovers.

Shards restart the indexes, so the key needs the job as well

With workers: 1, every shard's lone worker reports workerIndex 0 and parallelIndex 0, so two shards sharing a database both create user-0. In a config with chromium and firefox projects, a spec printing the project name, testInfo.config.shard, workerIndex and parallelIndex shows it on Playwright 1.64.1:

$ npx playwright test --project=chromium --shard=1/2
chromium shard={"current":1,"total":2} workerIndex=0 parallelIndex=0
$ npx playwright test --project=chromium --shard=2/2
chromium shard={"current":2,"total":2} workerIndex=0 parallelIndex=0
$ npx playwright test --project=firefox --shard=1/2
firefox shard={"current":1,"total":2} workerIndex=0 parallelIndex=0
$ npx playwright test --project=chromium --grep first
chromium shard=null workerIndex=0 parallelIndex=0

testInfo.config.shard separates the shards, but not the nightly shape: when each browser runs as its own job (--project=<name> --shard=1/2), chromium and firefox print the same shard, so a shard-only key gives 6 keys for 18 jobs. Key on the job: run, job index and worker.

// fixtures.ts (Playwright 1.64)
import { test as base } from '@playwright/test';
// snippet: createUser and deleteUsersByPrefix stand in for your data helpers
import { createUser, deleteUsersByPrefix } from './data';

// RUN_KEY and JOB_INDEX come from the CI workflow's env mapping.
const runKey = process.env.RUN_KEY ?? 'local';
const jobIndex = process.env.JOB_INDEX ?? '0';

export const test = base.extend<{}, { userName: string }>({
  userName: [
    async ({}, use, workerInfo) => {
      // The trailing "-" keeps w1 from matching w10 when deleting by prefix.
      const prefix = `u-${runKey}-j${jobIndex}-w${workerInfo.workerIndex}-`;
      const userName = `${prefix}user`;
      await createUser(userName);
      await use(userName);
      await deleteUsersByPrefix(prefix);
    },
    { scope: 'worker' },
  ],
});

Run as two jobs against one table, the naive user-${workerIndex} key made the second job's workers hit a unique violation, while the job key created four distinct names and every test passed. A shard-only key also collided for one shard on two browsers. Playwright's docs example is fine inside one job.

The run key is github.run_id plus github.run_attempt: run_id "does not change if you re-run the workflow run", and run_attempt "begins at 1 for the workflow run's first attempt, and increments with each re-run". The workflow maps the run key and strategy.job-index into env: (linted with actionlint, not run):

# .github/workflows/e2e.yml (excerpt: a step inside the matrix job)
      - name: Run Playwright shard
        env:
          RUN_KEY: ${{ github.run_id }}-${{ github.run_attempt }}
          JOB_INDEX: ${{ strategy.job-index }}
        run: npx playwright test --shard=${{ matrix.shardIndex }}/${{ matrix.shardTotal }}

strategy.job-index is zero-based and unique only within one matrix, so if more than one matrix job writes to the same database, add github.job (or the job name). A crashed worker leaves its prefix behind, so sweep the run's prefix after the last job.

Question 3: engine fidelity, CI time, and what Testcontainers supplies

Question 3 decides where the database comes from: in-memory SQLite, the production engine in a container, or a database the CI job provides. SQLite needs no server but isn't the production engine, so engine-specific behavior goes unexercised.

A container is a packaged, disposable copy of the software that a container runtime such as Docker starts and stops, and that runtime is the CI-time cost of fidelity. PyPI describes the Testcontainers package as a "library for throwaway instances of anything that can run in a Docker container", so every machine that runs the tests, CI runners included, needs a container runtime.

Testcontainers' own Python guide starts one module-scoped container, "so that it will only run once for all the tests in the module", and a function-scoped autouse fixture deletes all customer rows before each test, so "the tests can be run in any order". The container is the source; the delete-all fixture does the isolating. The same shape here is a snippet, not run:

# conftest.py: snippet, not run (needs a container runtime). testcontainers 4.15, psycopg 3
import psycopg
import pytest
from testcontainers.community.postgres import PostgresContainer


@pytest.fixture(scope="session")
def database_url():
    # Session scope starts one container per pytest process: under pytest-xdist, one per worker.
    with PostgresContainer("postgres:16-alpine", driver=None) as postgres:
        url = postgres.get_connection_url()
        with psycopg.connect(url, autocommit=True) as conn:
            conn.execute(  # stands in for migrations
                "CREATE TABLE accounts (id bigserial PRIMARY KEY, email text NOT NULL UNIQUE)"
            )
        yield url


@pytest.fixture(autouse=True)
def clean_accounts(database_url):
    # The container is only the source. This fixture does the isolating.
    with psycopg.connect(database_url, autocommit=True) as conn:
        conn.execute("TRUNCATE accounts RESTART IDENTITY")

driver=None gives a plain postgresql:// URL, since the default postgresql+psycopg2:// URL is rejected by psycopg 3, which must be installed separately.

The xdist how-to notes no built-in support for running a session-scoped fixture once, so a session-scoped container starts one container per worker (4 at -n 4) and a module-scoped one up to one per module per worker, derived from the docs and a stubbed run of the fixture wiring, not a started container. Sharing one container needs a run-once guard (the how-to shows a file lock), a recipe this post leaves out.

A container can also supply a reset: the Node module @testcontainers/postgresql ships snapshot() and restoreSnapshot(), which run a PostgreSQL template clone inside the container (source on GitHub).

Common questions about test data isolation with workers and shards

Does test data isolation with pytest-xdist need a per-worker database?

Only when something commits. If the code under test uses the test's own connection and never commits, the engine keeps open transactions apart. The testrun_uid example in the xdist docs isolates runs, not workers: one database per run, shared by every worker, so a per-worker name needs worker_id too.

How do you keep test data isolation intact when a failed CI job is re-run?

Put run_attempt in the key. run_id stays the same on a re-run and run_attempt increments, so a key that stops at run_id meets the first attempt's leftovers. If the first attempt crashed before its cleanup, its rows stay in a shared database: the new attempt's key keeps its data apart.

Do Playwright worker indexes stay unique across CI shards?

No. Each shard's worker starts at index 0 (reproduced on Playwright 1.64.1). When each browser runs as its own job (--project=<name> --shard=1/2), the shard number repeats across browsers, so key on the job index or on shard plus project name. Neither separates workers inside a job, so add workerIndex.

Does the test data isolation approach carry over to Jest, JUnit or Go?

The three questions and the run, job and worker key transfer, since they depend on what commits and where the database comes from. The template clone is PostgreSQL's CREATE DATABASE ... TEMPLATE, tested only there, so on another stack find the runner's worker id and the engine's way to copy a migrated database.

Settle reset, owner and source before adding workers

Question 1 picks the reset, question 2 the owner (a clone or a key per worker and job), and question 3 the source (SQLite or a container). Settle them in that order and stop at the cheapest answer that fits: a suite with one process writing needs no owner, and unique data per test is enough (the test data patterns skill catalogs ways to build it). Learn more in the Playwright parallelism docs.