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

Pgtestdb's template cloning approach to testing is fast