Your Test Suite Is Slow Because It Tests Postgres

I quit running the test suite on my laptop. Not deliberately. I just stopped typing the command, because by the time it finished I had lost the thread of whatever I was fixing. Standup would end, I would push, and wait for CI to tell me what I broke. That is the real cost of a slow suite: not the wall-clock time, but the gap between writing a line and knowing whether it works.

Ours was slow for a boring reason. Every test opened its own connection, applied the migrations, and talked to a real Postgres instance. A test that checked whether an email address was normalized still paid for a connection, because the fixture chain handed it a session it never used.

Find out what the tests are actually waiting on

The number to measure is not total runtime. It is per-test setup cost. Time each fixture, or wrap connection setup in a timer and log it, then sort the results. What you want is the ratio between time spent arranging infrastructure and time spent running the code under test. If setup dominates, the suite is measuring Postgres, not your logic. pytest --durations=20 names the slowest tests, but add your own fixture timing too, because the fixture is where the money goes.

Then read each test and ask one question: does this test fail if the SQL is wrong? Not "does it touch a table" — does it assert something only a database can tell you.

Which tests actually need a database

A test needs Postgres if it exercises one of three things:

• a query — the WHERE clause, a JOIN, an index assumption, an ORDER BY that depends on collation
• a constraint — a unique index, a foreign key, a check constraint, a NOT NULL
• a transaction boundary — rollback on error, isolation, locking, a deferred constraint

Everything else is using the database for convenience. If a test loads a row through a repository, calls a function on it, and asserts on the return value, the database was an expensive way to construct an object. That test wants a plain object.

The line is not "unit versus integration" by file layout. A test that calls UserRepository.get(id) against a real connection is an integration test even when it lives in tests/unit/ and never starts an HTTP client. Naming a directory does not change what the process does.

Shared schema, transaction per test

The fix that buys back most of the time is not deleting the database. It is applying the schema once and isolating each test in a transaction that rolls back.

@pytest.fixture(scope="session")
def engine():
    engine = create_engine(TEST_DATABASE_URL)
    Base.metadata.create_all(engine)  # migrations run once per session
    yield engine
    engine.dispose()

@pytest.fixture
def session(engine):
    connection = engine.connect()
    transaction = connection.begin()
    session = Session(bind=connection, join_transaction_mode="create_savepoint")
    yield session
    session.close()
    transaction.rollback()  # nothing survives the test
    connection.close()

The schema is built once. Each test gets a connection and a transaction, and rollback is the cleanup. No truncate loop between tests, no migrations re-run per test, no ordering dependencies leaking through leftover rows. Tests that need to observe a real commit opt out with an explicit marker, so the expensive path is visible in the file instead of hidden in a fixture.

One caveat: code that opens its own connection will not see the test's uncommitted rows. Make the session injectable, or move those tests into the small set that runs against a real server. Do not paper over it with a shared connection.

The tests that genuinely need Postgres

Keep them. Do not mock the query planner. A small set of tests that hit real Postgres and assert on real SQL behavior is worth more than a pile of tests that pretend, but it should be a set you can name, not the default for everything in the suite.

Mark them (@pytest.mark.postgres), run them on every push in CI, and keep the fast tier on every save locally. The default command runs the fast tests; the database tests are one flag away. When they fail, the failure means something about the SQL, which is exactly the signal you wanted from the database in the first place.

Calling something a unit test does not make it fast, and importing a repository does not make it an integration test. What matters is whether a real database is the only thing that can fail.

---

I write about production failures in Postgres, queues, and distributed systems.

Subscribe by email · RSS · Bluesky