When do you roll back a transaction per test instead of truncating and reseeding the database?

### What rollback-per-test actually requires The pattern is simple to state: before each test the harness opens a transaction, the test runs, and afterwards the harness unwinds it. Nothing is committed, so there is nothing to clean up and the reset costs roughly what an unwind costs, typically a millisecond or two. The requirement hidden inside it is that **the test and the code under test share the same transaction and the same connection**. If the code checks out a second connection from a pool, its writes land outside the wrapper and survive the unwind. If it schedules work on a background worker, that worker cannot see the uncommitted rows and will behave as though the setup never happened. If a parallel worker or an external process is meant to observe the data, it never will. The moment any of these appear, rollback is not an isolation mechanism, it is a source of confusing failures. Nested transactions are the other subtlety. Code that opens its own transaction inside the wrapper usually joins the outer one, so the unwind still works. Code that demands a genuinely independent transaction, or that commits explicitly, does not, and its rows persist. ### What rollback hides Rollback tests the write, not the commit. Anything the engine defers until commit is invisible: deferred constraint checks, commit-time triggers, and any value the engine computes at commit. Worse, a production bug in which a half-finished unit of work would have been committed looks identical to a correct run, because the harness was going to throw the writes away either way. So a test that specifically asserts durability, ordering across commits, or the visibility of committed data to another reader must not use rollback. ### Truncate-and-reseed mechanics The alternative empties the tables between tests and puts the reference rows back. Four details decide whether it works. **Order or cascade.** Emptying a parent table while child rows still reference it is rejected, so you either truncate in dependency order, empty everything in one set-based operation the engine treats atomically, or briefly relax constraint checking around the reset and restore it afterwards. **Sequence counters.** Emptying a table does not by itself reset the identifier counter unless you ask for it. Deciding whether to restart them is a real choice: restarting makes runs reproducible, and not restarting keeps any assertion on a literal identifier honestly fragile. **Reference rows.** Emptying a lookup table the application cannot start without means the reseed step is mandatory, not optional, and it becomes part of the per-test cost. **The table list.** This is where suites rot. A hand-maintained list drifts from the schema: a migration adds a table for expiring loyalty points, nobody adds it to the list, and that one table is never emptied. Rows accumulate across the entire run until some later test counts them and fails, far away from whichever test wrote them, and the symptom reads as a schema-drift mismatch rather than as a cleanup gap. The fix is to derive the list from the live catalogue at run start, minus an explicit allowlist of reference and migration-bookkeeping tables, so a new table is covered the day it appears. ### The cost arithmetic The trade is speed for fidelity, and it is worth doing the arithmetic out loud rather than arguing from taste. A suite of 1,412 persistence tests whose reset empties 42 tables at roughly 34 ms per reset spends about 48 seconds on cleanup; the same suite under rollback spends a few seconds. In a 27-minute suite that difference is noise, and fidelity wins. In a suite people run on every save, it is not noise, and the answer changes. There is a third option worth naming: build a prepared schema once, then restore each test from that template rather than re-running the reseed. It costs more setup but turns the per-test reset into a copy, and it composes with per-worker isolation. ### Parallel workers Neither mechanism isolates two workers that share one schema. Rollback does not, because the workers are separate connections. Truncate is actively hostile, because one worker's reset empties tables the other is mid-test on. Parallelism needs a boundary of its own: a schema or database per worker, or per-run keys unique enough that workers never touch the same rows. Saying this unprompted is what separates a mid-level answer from a rehearsed one. ### Choosing per test, not per suite The mature answer is not a single policy. Use rollback for the wide base of narrow persistence tests that live entirely inside one transaction, and truncate-and-reseed for the smaller set that crosses a commit boundary, involves a second reader, or asserts commit-time behaviour. Making that choice explicit in the harness, rather than leaving each author to guess, is what keeps the fast path fast and the honest path honest.