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).
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).
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
Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)
If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."
They are many ways for a database to behave in a not obvious way...
Typically error handling, xabort (in sql server, especially if you don't want to get blocking orphan connections etc).
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).