[pquerna]

At a $previous_job I basically also did what the post is describing.

The "best" thing we did was actually using a "template database": https://www.postgresql.org/docs/14/manage-ag-templatedbs.htm...

We would start a Postgres Process. We would create a new database, run all of our migrations and basic data bring up. Then we would create a new Database per Test Suite, using the one we just ran migrations as the Template.

This meant the initial bring up was a few seconds, but then each test suite would get a new database in a dozen milliseconds (IIRC).

[mborch]

That's probably more or less the same thing in terms of what actually happens in PostgreSQL.

All things considered, an actual database probably gives you the least gray hair, but with some careful test setup I have had good success using the savepoint/rollback trick (and it trivially supports nested fixtures as well).

[piperswe]

With multiple databases you get the advantage of being able to run tests in parallel, so even though they might be the same under the hood they offer interfaces that suit quite different use cases