pgtestdb uses Postgres template cloning for fast database isolation
A performance study shows cloning template databases provides full isolation in roughly 100ms, offering a simpler alternative to schema-based testing.
Database testing typically forces a compromise between execution speed and environment isolation. While developers often choose between fast but limited test transactions or slow, heavyweight Docker-based setups, a new approach using the Go package pgtestdb demonstrates that full database isolation can be achieved without a significant performance penalty.
Brandur, the creator of River, recently conducted a performance experiment comparing pgtestdb against River's own schema-based testing methodology. The study found that pgtestdb's setup time for cloning is approximately 98.4ms on average, nearly identical to the 99.4ms mean time required for creating and migrating a new schema. According to Brandur, copying a template is significantly faster than migrating a test database from scratch and far more efficient than many contemporary Docker-based techniques.
The Mechanics of Template Cloning
pgtestdb achieves these speeds by utilizing PostgreSQL's built-in `CREATE DATABASE ... TEMPLATE` feature. Rather than running a series of migration scripts for every test case, the tool clones a pre-prepared database. Technically, PostgreSQL implements this cloning process by copying materialized heap, index, and catalog files in 8 kB page chunks, allowing the system to bypass the overhead of executing SQL commands to rebuild the structure.
Impact on Testing Architecture
For developers managing large-scale applications with thousands of tests, database setup overhead is often the primary bottleneck. The ability to spin up a fully isolated database in roughly 100ms simplifies the testing architecture for Go and Postgres projects. This is particularly critical for end-to-end workflows—such as a job being inserted by a client and completed by a worker—where schema-level isolation may be insufficient to ensure a clean state.
Trade-offs and Future Use
Despite the speed of individual setups, the study noted that overall suite wall time can still vary based on the methodology. River's pooled schema approach ran approximately 3.5x faster (14.54s) than pgtestdb cloning (51.07s) due to specific reuse optimizations. However, the simplicity and isolation provided by pgtestdb make it a compelling alternative for many use cases.
Brandur indicated that he intends to add a recommendation for pgtestdb to the River documentation, specifically for users aiming to perform comprehensive end-to-end testing. As projects move away from heavyweight container orchestration for every test, template cloning presents a viable middle ground between speed and total isolation.