Every Test Gets a Fresh Postgres Database: Fast Per-Test Isolation in SQLAlchemy

Every test in our engine suite runs against its own real Postgres database. A thousand tests means a thousand databases, each one created fresh and dropped when its test finishes.

That sounds slow and wasteful. It’s neither, because we never build a database from scratch while the tests run. We build one template up front and clone it once per test. The clone skips the slow part of standing up a database, so the suite stays fast enough that nobody thinks about it. Right up until the morning it started wedging our CI for twenty minutes at a time.

Here’s how the setup works, and the single Postgres rule that turned it into an intermittent outage.

Why a real database per test

A lot of our engine code is database code: queue state and the transactional bookkeeping that has to survive a crash halfway through a merge. You can’t test that against a mock. You need a real Postgres that commits, rolls back, enforces constraints, and behaves exactly like production.

The advice you’ll find in most SQLAlchemy tutorials is the transaction-rollback pattern: wrap each test in a transaction and roll it back when it finishes. Nothing ever lands on disk, so tests stay isolated and fast. SQLAlchemy even handles the case where your code calls commit(): set join_transaction_mode="create_savepoint" and each commit releases a savepoint instead of ending the real transaction, so the outer rollback still wipes everything.

It works, and it’s fast, but the database never actually reaches a COMMIT. You’re testing a savepoint release standing in for a commit, which is close, though not the real thing. For code whose entire job is getting commits and transaction boundaries right, that gap is where the bugs hide. Give each test its own database and there’s no outer transaction in the way, so the code commits for real, exactly as it does in production.

The other win is isolation. When a test’s database is dropped, nothing it did leaks into the next test, no leftover rows and no cleanup code to forget.

The part Django gives you for free

If you write Django, you already have this. pytest-django sets up a test database, clones it, reuses it between runs, and wraps the transactional cases for you. You inherit a decision someone already made.

SQLAlchemy hands you nothing here, and that’s on purpose. The ecosystem is deliberately unopinionated. Everyone pairs SQLAlchemy with a different web framework and a different app structure, so there’s no single blessed way to stand up a test database. The flexibility is real, and so is the bill: you own your test harness. We wrote ours.

Build the schema once, clone it a thousand times

The expensive part of standing up a database is building the schema. In a mature project that means replaying a long stack of migrations, and doing it before every test would be absurd. Multiply a few seconds of schema setup by every test that touches the database and your suite spends most of its life running migrations.

Postgres has a feature that skips it: CREATE DATABASE ... TEMPLATE. You point a new database at an existing one and Postgres copies its files directly, with no migrations to replay. The copy isn’t literally free (it scales with the size of the template), but a file copy beats a full schema build every time, and for our schema it’s cheap enough to forget about.

So the harness builds exactly one template per test process. A session-scoped fixture creates an empty database called postgres0, initializes SQLAlchemy, creates the schema once, and hands back the name:

@pytest.fixture(scope="session")
async def mock_postgres_db_value(worker_id):
    db_name = f"postgres{_get_worker_id_as_int(worker_id)}"
    await database_utils.setup_postgres_database(db_name)
    with mock.patch.object(settings, "DATABASE_URL", ...):
        database.init_sqlalchemy()
        await manage.create_all()   # the one time we build the schema
        yield db_name

Every test then clones that template into a throwaway database with a random name:

async def create_database(db_url, db_name, template=None):
    engine = create_async_engine(db_url)
    async with engine.execution_options(isolation_level="AUTOCOMMIT").connect() as conn:
        await conn.execute(text(f"DROP DATABASE IF EXISTS {db_name}"))
        template_cmd = f" TEMPLATE {template}" if template else ""
        await conn.execute(text(f"CREATE DATABASE {db_name}{template_cmd}"))

The schema build runs once, and every test after that just clones it. After living with this setup, I’d recommend it to anyone standing up a SQLAlchemy test suite from scratch.

One caveat if you parallelize: CREATE DATABASE ... TEMPLATE takes a lock on the source, so two workers can’t clone the same template at once. Give each worker its own template (what we do, one per process), or chain the clones so each worker copies the source once and every test then clones from the worker’s own copy.

The rule that bit us

CREATE DATABASE ... TEMPLATE has one hard requirement: the template must have zero other connections at the moment you clone it. If anything is still attached, Postgres refuses:

ERROR:  source database "postgres0" is being accessed by other users
DETAIL:  There are 6 other sessions using the database.
STATEMENT:  CREATE DATABASE postgres<hex> TEMPLATE postgres0

We knew this from day one. Our teardown path was already careful about it: before dropping a test database, it evicts any leftover sessions so the DROP can’t be blocked. What we missed for a long time was that the clone path had no such guard. Teardown defended against stray connections. Creation trusted that there were none.

For most tests that trust is fine, because they clean up after themselves. Then we added a few tests that play with connection lifetimes on purpose, including one that triggers a statement-timeout cancellation. A cancelled query can leave a connection checked out and stranded on the template. Not always. Just often enough.

When it happened, the effect was brutal and quiet. A shard would strand a connection on postgres0, and from that point every clone in that process failed with the error above. The shard retried the clone every five seconds and made no progress, until the 20-minute job timeout killed it partway through, usually somewhere between 40 and 80 percent of its tests. At its worst, about a quarter of our merge-queue runs were failing this way, each time on a different shard.

The worst part was where the evidence lived. That ERROR line came from the Postgres container’s stderr, not from pytest’s output, so it never showed up when anyone searched the job logs. All you saw was a test run that stopped making progress and got killed. The timeout was the executioner. The stranded connection was the actual defect, and it was hiding one layer down.

sequenceDiagram
    participant T as A leaky test
    participant PG as Postgres
    participant S as Every later test
    T->>PG: open a connection on postgres0
    T->>PG: statement-timeout cancellation
    Note over T,PG: connection stranded, never returned
    S->>PG: CREATE DATABASE ... TEMPLATE postgres0
    PG-->>S: ERROR: source database is being accessed by other users
    loop every ~5s until the 20-min timeout
        S->>PG: retry clone
        PG-->>S: ERROR: still 6 other sessions
    end
    Note over S: job killed, shard reported as failed

It was also maddeningly intermittent, which is the signature of a race you have to lose. A connection has to be attached to the template at the exact instant a clone fires. Our test splitter made the odds worse: without a durations cache it falls back to an even-by-count split, so which tests land in a shard, and in what order, shifts from run to run. Some orderings put the leaky test right before a clone. Most didn’t.

The fix: make creation as paranoid as teardown

The obvious instinct is to hunt down the leaking test and fix it. (Bumping the 20-minute timeout crossed our minds too, but a wedged shard makes zero progress, so a longer timeout only postpones the same death.) We did eventually trace the leak to a single timeout test, but we didn’t make that the fix, and I’d argue you shouldn’t either.

Race conditions around connection lifetime are hard to reproduce and easy to reintroduce. Chase down today’s leak and someone adds a new one next quarter, and you’re back to a shard dying at minute twenty with the evidence buried in a container log. Betting your CI’s stability on every future test being perfectly disciplined about connections is a bad bet.

So we hardened the infrastructure instead. The clone path now evicts other sessions from the template before cloning, exactly the way teardown already did before dropping. Both paths call the same helper, so they can’t drift apart later:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = :db_name AND pid <> pg_backend_pid();

A regression test holds a live session open on a template and asserts that the clone still succeeds. Rip out the eviction and it fails with the exact ObjectInUse error from production, which is how you know the test is testing something real.

You could call terminating sessions “papering over the bug,” and I understand the reflex. My answer is that the clone requiring zero connections is a documented Postgres constraint, and our teardown path had already been treating it as one for years. All we did was make the two paths agree. The leaky test is still worth cleaning up, and we’re tracking it, but the CI no longer depends on that cleanup happening.

What I’d tell you

Cloning a template database per test is completely worth it. You get a real Postgres for every test (one that actually commits, instead of a transaction you roll back), and the schema cost you’d normally pay a thousand times you pay exactly once.

The one rule to internalize is that nothing may hold a connection to the template when you clone it. The Postgres docs say it plainly, and we learned it the loud way. Enforce it on the clone path itself, not only on teardown, because the failure mode when you don’t is an intermittent, silent death after twenty minutes, with the real error hiding in a log nobody greps.

And if you’re on SQLAlchemy, accept that this is your job to build. Nobody is going to hand you pytest-django. The upside of an unopinionated toolkit is that you get to make these calls yourself. The downside is that you have to.