pgtestdb ties schema-based Postgres tests on setup speed
Brandur, who maintains the Go job queue River, was reminded by the podcast Cup o' Go of pgtestdb, a Go/Postgres testing package built by Peter Downs. pgtestdb is built around Postgres template databases, a built-in feature reachable from a plain psql shell with CREATE DATABASE dbname TEMPLATE template_to_copy. Copying a template is fast because Postgres enumerates the template's relations and copies their materialized heap, index and catalog files in 8 kB page chunks, rather than running migrations from scratch or standing up a Docker container.
Curious how pgtestdb would compare to River's existing testing approach, Brandur had the AI coding tool Codex splice pgtestdb into River's test suite. River's own method isolates test cases by Postgres schema rather than by full database. A schema is lighter weight than a database, but it cannot be cloned, so the schema-based approach has to run migrations on every test, an area where Brandur says pgtestdb has a distinct advantage.
The comparison found that per-test setup time is remarkably similar for both approaches, right around 100 ms. Brandur says he had always assumed creating a new database would be noticeably slower, and was surprised pgtestdb's setup came out even with the schema-based method. Despite that, running the full test suite is about 3.5x faster with the schema-based approach. The reason is not that schemas are inherently faster to set up: River's test helpers create as many test schemas as Go's parallelization needs, then keep them pooled after test cases finish, so a later test case can clean and reuse an existing schema instead of generating a new one. Handling this correctly required tracking schema versions, so a test only reuses a schema built on the same version it expects; Brandur wrote this reuse logic before current LLM coding tools existed and says it took a few days to debug. If a test case fails, its schema is not reused, leaving its state available for later inspection.
Brandur plans to keep River on its existing schema-based method, partly because it is already fast and partly because exercising schema isolation incidentally verifies that River's own schema-based configuration works as advertised. He does intend to add a documentation recommendation for pgtestdb, particularly for projects testing full end-to-end flows such as a job inserted by a client and completed by a worker. He also notes that pgtestdb could adopt the same reuse/pooling optimization, either built into the package or layered on by projects that call it: 100 ms to bootstrap a test database is fast, but a large application with 10,000 tests would ideally want setup on the order of 10x faster, and reuse brings schema-based setup down to about 10-20 ms, closer to what test transactions achieve.
Key facts
- pgtestdb is a Go/Postgres testing package by Peter Downs built on Postgres template databases, which Postgres copies in 8 kB page chunks rather than replaying migrations or using Docker.
- Brandur, maintainer of the Go job queue River, had Codex integrate pgtestdb into River's test suite to compare it with River's existing schema-based test isolation.
- Per-test setup time came out roughly equal for both approaches, right around 100 ms.
- Despite equal setup time, River's schema-based suite runs the full test suite about 3.5x faster overall, because it pools and reuses schemas between test cases instead of creating a new one each time.
- Adding reuse/pooling to pgtestdb could bring its setup time down to about 10-20 ms, which Brandur says would matter for applications with around 10,000 tests.
Why it matters
Test suite speed compounds: identical per-test setup, 100 ms for both approaches here, still produces a 3.5x gap once thousands of tests run it. The post is a reminder that setup latency alone does not predict suite-level performance; reuse and pooling strategy can matter more than which low-level database primitive is used to isolate a test.
Who it affects
Go developers who write Postgres-backed test suites, and maintainers of testing libraries like pgtestdb deciding whether to add reuse or pooling features. It also speaks to teams choosing between schema-per-test and full-database-per-test isolation strategies for any Postgres-backed application.
How to use it
pgtestdb is invoked with a standard Postgres CREATE DATABASE ... TEMPLATE command under the hood. Brandur recommends it for projects that want to test full end-to-end flows, such as a job inserted by a client and completed by a worker, rather than replacing River's own schema-based helpers, which he is keeping because they are already fast and double as a check that River's schema isolation works correctly.
How solid is it
This is a single author's benchmark on one project's test suite (River), described in a short blog fragment rather than a formal methodology write-up; exact test counts, hardware and how many runs were averaged are not given. The 100 ms setup figure and the 3.5x overall gap are stated as measured results, and the explanation for the gap, schema pooling and reuse, is described concretely rather than speculated.
Risks and caveats
The comparison covers one codebase and one workload; results could differ for suites with different test counts, parallelism or database sizes. The proposed fix, adding reuse and pooling to pgtestdb, is described as something that 'could be done' and has not been implemented or benchmarked in the post.
“I like to think that River's testing methodology is more or less a gold standard for speed and reliability.”
— Brandur, River maintainer