Pgtestdb's template cloning approach to testing is fast

The original developer of a Rails project I inherited decided to seed the test db with db/seed.rb and tie every test to the content of the seed. There were a lot of tests so rewriting then was not an option, not immediately.

I wrote the new tests in the canonical way but I still had the problem of all those seconds spent seeding the db even when I run a single test. I wrote a couple of scripts that dumped the db at the end of the tests (if a dump did not exist yet) and reloaded it at the beginning of the tests. This is much faster. I still have to clear the test db and reseed it when I switch branch, because I don't have a dump list branch.

Maybe I can create a template with the data in it. Or finally rewrite every single old test.

Just want to say thank you, Brandur, for checking out pgtesdb and benchmarking it so thoroughly. I’m pleased that it performs so well and I’m going to throw some tokens and brainpower at potentially implementing a cleanup+reuse+pool of successful dbs instead of always tearing them down.

Some confusion in the threads below —

pgtestdb is just a primitive for “give my test a clean db, fast.” With your postgres running on ramdisk, I don’t think there’s any faster way to make a clean and fully migrated db — and your migrations only run one time, no matter how many test processes you have operating concurrently or how many tests are in parallel within those processes.

You can actually combine it with test transactions, you’re totally allowed to do anything you want with the db! It just so happens that it’s fast enough (in my experience) to Just Give Every Test Its Own Database, for quite a large number of tests.

Really cool upside of AI is enabling experiments like this one that previously would have been prohibitively time consuming. Thanks again, Brandur.

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.

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.

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.

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

Anything similar possible with MySQL?

Cloning a template is IO-heavy. You can speed it up further by putting postgres on a ramdisk.

[dead]