I've yet to be convinced all of this effort is worth it, compared to the ease of using a repository pattern and just using a fake for tests.
And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.
If you don't need SET LOCAL or different isolation levels in your tests, the fastest by far in my experience is to have a template DB per process (perhaps migrating it once and then using it as template to make copies), then run your tests wrapped in a transaction that you rollback at the end.
Postgres rollback is essentially instant, since all the cleanup is left to a later vacuum. Postgres can handle "nested transactions" (savepoints), so in most cases you don't have to modify your code.
For the small set of tests that aren't compatible with being wrapped in a transaction, you run them serially in each process and either DELETE or TRUNCATE CASCADE in between for cleanup. DELETE is a bit faster, but then you have to deal with foreign key issues yourself.
There's more that can go wrong when relying on transaction rollback. But in terms of speed, I don't know of a faster way.
Yeah, I've generally recommended using test transactions during test cases [1] as they're extremely fast. I'm open to alternatives like Peter's approach here though because the template approach has some major advantages. The biggest one is that because a database isn't rolled back, state is left around for a failing test, and that can occasionally be really useful when trying to debug a particularly difficult test bug.
Test transactions do occasionally cause other trouble too. DDL is theoretically transaction-safe in Postgres, but when running concurrent schema changes even in test isolation, you can still have tests that leak into each other. Testing anything based on listen/notify is also difficult in a test transaction.
Probably not a bad strategy is to have both tools available in your test helpers: (1) test transactions for the common case, and (2) template databases when you need more isolation.
However, as I alluded to in the second part of the article, in River we're currently using an approach similar to template databases in that every test case operates in its own isolated schema, but with the twist that we also reuse schemas after successful tests, saving us a lot of time in setup costs and bringing us back closer to test transaction performance. Great isolation and our tests are extremely fast, so it's working well.
Our db clearing takes like 5ms (large schema from mature company, not a toy). We start by restoring a production schema dump which ensures our test db / devdb schema is basically identical to what we run in production. Any migrations you are working on in your branch get added to the restore of the prod schema after it runs. Building a schema from ORM definitions is what you do if you don't care about your life or time. Clearing the test db takes something similar. This is fine, using template db's is a good way to make a scratch copy of a local database to test migrations (rsync is better if you are technical enough to use it to restore after destructive changes).
Timings:
- create testdb: 9ms
- restore prod schema: 500ms (done once per test process)
- clear test data in 96 tables between tests that write to db (5ms)
The fastest way to clear a test db is to run a query to get every schema/table name, then run ";".join("`DELETE FROM {schema}.{tablename};" for schema, table in my_tables) after putting the db in replica mode. This takes single digit ms a lot of the time even with a decent amount of test data. I've done it every which way and this is by far the fastest way to clear data between tests.
-- This will work on basically any postgresql database with basically any schema so just use it.
test_db_2235191=# CREATE OR REPLACE PROCEDURE public.delete_all_table_data()
LANGUAGE plpgsql
AS $procedure$
DECLARE
target record;
previous_replication_role text;
BEGIN
previous_replication_role :=
current_setting('session_replication_role');
PERFORM set_config('session_replication_role', 'replica', true);
BEGIN
FOR target IN
SELECT namespace.nspname AS schema_name,
relation.relname AS table_name
FROM pg_catalog.pg_class AS relation
JOIN pg_catalog.pg_namespace AS namespace
ON namespace.oid = relation.relnamespace
WHERE relation.relkind = 'r'
AND namespace.nspname NOT LIKE 'pg\_%' ESCAPE '\'
AND namespace.nspname <> 'information_schema'
ORDER BY namespace.nspname, relation.relname
LOOP
RAISE NOTICE 'Deleting %.%',
target.schema_name,
target.table_name;
EXECUTE format(
'DELETE FROM %I.%I',
target.schema_name,
target.table_name
);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
PERFORM set_config(
'session_replication_role',
previous_replication_role,
true
);
RAISE;
END;
PERFORM set_config(
'session_replication_role',
previous_replication_role,
true
);
END;
$procedure$;
CREATE PROCEDURE
Time: 0.840 ms
test_db_2235191=# CALL public.delete_all_table_data();
NOTICE: ... (notices removed for 96 tables)
CALL
Time: 5.855 ms
I concur with this approach. TRANSACTION-y tests (the default in Django) often don't quite line up with reality and make it hard to eg. drop in a breakpoint and run a server against the test's db state.
I've experimented (see below) with TEMPLATE dbs and such in Python (with inspiration from this library). IMHO the "around 100ms" mark is pretty slow for a big test suite. Interestingly, pg_restore is only twice as slow as TEMPLATEs.
I'd just say that a nice thing about it on disk (even if you disable fsync) is that in case of a failing test, you can examine the post-run state which is occasionally extremely valuable.
And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.
For the small set of tests that aren't compatible with being wrapped in a transaction, you run them serially in each process and either DELETE or TRUNCATE CASCADE in between for cleanup. DELETE is a bit faster, but then you have to deal with foreign key issues yourself.
There's more that can go wrong when relying on transaction rollback. But in terms of speed, I don't know of a faster way.
Test transactions do occasionally cause other trouble too. DDL is theoretically transaction-safe in Postgres, but when running concurrent schema changes even in test isolation, you can still have tests that leak into each other. Testing anything based on listen/notify is also difficult in a test transaction.
Probably not a bad strategy is to have both tools available in your test helpers: (1) test transactions for the common case, and (2) template databases when you need more isolation.
However, as I alluded to in the second part of the article, in River we're currently using an approach similar to template databases in that every test case operates in its own isolated schema, but with the twist that we also reuse schemas after successful tests, saving us a lot of time in setup costs and bringing us back closer to test transaction performance. Great isolation and our tests are extremely fast, so it's working well.
---
[1] https://brandur.org/fragments/go-test-tx-using-t-cleanup
Timings:
The fastest way to clear a test db is to run a query to get every schema/table name, then run ";".join("`DELETE FROM {schema}.{tablename};" for schema, table in my_tables) after putting the db in replica mode. This takes single digit ms a lot of the time even with a decent amount of test data. I've done it every which way and this is by far the fastest way to clear data between tests.I've experimented (see below) with TEMPLATE dbs and such in Python (with inspiration from this library). IMHO the "around 100ms" mark is pretty slow for a big test suite. Interestingly, pg_restore is only twice as slow as TEMPLATEs.
https://github.com/leontrolski/postgresql-testing
I'd be interested about how all this compares to snapshotting the postrgres dir with ZFS and restoring to that, but don't have a Linux box to hand.
https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-...
I'd just say that a nice thing about it on disk (even if you disable fsync) is that in case of a failing test, you can examine the post-run state which is occasionally extremely valuable.
https://www.postgresql.org/docs/current/wal-async-commit.htm...