HN Debrief

Pgtestdb's template cloning approach to testing is fast

  • Programming
  • Developer Tools
  • Open Source
  • Infrastructure

The post looks at pgtestdb, a small Postgres utility for test suites that need a fresh database often. Instead of rerunning migrations for every test, it migrates once, keeps that database as a template, and asks Postgres to clone it whenever a test needs a clean start. Brandur benchmarked the approach and found it fast enough to make per-test databases realistic, especially when you care about using a real database instead of mocks.

If your backend tests still rebuild schema or lean heavily on fakes, you probably have an easy speed and reliability win available. Pick a real-database strategy that matches your constraints: template clones for isolation and debugging, transactions for raw speed, or shared-state testing when you want concurrency bugs to surface.

Discussion mood

Strongly positive on testing against a real Postgres instance and generally favorable to template cloning as a practical isolation strategy. The main caveat was that template clones are one good tool among several, not a universal fastest path, with many engineers preferring transactions, shared-state testing, or bulk table clearing when those better fit their suites.

Key insights

  1. 01

    Rollback speed loses debugging visibility

    Transaction-wrapped tests are usually faster, but they erase the most useful evidence when something subtle fails. Leaving the database intact after a bad test makes hard bugs easier to inspect, and some Postgres features like LISTEN/NOTIFY or concurrent DDL are awkward enough under test transactions that a clone-based path earns its keep even when it is slower.

    Keep both modes in your test helpers. Use rollback-based tests for the common path, then switch specific cases to isolated databases or schemas when you need realistic behavior or post-failure inspection.

      Attribution:
    • brandur #1
  2. 02

    Dirty databases catch bugs clean ones hide

    Running many tests against one ever-growing database pushes your code through conditions that look more like production. It forces assertions to target specific records instead of idealized counts, and it flushes out race conditions, residual-state assumptions, and overly broad queries that a freshly reset database often lets slip by.

    Add at least some tests against a shared noisy dataset, especially around concurrency and query correctness. If your suite only ever sees a pristine database, expect blind spots around production behavior.

      Attribution:
    • rgbrgb #1
    • benoau #1
    • jtwaleson #1
    • arialdomartini #1
    • ltbarcly3 #1
  3. 03

    Fast table clearing can beat cloning

    For some large schemas, the fastest reset is not another database clone but a one-time schema restore per worker followed by bulk DELETEs between tests. One setup reported clearing 96 tables in about 5.8 milliseconds by temporarily switching session_replication_role to replica, and then went further by collapsing all table IDs onto one master sequence so tests catch swapped-ID bugs across tables.

    Benchmark reset strategies on your own schema before standardizing on clones. If your suite mostly needs fresh data rather than fresh schema, a per-worker database plus aggressive cleanup may be the better trade.

      Attribution:
    • ltbarcly3 #1 #2
    • stephen #1
  4. 04

    Real databases beat high-fidelity fakes

    The pushback on repository fakes was not philosophical. It was about missing the exact failures teams actually ship: bad types, broken foreign keys, constraint violations, migration errors, stored procedure issues, row-level security behavior, and wrong IDs flowing through updates and deletes. Once the suite includes enough behavior to matter, the fake starts converging toward an incomplete database anyway.

    Reserve fakes for narrow unit tests. For service-level tests that protect releases, wire in the production repository and database so migrations and constraints are part of the contract you verify.

      Attribution:
    • brandur #1
    • dagss #1 #2
  5. 05

    Storage tuning gives easy extra speed

    People who already use clone-based testing said the next gains are usually operational, not architectural. Putting Postgres on tmpfs, disabling fsync, or at least relaxing synchronous_commit can shrink clone cost further, and several commenters said turning off fsync gets close to ramdisk performance without consuming memory on one large test run.

    If template cloning is already good enough functionally, squeeze more speed from your local or CI Postgres config before redesigning the whole test harness. Separate settings for test environments can buy a lot for very little effort.

      Attribution:
    • brandur #1
    • peterldowns #1
    • ltbarcly3 #1
    • koolba #1
    • leontrolski #1

Against the grain

  1. 01

    A simple fake is often good enough

    The case for repository fakes was that many teams do not need perfect database fidelity at the lower layers. An in-memory hash-table-backed fake can preserve the shape of inserts, updates, and deletes while avoiding the cost and complexity of full Postgres orchestration, and if your system avoids heavy constraint logic the missing five percent may not justify the infrastructure.

    If your test target is pure business logic and your persistence layer is intentionally thin, do not overbuild the harness. Use the cheapest setup that still covers the failure modes you actually see.

      Attribution:
    • eximius #1
  2. 02

    Hundred-millisecond resets are still slow

    One commenter argued that template cloning is respectable but not especially impressive for very large suites. In their measurements, pg_restore was only about twice as slow as TEMPLATE-based copies, which suggests the practical gap may be smaller than the headline implies once you leave toy examples and start measuring whole-suite throughput.

    Look at wall-clock suite time, not just per-clone elegance. If clones still leave you above your feedback budget, test a dump-and-restore path or a different isolation model instead of assuming templates are the end state.

      Attribution:
    • leontrolski #1

In plain english

DDL ↩
Data Definition Language means SQL statements that change database structure, such as CREATE TABLE or ALTER TABLE.
fsync ↩
fsync is a durability mechanism that forces buffered file changes to be written to stable storage.
LISTEN/NOTIFY ↩
LISTEN/NOTIFY is a PostgreSQL feature for sending lightweight pub-sub style notifications between database connections.
pgtestdb ↩
pgtestdb is a tool that creates clean PostgreSQL test databases quickly by cloning a prepared template database.
Postgres ↩
PostgreSQL, a full-featured open source relational database often used in production web applications.
ramdisk ↩
A ramdisk is a filesystem stored in memory, used to speed up file operations at the cost of volatility.
schema ↩
A schema is a named namespace inside a database that groups tables and other database objects.
session_replication_role ↩
session_replication_role is a PostgreSQL setting that can change how triggers and foreign key checks behave for the current session.
synchronous_commit ↩
synchronous_commit is a PostgreSQL setting that controls how aggressively transactions wait for writes to be durably recorded.
tmpfs ↩
tmpfs is an in-memory filesystem that stores files in RAM instead of on disk.

Reference links

Core project and article

Alternative database cloning and reset techniques

Related tools and writeups

Postgres durability tuning