The Efficiency of pgtestdb's Template Cloning for Testing

Published July 29, 2026 | By Brandur (@brandur on X/Twitter)

Recently, a tip from Cup o Go brought pgtestdb back to my attention. This is a specialized testing package designed for Go and PostgreSQL.

The Core Mechanism: Template Databases

The magic behind pgtestdb is its utilization of Postgres template databases. This is a native feature that can be executed directly via a psql shell:

CREATE DATABASE dbname TEMPLATE template_to_copy;

How it Works Internally

Instead of running a full migration suite, Postgres performs a low-level copy. It identifies the template's relations and duplicates the following in 8 kB page chunks:

• Materialized heaps
• Index files
• Catalog files

This process is significantly faster than starting a migration from zero and far more lightweight than the heavyweight Docker-based orchestration common in many modern projects.

Benchmarking: pgtestdb vs. River's Schema Isolation

I wanted to see how this stacked up against the testing methodology used in River, which I consider to be a high-water mark for reliability and speed.

"River's testing methodology is more or less a gold standard for speed and reliability."

The River Approach

River isolates test cases using schemas rather than full databases. While this is slower than using test transactions, it provides critical benefits:

• Post-mortem Analysis: Test state is preserved upon failure for manual inspection.
• Feature Support: Allows testing of database-wide functionality, such as LISTEN/NOTIFY.
• Concurrency Testing: Enables the verification of complex interactions between multiple transactions and rollbacks.

The Trade-off: While a schema is "lighter" than a database, schemas cannot be cloned. Consequently, the schema-based approach must execute migrations for every new instance.

The Results: Setup Latency

I integrated pgtestdb into the River suite to compare the setup times:

The results were surprising: both methods hover around $100\text{ms}$ for setup. I had previously assumed that creating entire databases would be sluggish, but pgtestdb proves otherwise.

The "Wall Time" Paradox

Despite the similar setup times, the total execution time for the suite told a different story:

The schema-based approach is roughly $\approx 3.5\times$ faster. This isn't because schemas are inherently faster to create, but because of a pooling optimization I implemented in River.

The Reuse Strategy

River's helpers create schemas based on Go's parallelization needs but maintain a pool of them.

• Check for availability: If an unclaimed schema exists, reuse it.
• Cleanse: Wipe the schema before the next test.
• Version Match: Ensure the schema version matches the test's expectations.
• Failure Handling: If a test fails, do not reuse the schema (keep it for debugging).

Implementing this logic was a manual process (pre-LLM) that took several days to debug.

Final Verdict and Recommendations

I will keep River on its current schema-based system because it is already highly optimized and verifies our schema-based configuration. However, I highly recommend pgtestdb for others, especially those performing end-to-end (E2E) testing.

Closing Thought: While $100\text{ms}$ is fast, a suite with 10,000 tests needs something even faster. If pgtestdb (or the projects using it) implemented a similar reuse pool, setup times could drop to $10\text{--}20\text{ms}$, bringing it closer to the speed of test transactions.