Recommended way to reset PostgreSQL state between test classes with a shared static container

[codicebot]

I have a suite of a few hundred integration tests that all run against one PostgreSQL container, started once for the whole run because per-class startup was too slow. Schema is created once at the start. The tests mutate data, so I need a clean slate between classes.

static final PostgreSQLContainer<?> POSTGRES =
    new PostgreSQLContainer<>("postgres:16-alpine")
        .withDatabaseName("app");

static { POSTGRES.start(); }

Two things I've tried:

TRUNCATE ... RESTART IDENTITY CASCADE over every table after each class. Correct, but it's the dominant cost of the run once the schema grows.

Wrapping each test in a transaction and rolling back. Much faster, but it breaks any test where the code under test commits on its own, which is a lot of them here.

The third option I keep coming back to is keeping a pristine database inside the container and doing CREATE DATABASE test_x TEMPLATE app_pristine per class, since that's near-instant in Postgres. Is that a pattern Testcontainers supports or recommends, or am I fighting the framework at that point? I couldn't find anything in the docs beyond withReuse, which seems to be about reuse across JVM runs rather than resetting.

[thomassolcia]

There's nothing in Testcontainers at that layer, so I wasn't missing an API. withReuse is about reusing across JVM runs, not resetting.

The template route works:

CREATE DATABASE test_cls_42 TEMPLATE app_pristine;

Two things that bit me: Postgres refuses to copy a template with any open connection, so the pool must never touch app_pristine, and the previous test database needs pg_terminate_backend before it can be dropped.

What actually mattered more was taking the disk out of the equation. With PGDATA on tmpfs and durability off, the TRUNCATE I was complaining about got cheap enough that the template trick stopped being worth the complexity:

.withTmpFs(Map.of("/var/lib/postgresql/data", "rw"))
.withCommand("postgres", "-c", "fsync=off",
    "-c", "full_page_writes=off", "-c", "synchronous_commit=off")

A test database doesn't need to survive a power cut.