Reset a test database by running each test inside a transaction you roll back, by truncating tables between runs, or by starting a throwaway database container per run. Choose the method that matches your isolation needs, then confirm that nothing outlives the job, including sequences, caches and files.
Why reset matters for data handling
Leftover rows are the quiet way test data lingers. A shared staging database that is never reset collects records from every run, including any real values someone pasted in to reproduce a bug. A strict reset routine caps how long any record can exist and makes deletion a default rather than a chore.
The GDPR principle on storage limitation asks that identifiable data be kept no longer than necessary for its purposes. This is general information, and your counsel should interpret it for your case.
Three approaches compared
| Approach | How it works | Strength | Watch for |
|---|---|---|---|
| Transaction rollback | Each test opens a transaction and rolls back at the end | Fast, nothing committed | Code that commits on its own, or uses a second connection |
| Truncate between tests | TRUNCATE empties tables | Simple, covers committed data | Order of tables, foreign keys, identity counters |
| Throwaway container | A fresh database instance per run | Cleanest, nothing shared | Startup time, image contents |
The PostgreSQL documentation states that TRUNCATE is transaction safe with respect to table data: the truncation is rolled back if the surrounding transaction does not commit. It also says that with RESTART IDENTITY the sequence restarts are done transactionally too, though it warns about side effects on sequence functions inside a failed transaction. That makes truncation inside a transaction a workable pattern for suites that need a clean slate.
For the container route, the Testcontainers project describes providing lightweight, throwaway instances of common databases for tests. When the container stops, the data goes with it, provided you did not mount a persistent volume.
Steps for a reliable reset
- Decide the unit. Reset per test, per suite or per pipeline run. Per run is a sensible floor.
- Load from the seed script. After the reset, load the known fake dataset. Never restore a production dump as the baseline.
- Cover all stores. Reset caches, search indexes, message queues and object storage buckets used by tests, not just the SQL database.
- Avoid persistent volumes for CI databases unless you have a reason. If you need one, schedule a wipe.
- Fail on leftovers. After the suite, run a check that counts rows in key tables. A nonzero count in a table that should be empty is a failed build.
- Log the reset. Record job identifier, time and the outcome, so you can show it later.
Shared staging is different
A long lived staging environment cannot reset after every test. Give it a refresh schedule instead, wipe and reseed weekly or nightly, and document the interval. See the related post on how long staging data should live.
Mistakes to avoid
- Relying on rollback while the code under test opens a separate connection that commits.
- Truncating tables but leaving uploaded files in a bucket.
- Forgetting identity counters, which then leak how many records earlier runs created.
- Pointing the reset script at the wrong host. Add a name check, as in the seed script post.
Quick check
Is rollback enough to delete data?
For data written inside the rolled back transaction, yes. It does not cover anything committed by another connection, files written to disk, or messages sent to other systems.
Does truncate leave traces?
Table rows are removed, but database logs and backups may still hold earlier content for their own retention periods. Check how long those are kept.
Sources
Last reviewed: 19 October 2026.
