What is Testcontainers Postgres testing and why use a real database in a monolith?
Testcontainers Postgres testing means your test suite starts a throwaway Postgres server in a container. It points the application's real data-access code at that server and throws the container away when the run ends. The database is the same engine you ship to production, not a stand-in. Testcontainers handles the lifecycle: pulling the image, mapping a free port, waiting for readiness and cleaning up.
A monolith usually leans hard on database behaviour, and fakes get exactly that behaviour wrong:
- Constraints and deferred checks. A fake may accept rows that Postgres would reject.
- JSONB operators and indexes. These may not exist in a substitute engine at all.
- Row locking.
SELECT ... FOR UPDATE SKIP LOCKEDonly behaves correctly against the real engine. - SQL dialect quirks. Examples include
ON CONFLICT, how sequences behave after a rollback, and collation.
When a large shared schema is owned by many teams, a test that passes on a fake and fails in production is expensive. The engineers who benefit most are the backend and platform engineers who own that schema and its migrations.
The prerequisite is a Docker-compatible runtime. That can be a problem on CI runners without container support, on locked-down laptops, and in nested-container setups such as CI jobs that already run inside a container.
| Approach | Production fidelity | Isolation | Main drawback |
|---|---|---|---|
| Mocks | None | Perfect | Tests your assumptions, not SQL |
| In-memory/embedded DB | Low–partial | Good | Dialect and feature gaps |
| Long-lived shared test server | High | Poor | State leaks between runs and teams |
| Locally installed Postgres | High | Manual | Version drift across machines |
| Testcontainers Postgres | High | Good | Needs a container runtime |
Who should use Testcontainers Postgres?
Use it if your application's correctness depends on Postgres behaviour rather than on generic SQL. That covers most monoliths with a long-lived schema. The approach fits well when:
- Your code uses Postgres-specific features. Examples include JSONB, arrays,
ON CONFLICT, partial or expression indexes, advisory locks,LISTEN/NOTIFY, row-level locking, triggers and deferred constraints. - Migrations change often and are risky. Running the real migration tool against a real server in every test run catches broken migrations before deploy.
- Many teams share one schema. A reproducible, disposable database removes "works on my machine" differences and stops teams from stepping on each other in a shared test server.
- Developers and CI can run containers. Local machines and CI runners need a working Docker-compatible runtime.
It is a poor fit, or should be used sparingly, when:
- The logic does not touch the database. Pure domain logic belongs in unit tests that run in milliseconds without a container.
- No container runtime is available. Some CI environments and locked-down machines cannot run containers, or can only do so with nested-container workarounds.
- The suite is already dominated by slow end-to-end tests. Adding a real database will not fix that. Rebalance the test pyramid first.
A practical split for a monolith is to keep the bulk of business-rule tests as fast unit tests. Use Testcontainers Postgres for repository, query, migration and transaction-boundary tests, where database behaviour is the thing under test.
How does Testcontainers Postgres work?
Testcontainers is a library you call from test code. It drives a container runtime through that runtime's API. For a Postgres container, a run goes through these steps:
- Resolve the image. The library checks whether the requested image, such as
postgres:16, is present locally and pulls it if it is not. - Create and start the container. It passes environment variables that the official Postgres image reads on first start to create the user, password and database. It maps the container's port 5432 to a random free port on the host, so parallel runs and a locally installed Postgres do not collide.
- Wait for readiness. A wait strategy blocks until Postgres actually accepts connections, not merely until the container process exists. The Postgres modules do this by watching the container log for the server's "ready to accept connections" message, or by attempting a connection. Without this step, tests race the server's startup and fail intermittently.
- Expose connection details. The test asks the container object for the host, the mapped port and the JDBC or connection URL. Use those values instead of hard-coding
localhost:5432. - Run your code. Migrations and tests connect over that mapped port exactly as the application would connect to any Postgres server.
- Clean up. When the container object is closed, for example when a
withblock ends, a fixture tears down or a JUnit extension finishes, the library stops and removes the container. Testcontainers also starts a small companion reaper container. If the test process is killed before it can clean up, the reaper removes the containers, networks and volumes the session created.
Everything inside the container is a normal Postgres server. You can connect to it with psql while a test is paused, run EXPLAIN against it, or create extra databases in it. Later sections rely on that last point.
What are the alternatives to Testcontainers Postgres?
Each alternative trades production fidelity, isolation and setup cost differently. The comparison table above summarises them. In more detail:
- Mocks or fake repositories. You replace the data-access layer with in-process fakes. These are fast and need no infrastructure, but they never execute SQL. They are good for business logic and useless for verifying queries, constraints or migrations.
- In-memory or embedded SQL engines. You run a different database engine in-process, sometimes with a Postgres compatibility mode. Startup is fast, but compatibility modes cover only a subset of the dialect. JSONB, locking semantics, extensions and many functions behave differently or are missing, so passing tests prove less than they appear to.
- Embedded Postgres binaries. Some libraries download and launch real Postgres binaries as a child process without a container runtime. You get the real engine and avoid the container dependency. In exchange, you depend on platform-specific binaries and get less control over the exact image you deploy.
- CI-provided service containers or a compose file. The CI system or a local compose setup starts Postgres before the tests run. This gives the same fidelity as Testcontainers. However, the database lifecycle lives outside the test code, so local and CI setups can diverge, and per-run isolation is up to you.
- A long-lived shared test server. One Postgres instance serves every developer and pipeline. There is no startup cost, but state leaks between runs and teams, and concurrent schema changes collide.
- A locally installed Postgres. Each developer runs Postgres natively. It is fast and real, but versions, extensions and configuration drift between machines and away from production.
Testcontainers is the right choice when you want the real engine, a database lifecycle defined in the test code itself, and identical behaviour on laptops and CI, and you can accept the container runtime dependency. If you cannot run containers, embedded Postgres binaries or CI service containers are the closest substitutes in fidelity.
Where does integration test suite time actually go?
Each integration test run spends time in five phases. They do not scale the same way, so it helps to separate them:
- Image pull: downloading the Postgres image. This is paid once per machine or CI runner and is skipped when the image is already cached.
- Container start: creating the container and waiting until Postgres accepts connections.
- Migrations: applying the schema. In a mature monolith this often takes longer than starting the container.
- Per-test setup: isolation work such as opening a transaction, truncating tables or seeding fixtures.
- Test bodies: the queries and assertions you actually want to run.
Total time follows this formula:
T = pull + containers × (start + migrate) + tests × (setup + body)
Illustrative estimate (assumed numbers, not a benchmark). Assume 400 tests, a 30 s image pull, 4 s container start, 6 s of migrations, 0.05 s per-test setup and 0.1 s per test body.
| Strategy | Containers | Calculation | Total |
|---|---|---|---|
| Container per test | 400 | 30 + 400 × (4 + 6) + 400 × 0.15 | ~4,090 s (~68 min) |
| One container per run | 1 | 30 + 1 × (4 + 6) + 400 × 0.15 | ~100 s |
With these assumed inputs, startup and migrations account for almost all of the per-test-container cost. When you share a container, the remaining time comes mostly from per-test setup and test bodies.
Your numbers will be different. Before you optimise anything, time each phase in your own suite. Log timestamps around container startup, around the migration step and around the whole run. Then subtract to get the time spent in tests, and spend your effort on the largest term.
How do you start one shared Postgres container per test run?
Startup cost is usually the easiest win, and it depends on how often you create a container.
| Lifecycle | Startups per run | Isolation from the container | Main cost |
|---|---|---|---|
| Per test | One per test | Complete | Startup and migrations repeated hundreds of times |
| Per class or file | One per file | Strong | Still scales with file count; migrations rerun per file |
| Per test process | One | None, so you must add your own | Tests must clean up or use separate databases |
For a monolith with a large schema, one container per test process is usually the right default. Startup and migrations become a fixed cost instead of one that grows with every test or file you add. You then handle isolation inside Postgres, which is cheaper than isolation at the container level.
Start the container in a hook that runs once, before any test executes:
- a lazily initialised singleton in JUnit
- a session-scoped fixture in pytest
TestMainin Go- a global setup file in JavaScript runners
Expose the connection details to tests through environment variables or a shared accessor.
Pin the image to the Postgres major version you run in production, such as postgres:<major>, not latest. Planner behaviour, defaults and SQL features differ between major versions. Pre-pull the image or cache it in CI so the pull doesn't count against every run.
Next, run migrations once against a dedicated database using your production migration tool. If you hand-roll the schema for tests, it will drift from what you deploy. That migrated database then becomes the source for per-test or per-worker copies.
test process
└─ global setup ──► Postgres container (pinned major)
├─ app_template (migrated once)
├─ test_w1 ◄─ copy of template
├─ test_w2 ◄─ copy of template
└─ test_wN ◄─ copy of template
import os
import subprocess
import pytest
from testcontainers.postgres import PostgresContainer
PG_IMAGE = "postgres:16"
@pytest.fixture(scope="session", autouse=True)
def pg_container():
# Session scope + autouse: started once, before the first test runs.
with PostgresContainer(
PG_IMAGE,
username="test",
password="test", # placeholder, local-only credentials
dbname="app_template", # migrated once, then copied per test/worker
) as container:
url = container.get_connection_url()
# Run the production migration tool once against the template DB.
# Assumes the project's alembic env.py reads DATABASE_URL.
subprocess.run(
["alembic", "upgrade", "head"],
env={**os.environ, "DATABASE_URL": url},
check=True,
)
# Expose connection details to tests and helpers.
os.environ["TEST_TEMPLATE_DATABASE_URL"] = url
yield container
# Leaving the with-block stops and removes the container.
A session-scoped, autouse pytest fixture that starts one pinned Postgres container per test process, runs Alembic migrations once into a template database, and exposes its URL to tests (under pytest-xdist each worker is its own process, so you get one container per worker).
How do you isolate each test: transactions, truncation or template databases?
Each strategy resets state at a different layer, so each one breaks in a different way.
Transaction rollback. Open a transaction in beforeEach, hand that connection to the code under test, and issue ROLLBACK in afterEach. Nothing is ever committed, so cleanup is close to free. Sequences are the catch. nextval() is never rolled back, so a test that asserts id = 1 passes alone and fails after any earlier insert. Assert on returned IDs, not literal ones.
Truncation. Let tests commit normally, then run a single TRUNCATE orders, customers, ... RESTART IDENTITY CASCADE. Listing every table in one statement avoids foreign-key ordering problems. RESTART IDENTITY resets sequences. Exclude seed and lookup tables, or re-insert them afterwards. The cost scales with the number of tables, not the number of rows, so a monolith with hundreds of tables pays a noticeable fixed price on every test.
Template cloning. Run CREATE DATABASE test_x TEMPLATE app_template to get a fresh database for each test or file. Postgres rejects the command if any session is connected to the template, so close migration connections and keep pools away from it. The clone copies files, so each copy gets slower as the template grows.
| Strategy | Per-test speed | Realism | Typical failure mode |
|---|---|---|---|
| Transaction rollback | Fastest | Lowest: no real commits | Brittle ID assertions; code that opens its own connection or transaction |
| Truncation | Slows as table count grows | High | Lost seed data; a missed table leaks state |
| Template clone | Slows as template size grows | Highest | "Source database is being accessed by other users" |
-- 1) Transaction rollback: data is discarded, sequence advances are not
BEGIN;
INSERT INTO customers (name) VALUES ('Ada') RETURNING id; -- say id = 1
ROLLBACK;
INSERT INTO customers (name) VALUES ('Ada') RETURNING id; -- now id = 2, not 1
-- 2) Truncation: one statement for all mutable tables avoids FK ordering issues.
-- RESTART IDENTITY resets their sequences. Seed and lookup tables such as
-- countries are left out of the list so their rows survive.
TRUNCATE orders, order_items, customers RESTART IDENTITY CASCADE;
-- 3) Template cloning: run once after migrations, connected to another
-- database such as postgres. Block new sessions on the template.
ALTER DATABASE app_template WITH ALLOW_CONNECTIONS false;
-- Close leftover sessions (for example idle migration connections),
-- otherwise CREATE DATABASE fails with
-- "source database is being accessed by other users"
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'app_template' AND pid <> pg_backend_pid();
-- Per test or per file. This statement cannot run inside a transaction block.
-- The copy time grows with the template's on-disk size.
CREATE DATABASE test_orders_1 TEMPLATE app_template;
-- After the test, close the pool connected to test_orders_1, then:
DROP DATABASE test_orders_1;
Shows the SQL behind each isolation strategy: a rollback that leaves the sequence advanced, a single TRUNCATE ... RESTART IDENTITY CASCADE over the mutable tables, and cloning a migrated template after closing every connection to it.
When should you avoid transaction-rollback isolation?
Rollback isolation never commits, so anything Postgres does at commit goes untested. Deferred constraints (DEFERRABLE INITIALLY DEFERRED foreign keys, unique and exclusion constraints) are checked only at COMMIT, so a violating write passes the test and fails in production. Constraint triggers declared as deferred are skipped the same way. NOTIFY messages are delivered only when the sending transaction commits, so listener tests receive nothing.
Code that opens its own connection is also affected: a background worker, a connection pool used outside the request scope, or a migration helper. It cannot see the test's uncommitted rows, and it may block on the test's locks. Code that calls COMMIT itself ends the test's outer transaction early.
Workarounds
- Savepoints: many frameworks map the application's
BEGIN/COMMITtoSAVEPOINT/RELEASEinside the outer test transaction. This keeps nested transaction logic working, but commit-time checks still never run. - One shared connection: inject the test's connection into the code under test so every query sees the same transaction.
- Opt out per test: tag commit-dependent tests and run them against truncation or a template clone instead.
Test pollution
Rollback does not undo everything. Sequences advance regardless of rollback. A plain SET outlives the transaction, unlike SET LOCAL. Any test that commits leaves rows behind. A later test that asserts an ID or a row count then passes or fails depending on what ran before it. To surface this, randomise test order in CI and log the seed so failures can be replayed. Run DISCARD ALL when a pooled connection is returned to reset session state, and assert that key tables are empty after the suite finishes.
-- Run in psql against the test container to see what rollback isolation hides.
CREATE TABLE parent (id int PRIMARY KEY);
CREATE TABLE child (
id serial PRIMARY KEY,
parent_id int REFERENCES parent (id) DEFERRABLE INITIALLY DEFERRED
);
-- 1. Typical rollback-isolated test: the bad write looks fine.
BEGIN;
INSERT INTO child (parent_id) VALUES (42); -- no parent 42, but no error yet
SELECT count(*) FROM child; -- returns 1, so the assertion passes
ROLLBACK; -- the FK is never checked
-- 2. Workaround: force deferred checks before the test rolls back.
BEGIN;
INSERT INTO child (parent_id) VALUES (42);
SET CONSTRAINTS ALL IMMEDIATE; -- raises the FK violation now, as COMMIT would
ROLLBACK;
-- 3. NOTIFY is only delivered on commit, so a rolled-back test sends nothing.
LISTEN order_events;
BEGIN;
NOTIFY order_events, 'created';
ROLLBACK; -- no notification is delivered
-- 4. Pollution: sequences are not transactional.
SELECT last_value FROM child_id_seq; -- advanced by both rolled-back inserts
-- 5. Pollution: SET outside a transaction persists for the session.
SET statement_timeout = '50ms'; -- e.g. leaked from one test's setup
SHOW statement_timeout; -- the next test on this connection inherits it
-- Reset session state (settings, LISTENs, prepared statements, temp tables)
-- when the pool takes a connection back. Must run outside a transaction block.
DISCARD ALL;
SHOW statement_timeout; -- back to the server default
A psql session showing how rollback isolation skips deferred foreign-key checks and NOTIFY delivery, how SET CONSTRAINTS ALL IMMEDIATE surfaces the violation early, and how sequences and session settings leak between tests until DISCARD ALL resets the connection.
How do you run Postgres integration tests in parallel safely?
Parallel workers multiply contention, so isolate them at the database level rather than starting a container per worker. One Postgres server can host many databases cheaply. Each extra container repeats startup, image extraction and migrations, which is the cost the shared container was meant to avoid.
Derive the database name from the worker
Most runners expose a stable worker identifier. pytest-xdist sets PYTEST_XDIST_WORKER (gw0, gw1, …), Jest sets JEST_WORKER_ID, and Gradle exposes org.gradle.test.worker. Build the name from it, for example app_test_gw3, so it is deterministic. A worker that crashes and restarts then reuses its own database instead of leaking a new one. If you also run several CI jobs against a shared server, include the run ID in the name so jobs cannot collide. Schema-per-worker within one database also works if your code honours search_path, but extensions and other global objects are shared, which makes failures harder to diagnose.
Clone without template conflicts
CREATE DATABASE … TEMPLATE app_template fails if any other session is connected to the template. Close the migration connection before cloning, and then run ALTER DATABASE app_template ALLOW_CONNECTIONS false so a stray pool cannot connect to it. Clone all worker databases once, in run-level setup, before the workers start. If workers must clone their own databases, serialise the cloning with an advisory lock on a separate maintenance database.
Watch shared limits
All workers draw on the same server. Multiply your pool size by the number of workers and compare the result with max_connections. With the default of 100, eight workers using 15-connection pools would exceed the limit. Every connection uses memory, and work_mem applies to each sort or hash operation, not to each session. Docker's default /dev/shm is small, which can cause "could not resize shared memory segment" errors under parallel queries. Raise the shared memory size or turn off parallel query in tests.
import os
import re
import psycopg
import pytest
from psycopg import sql
from psycopg.conninfo import make_conninfo
ADMIN_DSN = os.environ.get("TEST_PG_ADMIN_DSN", "postgresql://test:test@localhost:5432/postgres")
CLONE_LOCK_KEY = 815001 # arbitrary app-wide advisory lock key
def worker_db_name() -> str:
# Deterministic: a restarted gw3 reuses app_test_<run>_gw3 instead of leaking a new db.
worker = os.environ.get("PYTEST_XDIST_WORKER", "main")
run_id = os.environ.get("CI_RUN_ID", "local") # keeps concurrent CI jobs apart
name = re.sub(r"[^a-z0-9_]", "_", f"app_test_{run_id}_{worker}".lower())
return name[:63] # Postgres truncates identifiers longer than 63 bytes
@pytest.fixture(scope="session")
def database_url():
name = sql.Identifier(worker_db_name())
# CREATE DATABASE cannot run inside a transaction block, so use autocommit.
# We connect to 'postgres', never to app_template, so we never block the clone.
with psycopg.connect(ADMIN_DSN, autocommit=True) as conn:
# Serialise clones: concurrent CREATE ... TEMPLATE calls on one template can conflict.
conn.execute("SELECT pg_advisory_lock(%s)", (CLONE_LOCK_KEY,))
try:
conn.execute(sql.SQL("DROP DATABASE IF EXISTS {}").format(name))
# Works even when app_template has ALLOW_CONNECTIONS false.
conn.execute(sql.SQL("CREATE DATABASE {} TEMPLATE app_template").format(name))
finally:
conn.execute("SELECT pg_advisory_unlock(%s)", (CLONE_LOCK_KEY,))
# Keep per-worker pools small: workers x pool size must stay under max_connections.
yield make_conninfo(ADMIN_DSN, dbname=worker_db_name())
A pytest conftest fixture that derives a deterministic database name from PYTEST_XDIST_WORKER and a run ID, then clones it from app_template while holding an advisory lock taken on the postgres maintenance database.
How can test database reuse and Postgres tuning save engineering time?
Reuse mode keeps the container alive between local runs, so you skip container startup and migrations on every edit-and-run cycle. It requires opt-in in two places: the container definition must request reuse (in Java, withReuse(true)), and the developer's machine must enable it in ~/.testcontainers.properties with testcontainers.reuse.enable=true. If either is missing, Testcontainers starts a fresh container as usual.
Reuse has costs:
- Containers keep running. They hold memory and ports until someone stops them.
- Schema drift. When a migration changes, the reused database can still hold the old schema. Tests may pass or fail for reasons that have nothing to do with your change.
- Local configuration. Results now depend on a file outside the repository.
Keep reuse off in CI. A pipeline should start from a clean, reproducible state, and reuse would bring back the shared-state problems that isolation was meant to remove.
For throwaway databases, set fsync=off, synchronous_commit=off, and full_page_writes=off, and put the data directory on tmpfs. Together these remove most disk flush and write overhead. These settings are unsafe for real data. A crash can corrupt the cluster. They also make tests less like production, so durability-related behavior is no longer exercised. Never reuse an image or configuration built this way for anything real.
Illustrative estimate (assumed numbers, not a benchmark): daily time saved = runs per day × seconds saved per run × engineers. With 30 runs × 20 s × 10 engineers, that is 6,000 s, or about 100 minutes per day. Faster feedback also means fewer flaky reruns. Budget time to maintain the reuse setup and to clear drifted containers.
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 testcontainers postgres, 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.
- Changing a shared variable is one edit and one review instead of editing every service's
.envby hand.
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.
- A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
- 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.