Pgtestdb's template cloning approach to testing is fast

Pgtestdb's template cloning approach to testing is fast
brandur.org

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)

    -- 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

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