Pgtestdb's template cloning approach to testing is fast

I was reminded by Cup o’ Go yesterday of the beingness of Peter Downs’ pgtestdb, a Go/Postgres testing package.

pgtestdb is built astir Postgres template databases, a built-in characteristic that you tin effort correct from a vanilla psql shell:

CREATE DATABASE dbname TEMPLATE template_to_copy;

Copying a template is very fast, much truthful than migrating a trial database from scratch, and much much truthful than immoderate of the heavyweight Docker-based techniques immoderate projects are utilizing these days. At a debased level, Postgres enumerates the template’s relations and copies their materialized heap, index, and catalog files successful 8 kB page chunks.

I retrieve reference astir this characteristic years ago, but to beryllium honorable I’d forgotten it existed, and I was funny really it performed compared to different testing approaches, truthful I had Codex splice pgtestdb into River’s trial suite to spot really it’d fare.

I for illustration to deliberation that River’s testing methodology is much aliases little a golden modular for velocity and reliability. It uses a civilization group of trial helpers that isolate trial cases based connected schema, an attack that’s slower than test transactions, but which has immoderate advantages:

• Leaves trial authorities successful spot to analyse successful lawsuit of a failing test.
• Enables testing of database-wide features for illustration listen/notify.
• Enables testing edges astir aggregate transactions interacting and rollbacks.

Results

In Postgres a schema is lighter weight than a database, truthful the schema-based attack has that edge. However, you can’t clone a schema, truthful the schema-based attack has to tally migrations each time, giving pgtestdb a chopped advantage successful that respect.

That should springiness america an absorbing comparison. Here are the results I got:

Method Count Mean p90 p95 Max

pgtestdb clone	466	98.4ms	247.4ms	299.5ms	465.1ms
Create + migrate schema	81	99.4ms	152.1ms	209.0ms	327.0ms

What we find is that the timing of some approaches is remarkably similar, correct astir 100 sclerosis of setup time.

I’d ever internalized that thing involving creating caller databases would beryllium comparatively slow, truthful I was amazed astatine really accelerated pgtestdb’s attack turned retired to beryllium here.

I’m going to time off River’s tests connected its existing schema-based method fixed it’s already accelerated and testing schema isolation is incidentally useful successful verifying that River’s schema-based configuration useful arsenic advertised, but I’m going to adhd a proposal successful our docs for pgtestdb, peculiarly for users aiming to trial end-to-end (i.e. occupation inserted by customer → afloat completed by worker).

Optimizing via reuse

I was sandbagging a small above. Although setup time for the schema-based attack is akin to pgtestdb’s afloat databases, wide the trial suite runs ~3.5x faster connected the former:

Method Wall time

pgtestdb clone	51.07s
Create + migrate schema	14.54s

But it’s not because schemas are that overmuch faster. River’s trial helpers person a useful optimization successful that they’ll create arsenic galore trial schemas arsenic Go’s instantaneous parallelization requires, but support them pooled arsenic trial cases finish. If an unclaimed schema is ready, a trial lawsuit will cleanable and reuse it alternatively of generating a caller 1 from scratch .

This is simply a small easier said than done because you request to deliberation astir specifications for illustration schema type – i.e. erstwhile testing crossed schema versions, each trial lawsuit must only reuse a schema connected the aforesaid type it expects. This is very doable, of course, but takes a small thought. I wrote River’s implementation pre-LLM, and it took maine a fewer days to compression each the bugs out.

I mention reuse because it could beryllium done pinch pgtestdb arsenic well, perchance arsenic portion of the package, aliases arsenic an augmentation successful projects that telephone into it. 100 sclerosis to bootstrap a trial database is beautiful fast, but if you’re building a afloat exertion that’s going to person 10,000 tests, ideally you want a trial setup connected the bid of 10x faster. Reuse gets it down to 10-20 ms, and much successful statement pinch trial transactions.