Why are database tests the slowest part of a monolith test suite?
In a monolith, most tests eventually touch the database, and the cost rarely comes from the queries themselves. It comes from the work done around them. Common culprits are rebuilding or migrating the schema per test file, truncating every table after each test, re-seeding reference data, and running everything serially because every test shares one database and would otherwise trample its neighbors' rows. Each of those costs is small. Multiplied across thousands of tests on a single connection, they set the length of your CI run.
This guide is for backend and platform engineers whose suites fit that description. The technique has two parts. Each test runs inside a transaction that is rolled back at the end, and each parallel worker gets its own database. Together they remove most of the per-test cleanup and let you use more than one CPU core.
Measure before changing anything. Instrument the suite to separate four buckets:
- Setup: schema creation, fixtures, seeding.
- Teardown: truncation, deletes, dropping objects.
- Query time spent inside the test body.
- Waiting on locks, connection pools, or external services.
The approach pays off when setup and teardown dominate and the suite runs serially against a shared database. It is not worth the migration if the suite finishes in a few minutes, or if the profile shows time going to CPU-bound logic, browser automation, network calls, or application boot. Transactions and per-worker databases do nothing to reduce those costs.
How do transaction rollback tests work?
A transaction rollback test opens a transaction, or a savepoint inside one, before each test starts. The test runs all its queries on that same connection, and teardown issues a rollback, so nothing is ever committed. Rolling back a handful of rows is much cheaper than deleting or truncating them, and the next test sees exactly the state the fixtures set up.
The single-connection requirement
Uncommitted writes are visible only on the connection that made them. If the ORM or connection pool hands the code under test a different connection, that second connection runs in its own transaction. Under the usual isolation levels it will not see the fixture's rows. If it writes, it commits independently, and the rollback cannot undo that write. Neither case raises an error. You get puzzling "record not found" failures or data that leaks into later tests. The fix is to route every database access in the test, including the ORM session, the pool, and any raw driver calls, to the fixture's connection.
Nested transactions and commits
Application code often calls commit or opens its own transaction. The harness has to intercept these. A nested begin becomes SAVEPOINT, an inner commit becomes RELEASE SAVEPOINT, and an inner rollback becomes ROLLBACK TO SAVEPOINT. Frameworks and drivers handle this differently, and some silently ignore nested begins. Check your stack's behavior with a test instead of assuming.
Trade-offs
Forcing one connection hides concurrency bugs, because two "concurrent" operations are actually serialized on a single connection. Code that waits for a second connection, such as a lock held across sessions, can deadlock. Savepoint emulation also changes semantics. An inner "commit" is still discarded if the outer transaction rolls back, and an inner rollback undoes only part of the work instead of aborting the whole transaction.
import pytest
from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session
engine = create_engine("postgresql+psycopg://app:example_password@localhost:5432/app_test")
@pytest.fixture
def db_session():
# One connection per test. All queries in the test must run on it.
connection = engine.connect()
outer = connection.begin() # outer transaction, never committed
# create_savepoint: session.commit() releases a SAVEPOINT and
# session.rollback() rolls back to it. The outer transaction stays open.
session = Session(bind=connection, join_transaction_mode="create_savepoint")
try:
yield session # inject this session into the code under test
finally:
session.close()
outer.rollback() # discards everything, including inner "commits"
connection.close()
def test_inner_commit_is_only_a_savepoint(db_session):
# Checks the stack's behavior directly instead of assuming it.
db_session.execute(text("INSERT INTO users (email) VALUES ('a@example.com')"))
db_session.commit() # RELEASE SAVEPOINT, not a real COMMIT
q = text("SELECT count(*) FROM users WHERE email = 'a@example.com'")
assert db_session.execute(q).scalar() == 1 # visible on the fixture's connection
# A second pooled connection has its own transaction. It cannot see
# the uncommitted row. This is the silent failure the routing prevents.
with engine.connect() as other:
assert other.execute(q).scalar() == 0
A SQLAlchemy 2.0 pytest fixture binds the Session to one connection and maps inner commits to savepoints. The test checks that a commit releases only a savepoint and that a second pooled connection cannot see the uncommitted row.
Rollback vs. truncation vs. re-creation: what are the alternatives?
Those semantic shifts are the price of rollback, so it helps to compare it with the other ways of resetting state between tests.
| Strategy | Relative speed | What it cannot test | Production parity | Maintenance cost |
|---|---|---|---|---|
| Per-test rollback | Fastest: nothing is written durably | Real commits, multiple connections, commit-time constraints | High, if the engine matches | Low, once fixtures are transaction-aware |
| Truncation | Moderate: cost grows with table count, not data size | Little; data is really committed | High | Medium: table ordering, sequence resets, foreign keys |
| Full re-creation | Slowest: schema build or template copy per test or per file | Nothing extra | Highest | Low, but CI time is high |
| In-memory or different engine | Fast | Engine-specific SQL, locking, isolation levels, type coercion | Low | High: dialect workarounds pile up |
Use the same database engine as production. A substitute engine often passes tests that production would reject. Its type coercion may be looser, its constraint enforcement may differ, it may lack row-level locking, and its default isolation level may not match production. Each of these gaps eventually needs a conditional in application code or a skipped test. Running the real engine also costs less than it once did, because each test worker can get its own database cloned from a migrated template.
Keep truncation for the tests that rollback cannot serve. These include code paths that commit explicitly and work spread across several connections, such as background jobs or connection pools. They also include features that only fire at commit time, such as deferred constraints or after-commit hooks. Tag these tests and run them against a dedicated database. Truncate only the tables they touch, so the slow path stays small and the rollback suite keeps its speed.
Why doesn't rollback alone make parallel database tests safe?
A rolled-back transaction hides its rows from other sessions, but it still holds locks on them. When eight workers share one database, they collide in four ways:
- Unique constraints: two workers inserting
admin@example.comblock on each other's uncommitted index entry, because the second insert must wait to learn whether the first commits. - Row locks: tests that update shared seed rows, such as a default tenant or a settings record, serialize on those rows. Two tests that lock them in opposite order deadlock.
- Sequences: sequences are not transactional. Tests that assert on specific IDs pass alone and fail in parallel.
- Contention failures: these surface as timeouts and intermittent failures, not clean errors, so they look like flakiness.
Give each worker its own database
Per-worker isolation removes the shared state. Most runners expose a worker ID, for example PYTEST_XDIST_WORKER in pytest-xdist or JEST_WORKER_ID in Jest. Use it to pick a database or schema such as app_test_gw3.
Don't run migrations once per worker. Migrate one template database per run, then clone it. In PostgreSQL, CREATE DATABASE ... TEMPLATE copies files instead of replaying DDL, so each clone is usually much faster than a full migration.
To catch stale templates, hash the migration files and the schema dump, and store the hash in the template's name or in a metadata table. If the hash doesn't match, rebuild the template before cloning.
Resource costs
Each worker multiplies three resources:
- Connections: a pool of 5 per worker across 16 workers uses 80 of the server's
max_connections. - Disk: every clone is a full copy of the template.
- Memory: each backend process adds its own memory.
Past the point where CPU or disk I/O saturates, adding workers makes the run slower. Benchmark worker counts on your own CI hardware before picking a default.
# conftest.py: one migrated template per schema hash, cloned once per xdist worker
import hashlib, os, pathlib, subprocess
import psycopg
import pytest
from psycopg import sql
ADMIN_DSN = "postgresql://test:test@localhost:5432/postgres"
BASE_URL = "postgresql://test:test@localhost:5432/"
LOCK_KEY = 424242 # arbitrary advisory-lock key shared by all workers
def schema_hash():
# Any edit to a migration changes the template name, so stale templates are never reused.
h = hashlib.sha256()
for f in sorted(pathlib.Path("migrations").rglob("*.py")):
h.update(f.read_bytes())
return h.hexdigest()[:12]
@pytest.fixture(scope="session")
def database_url():
template = f"app_tpl_{schema_hash()}"
worker = os.environ.get("PYTEST_XDIST_WORKER", "gw0") # unset when run without xdist
db = f"app_test_{worker}"
ident = sql.Identifier
with psycopg.connect(ADMIN_DSN, autocommit=True) as conn: # CREATE DATABASE can't run in a transaction
# Serialize workers: the first one builds the template, the others wait, then clone.
conn.execute("SELECT pg_advisory_lock(%s)", (LOCK_KEY,))
try:
found = conn.execute(
"SELECT 1 FROM pg_database WHERE datname = %s", (template,)
).fetchone()
if not found:
conn.execute(sql.SQL("CREATE DATABASE {}").format(ident(template)))
# Migrate once per run; assumes migrations/env.py reads DATABASE_URL.
subprocess.run(["alembic", "upgrade", "head"], check=True,
env={**os.environ, "DATABASE_URL": BASE_URL + template})
# File-level copy, no DDL replay. Fails if any session is connected to the template.
conn.execute(sql.SQL("DROP DATABASE IF EXISTS {}").format(ident(db)))
conn.execute(sql.SQL("CREATE DATABASE {} TEMPLATE {}").format(ident(db), ident(template)))
finally:
conn.execute("SELECT pg_advisory_unlock(%s)", (LOCK_KEY,))
yield BASE_URL + db
A pytest-xdist session fixture that names the template after a hash of the migration files, builds and migrates it once under an advisory lock, and then clones it into a database for each worker with CREATE DATABASE ... TEMPLATE.
Which test isolation leaks survive a rolled-back transaction?
Rolling back a transaction undoes row changes. It does not undo every change a test can make, and the leftovers usually surface only when tests run in parallel or in a different order.
| Leak | Why rollback misses it | Typical engine behaviour |
|---|---|---|
| Sequences / auto-increment | Counters are advanced outside transaction control so concurrent inserts never block on them | PostgreSQL nextval and InnoDB auto-increment values are not returned on rollback |
| DDL | Some engines commit the open transaction before running DDL | MySQL implicitly commits on most DDL. PostgreSQL DDL is transactional, but a few commands refuse to run inside a transaction block |
| Session settings | Scoped to the connection, not the transaction | MySQL SET SESSION persists. In PostgreSQL, a plain SET issued before BEGIN persists |
| Temporary tables | Live for the session | Persist in MySQL. In PostgreSQL they are dropped on rollback only if they were created inside it |
| Advisory locks | Session-level locks belong to the connection | pg_advisory_lock and MySQL GET_LOCK stay held. pg_advisory_xact_lock is released |
The sequence row leads to a firm rule: tests must not assert on generated ID values. With several workers sharing a database, or with a reordered run, the "first" user will not have ID 1. Assert on the ID your insert returned, or on natural keys.
For connection-scoped state, reset each connection before it goes back to the pool or before the next test borrows it. PostgreSQL provides DISCARD ALL, which must run outside a transaction block. MySQL provides a connection-reset command at the protocol level. Use per-transaction variants where they exist, such as SET LOCAL and transaction-scoped advisory locks, so that rollback cleans them up.
-- PostgreSQL. Setup, run once:
-- CREATE TABLE users (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text UNIQUE NOT NULL);
-- 1. Identity/sequence values are not returned on rollback.
BEGIN;
INSERT INTO users (email) VALUES ('a@example.test') RETURNING id; -- assert on this returned value
ROLLBACK;
BEGIN;
INSERT INTO users (email) VALUES ('a@example.test') RETURNING id; -- higher id than before: never assert id = 1
ROLLBACK;
-- 2. Leaky: session-scoped state created outside the test transaction.
SET statement_timeout = '5s'; -- persists on this connection
SELECT pg_advisory_lock(42); -- held until unlocked or disconnected
CREATE TEMP TABLE scratch (n int); -- lives for the whole session
-- 3. Safe: transaction-scoped equivalents that ROLLBACK cleans up.
BEGIN;
SET LOCAL work_mem = '64MB'; -- reverts at end of transaction
SELECT pg_advisory_xact_lock(43); -- released at end of transaction
CREATE TEMP TABLE scratch_tx (n int) ON COMMIT DROP; -- created inside, so dropped on rollback too
ROLLBACK;
SHOW work_mem; -- back to the previous value
SHOW statement_timeout; -- still '5s': the leak from step 2
-- 4. Between tests, outside any transaction block, reset the connection.
-- Resets settings, drops temp tables, releases session advisory locks,
-- and deallocates prepared statements.
DISCARD ALL;
A PostgreSQL session showing that identity values, plain SET, session advisory locks and temp tables survive ROLLBACK, while SET LOCAL, pg_advisory_xact_lock and in-transaction temp tables do not, followed by DISCARD ALL to reset the connection between tests.
When should you avoid running a test inside a rollback transaction?
Some tests break the premise of rollback, and forcing them into it produces passes that mean nothing. Move these out:
- Code that uses its own connection. Threads, background job workers, and connection-pool checkouts outside the test's transaction cannot see uncommitted rows. The test either fails mysteriously or gets patched to run the job inline, which skips the code path you meant to test.
- Transaction and locking semantics. Retry-on-serialization-failure logic,
SELECT ... FOR UPDATEcontention, and isolation-level behavior need two real transactions racing. With one connection wrapped in a test-owned transaction, you only get savepoints, not concurrency. - After-commit callbacks and deferred constraints. Hooks that fire on commit never fire. Constraints declared
DEFERRABLE INITIALLY DEFERREDare checked at commit, so violations go unnoticed. - External systems. Queues, caches, and search indexes don't roll back. A test that enqueues a message or writes a cache entry leaves state behind for the next test.
The common thread is that a rollback-only suite never executes COMMIT. Commit is where deferred checks run, where callbacks fire, and where other connections start to see your writes. A smaller suite that commits for real is the only place those behaviors get tested.
Tag these tests with a marker in your test framework. The runner should exclude the tag from the parallel rollback pool and run tagged tests in their own group. That group either runs serially against a shared database or runs on per-worker databases cleaned by truncation between tests.
Track how long this group takes. Tagging a test is easier than fixing it, so the committed group tends to grow. Without review, it ends up as the slowest part of CI again.
# conftest.py: route tests to the rollback pool or the committed group
import os
import psycopg
import pytest
DSN = os.environ.get("TEST_DATABASE_URL", "postgresql://app:app@localhost:5432/app_test")
TABLES = ["orders", "users"] # tables the committed group may write to
def pytest_configure(config):
# Register the tag so typos fail loudly under --strict-markers
config.addinivalue_line("markers", "committed: commits for real; never runs in the rollback pool")
@pytest.fixture
def db(request):
conn = psycopg.connect(DSN)
if request.node.get_closest_marker("committed"):
# Real COMMITs: other connections see rows, after-commit hooks fire,
# and DEFERRABLE INITIALLY DEFERRED constraints are actually checked.
conn.autocommit = True
try:
yield conn
finally:
# Nothing to roll back, so wipe state for the next test
conn.execute(f"TRUNCATE {', '.join(TABLES)} RESTART IDENTITY CASCADE")
conn.close()
else:
# psycopg opens a transaction implicitly; rollback discards all writes
try:
yield conn
finally:
conn.rollback()
conn.close()
# In a test module:
# @pytest.mark.committed
# def test_worker_picks_up_order(db): ...
#
# CI runs the two groups separately (-n needs pytest-xdist):
# pytest -m "not committed" -n auto # parallel rollback pool
# pytest -m committed --durations=20 # serial; watch how long it gets
A pytest conftest that registers a committed marker, gives tagged tests real commits with TRUNCATE cleanup and everything else a rolled-back transaction, and runs the two groups as separate CI commands.
How much engineering time can parallel database tests save, and what does the migration cost?
Illustrative estimate (assumed numbers, not a benchmark). Assume 4,000 database tests, a 150 ms average test body, 250 ms of truncation per test, about 5 ms for a rollback, 8 workers, and 200 tests left in the serial group:
wall time ≈ (N_parallel × (t_body + t_cleanup)) / W + N_serial × (t_body + t_truncate)
| Setup | Calculation | Wall time |
|---|---|---|
| Serial, truncation | 4,000 × 0.40 s | ~26.7 min |
| Serial, rollback | 4,000 × 0.155 s | ~10.3 min |
| 8 workers, rollback, 200 serial | 3,800 × 0.155 / 8 + 200 × 0.40 | ~2.6 min |
The two changes compound. Rollback shrinks the per-test cost, and parallelism divides what remains. In the last row, though, the 200 serial tests take longer than the 3,800 parallel ones. Adding workers stops helping at whichever limit you reach first: CPU cores on the runner, connections and I/O on the database server, or the serial group. The serial group is often the one to watch.
The engineering cost comes at the start. When you turn on parallelism, every hidden order dependency in the suite shows up in the same week. Before parallelizing, run the suite in shuffled order with a fixed seed and log that seed in CI output, so any failure can be reproduced exactly. Treat each new flake as an isolation bug and fix it. Retrying hides the shared state that will break later.
A rollout that controls cost:
- Shuffle serially with a logged seed and fix the failures.
- Switch cleanup to rollback with one worker.
- Add per-worker databases and two workers.
- Scale workers until wall time stops dropping, and keep a ceiling on the size of the serial group.
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 database tests, 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.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
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.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- 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.