Pgtestdb's template cloning for Postgres testing offers fast setup times

Pgtestdb and Template Databases

Pgtestdb is a Go/Postgres testing package that leverages Postgres template databases. This built-in Postgres feature allows for rapid creation of new databases by copying an existing template. The process involves Postgres enumerating relations and copying materialized heap, index, and catalog files in 8 kB page chunks, making it faster than migrating a database from scratch or using Docker-based techniques.

Comparison with Schema-Based Testing

A comparison was conducted between pgtestdb's template cloning approach and a schema-based testing methodology used in the River project. The schema-based method, while slower than test transactions, offers advantages in isolation and uses lighter-weight Postgres schemas. However, schemas cannot be cloned, requiring migrations to run every time, which is where pgtestdb's approach has a potential advantage.

Performance Results

The initial setup time for both pgtestdb's database cloning and the schema-based approach was found to be remarkably similar, both around 100 milliseconds. This finding challenged the assumption that creating new databases would be inherently slow. Despite the similar setup times, the overall test suite using the schema-based method ran approximately 3.5 times faster. This was attributed to an optimization in River's test helpers that creates test schemas as needed for Go's parallelization.

Implications for Testing

While the River project will continue using its existing schema-based method due to its overall speed and the incidental benefit of verifying schema-based configuration, pgtestdb is recommended for users focused on end-to-end testing. Its fast database cloning capability is beneficial for scenarios where a full database environment needs to be quickly instantiated for each test.