Article URL: https://brandur.org/fragments/pgtestdb Comments URL: https://news.ycombinator.com/item?id=49111687 Points: 7 # Comments: 2

I 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: 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: 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. What we find is that the timing of both approaches is remarkably similar, right around 100 ms of setup time. I’d always internalized that anything involving creating new databases would be relatively slow, so I was surprised at how fast pgtestdb’s approach turned out to be here.