In the past few years I've been using a "dirty db" approach to testing where I run parallel integration tests against a single postgres-based backend without any cleanup between tests. Every test hits the same db with unique ID's. The constraint is that you can't assert exact counts, you need to assert that specific ID's exist. With this setup, the db just gets setup once and all the tests can run in parallel. Integration points are where I've seen the most breakage in prod, so I like to test with a real db. Parallelism makes it fast and incidentally this approach sometimes catches racy interactions that otherwise might only see with concurrent prod requests (heisenbugs in waiting). Many tests hitting the API's in parallel ends up being a better simulation of prod behavior.
tux3 1 hours ago [-]
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.
brandur 1 hours ago [-]
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.
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.
brandur 6 minutes ago [-]
Some very significant disadvantages to that approach:
* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.
* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.
* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.
* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.
* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.
* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.
* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.
There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.
bbkane 12 minutes ago [-]
Once you've done it a time or two, setting up the "clone the db"/"erase the db for each test" pattern isn't that much work (plenty of libraries to help too).
And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.
So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.
ltbarcly3 2 hours ago [-]
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
leontrolski 39 minutes ago [-]
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 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.
leontrolski 38 minutes ago [-]
Being to lazy to think or test it - does the above reset SEQUENCEs?
ltbarcly3 7 minutes ago [-]
actually no, and we have something weird for that too:
We have code that creates one master sequence then replaces every sequence in the testdb with that master sequence, so all tables pull from the same sequence. That way you can never accidentally swap two id's in an api response and have the tests pass because you coincidentally both had id 5 or whatever. We can run the tests either way (with sequences swapped out or not). We almost always leave the master sequence in place because the id-swap bug is very common and the alternative (bug caused by two id's being the same on different tables) basically never comes up.
brandur 1 hours ago [-]
Interesting. How many database copies do you bring up when the test suite starts running, and how is parallelism handled?
ltbarcly3 4 minutes ago [-]
We use pytest with xdist and we run as many as the system it is running on can handle. On my threadripper machine with 256GB I could run about 60 concurrent tests, on my 9955hx machine I use day to day I can run 13. On a MBP I think it's about 8-16. There is diminishing returns with more processes. Each xdist process gets it's own testdb.
If I was designing it from scratch I would use a single testdb and point all the python processes at it, and never clean up between tests or even between test runs. This is both faster and a better test, as I feel that clearing the db makes it very hard to detect overly broad queries unless you go out of your way to pack in a lot of extra harness data which people almost never do and is a chore.
hugodutka 2 hours ago [-]
Cloning a template is IO-heavy. You can speed it up further by putting postgres on a ramdisk.
brandur 1 hours ago [-]
Peter actually makes this exact point in the project's README. See this section here:
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.
ltbarcly3 2 hours ago [-]
Just turning off fsync is basically just as fast as a ramdisk and you can't use up all your memory with one big test.
koolba 2 hours ago [-]
Turning off synchronous_commit gets you most of the way without ever worrying about data corruption if something crashes mid way through.
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
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.
* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.
* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.
* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.
* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.
* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.
* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.
* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.
There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.
And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.
So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.
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.
We have code that creates one master sequence then replaces every sequence in the testdb with that master sequence, so all tables pull from the same sequence. That way you can never accidentally swap two id's in an api response and have the tests pass because you coincidentally both had id 5 or whatever. We can run the tests either way (with sequences swapped out or not). We almost always leave the master sequence in place because the id-swap bug is very common and the alternative (bug caused by two id's being the same on different tables) basically never comes up.
If I was designing it from scratch I would use a single testdb and point all the python processes at it, and never clean up between tests or even between test runs. This is both faster and a better test, as I feel that clearing the db makes it very hard to detect overly broad queries unless you go out of your way to pack in a lot of extra harness data which people almost never do and is a chore.
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...