[cxcorp]

For the postgres config, set fsync=off and full_page_writes=false, and increase min_wal_size, max_wal_size and checkpoint interval with the hope that your tests pass before having to flush the WAL. Maybe slap in some tunings from PGTune.

If you're using docker/podman or docker-compose and your db size is small, a major speedup on linux is to just mount the entire data dir into memory with --tmpfs /var/lib/postgresql/data (or tmpfs: - /var/lib/postgresql/data in docker-compose)

Additionally, if you constantly reset your db in the tests, consider making a template db at the start and later just doing CREATE DATABASE ... TEMPLATE foo; to copy the pages from that template instead of running migrations that produce WAL log. In fact, consider making a db for every test suite from that template at the start - then you can run each suite in parallel (if your app's only state is the db and a single backend).

[williamdclt]

I was surprised but CREATE DATABASE TEMPLATE is still pretty slow, a few hundred ms on my machine. Better than alternatives though.

A few more application-level optimisations: if you need to clear your database between tests, the best way is to wrap each test in a transaction and roll it back (Postgres supports nested transactions with checkpoints which might help make that transparent to the application under test). The second best way is usually to use DELETE rather than TRUNCATE, the latter is much slower on small tables (but much faster on big ones). The third fastest way is to create a new DB from template, although it plays nice with parallelisation.

[magicalhippo]

How much does an fsync cost against memory (tmpfs/RAM disk)?

Just made me curious.

[zamalek]

Thank you!