GoKawiilI was reminded by Cup o’ Go yesterday of the existence of Peter Downs’ pgtestdb, a Go/Postgres testing package.
pgtestdb is built around Postgres template databases, a built-in feature that you can try right from a vanilla psql shell:
CREATE DATABASE dbname TEMPLATE template_to_copy;
Copying a template is very fast, more so than migrating a test database from scratch, and much more so than some of the heavyweight Docker-based techniques some projects are using these days. At a low level, Postgres enumerates the template’s relations and copies their materialized heap, index, and catalog files in 8 kB page chunks.
I remember reading about this feature years ago, but to be honest I’d forgotten it existed, and I was curious how it performed compared to other testing approaches, so I had Codex splice pgtestdb into River’s test suite to see how it’d fare.
I like to think that River’s testing methodology is more or less a gold standard for speed and reliability. It uses a custom set of test helpers that isolate test cases based on schema, an approach that’s slower than test transactions, but which has some advantages:
Leaves test state in place to examine in case of a failing test.
Enables testing of database-wide features like listen/notify.
Enables testing edges around multiple transactions interacting and rollbacks.
In Postgres a schema is lighter weight than a database, so the schema-based approach has that edge. However, you can’t clone a schema, so the schema-based approach has to run migrations every time, giving pgtestdb a distinct advantage in that respect.
... continue reading