[tln]

My test suite has the schema and seed data in a template database then runs

    CREATE DATABASE testX TEMPLATE seed;

...which takes about 1 second per test run. The seed data and schema is baked into a docker image, and recreated whenever there are new migration files. Starting the docker image is slow but that doesn't happen on every test run.

[brasetvik]

That's a pretty reasonable approach, and similar in spirit to what I find to work well:

- Create a template with necessary extensions, or just install them to "template1" which is the template if you don't explicitly specify a template. Installing PostGIS, for example, takes a few seconds - which is annoying if you create the schema from scratch in test runs. (`create extension if not exists postgis` can still be around in your schema, it'll just return right away) - Create a template for the test session, based on the template you've pre-installed extensions in, and apply the schema there. - Create a database based on the second template for whatever scope makes sense for your test.

If your Postgres cluster is only serving test workloads, `fsync=off` can speed up things as well. (Which a stock postgresql.conf will point out can cause irrecoverable data loss, which I don't care about for test data)

[stickfigure]

I do the same thing, though it's quite a bit faster than 1s (perhaps my schema is smaller).

No docker though. I just run my migration scripts against the template database. In CI it runs every time; for local testing, I drop the template db and run the migrations.

The only annoying thing is that my local pg instance fills up with test runs. The good thing is that it's really easy to go back and inspect the database after a test run. But periodically I have to run a script that drops them all.