Pgtestdb's template cloning approach to testing is fast | Hacker News Reader

Pgtestdb's template cloning approach to testing is fast

brandur.org

69 pointsbrandur51 comments

For a long time I've used similar approaches for testing against Postgres, especially when using tools like Testcontainers. It's saved a massive amount of time waiting for my tests to run over the years and it's incredibly simple to implement, even manually.

1. Set up a container image based on our prod Postgres DB's version with current prod migrations pre-applied.

2. Configure Testcontainers to be reuse the container between tests, at least within the same file.

3. ~25 lines of init code that apply local-branch migrations to a template DB and copy test data to it on the first test.

4. When the template DB already exists, test setup just drop the DB and copy it fresh from the template DB.

I find tests that actually perform the actions catch a heck of a lot more bugs and are easier to setup up than code that uses a lot of mocking or "test implementations" of a class. And with just a little care the performance penalty is negligible and way more than worth it.

Many people are unaware of a relatively recent method that allows almost instant copies of postgres databases that I use very heavily:

  CREATE DATABASE new_db TEMPLATE templatedb STRATEGY FILE_COPY

Combined with

  set file_copy_method = 'clone'

This copies arbitrarily sized DBs sub-second - I use it on dbs larger than 1TB regularly - and has the advantage of being copy-on-write - the new db doesn't take up any extra disk space until you start writing to it in which case only the differences are used.

Only supported on linux and macos AFAIK

https://www.postgresql.org/docs/18/runtime-config-resource.h...

Thanks for that — I didn't know about `clone` (and important to note it's non-default) and now intend to try it out.

I'm actually curious if this is faster than a tmpfs clone, I suspect it might be as depending on how many underlying files it's copying, it might effectively be doing "nothing" for each file.

Obviously operations after that will be a lot slower reading / writing to a normal disk

I assume it's only fast for large databases when running on a filesystem that supports copy-on-write? In particular, I assume it won't benefit from CoW on a tmpfs?

I do know that if it's not supported by the underlying filesystem if falls back to the default.

What I don't know is if it's faster to copy than a regular copy on a ram tmpfs - since it may be essentially be doing "nothing" - updating some pointers instead of copying stuff in ram.

So if your workload is limited by copy not execution speed it might be worth testing.

However at that point you're running on a regular FS so if you're trying to benefit test speed by having all your regular postgres operations on a ramdisk, that part will end up being slower.

Just want to say thank you, Brandur, for checking out pgtesdb and benchmarking it so thoroughly. I’m pleased that it performs so well and I’m going to throw some tokens and brainpower at potentially implementing a cleanup+reuse+pool of successful dbs instead of always tearing them down.

Some confusion in the threads below —

pgtestdb is just a primitive for “give my test a clean db, fast.” With your postgres running on ramdisk, I don’t think there’s any faster way to make a clean and fully migrated db — and your migrations only run one time, no matter how many test processes you have operating concurrently or how many tests are in parallel within those processes.

You can actually combine it with test transactions, you’re totally allowed to do anything you want with the db! It just so happens that it’s fast enough (in my experience) to Just Give Every Test Its Own Database, for quite a large number of tests.

Really cool upside of AI is enabling experiments like this one that previously would have been prohibitively time consuming. Thanks again, Brandur.

I haven't had time to try it, but I thought running PG on a copy-on-write filesystem with a specific "clone template" incantation would get you instant clones? Probably doable in a docker container? https://boringsql.com/posts/instant-database-clones/

Possibly — but then you need a cowfs everywhere you run your tests. Templates are already really fast. Give it a shot and lmk if it’s faster!

No worries Peter. And thanks for putting together pgtestdb — such a great project! This conversion sprint was a fun little experiment.

The pgtestdb readme claims 20ms setup, while OP claims 100ms. Where does that difference come from? A different postgres setup (in memory vs disk)?

In the past few years I've been using a "dirty db" approach to testing where I run parallel integration tests against a single postgres-based backend without any cleanup between tests. Every test hits the same db with unique ID's. The constraint is that you can't assert exact counts, you need to assert that specific ID's exist. With this setup, the db just gets setup once and all the tests can run in parallel. Integration points are where I've seen the most breakage in prod, so I like to test with a real db. Parallelism makes it fast and incidentally this approach sometimes catches racy interactions that otherwise might only see with concurrent prod requests (heisenbugs in waiting). Many tests hitting the API's in parallel ends up being a better simulation of prod behavior.

I like this approach too, just have to build your tests around the expectation that there will be unrelated/residual test data. This is also very useful when you're testing the frontend too like Playwright tests where cleaning up data requires helper endpoints or db connection info and libraries.

What I really don't like is mocking dependencies and if a function gets called you pass, because so much more can actually go wrong like transaction locks, SQL issues, data issues, UI issues.

Same approach here. Sometimes it's hard to debug. "Why is this weird test failing for an unrelated change", but I have to remind myself that it's a real concurrency bug that would have otherwise gone unnoticed. Worth the effort in my book!

My team and I are using the same approach since about 5 years. I collected a bit of notes about it here

https://arialdomartini.github.io/when-im-done-i-dont-clean-u...

If you don't need SET LOCAL or different isolation levels in your tests, the fastest by far in my experience is to have a template DB per process (perhaps migrating it once and then using it as template to make copies), then run your tests wrapped in a transaction that you rollback at the end. Postgres rollback is essentially instant, since all the cleanup is left to a later vacuum. Postgres can handle "nested transactions" (savepoints), so in most cases you don't have to modify your code.

For the small set of tests that aren't compatible with being wrapped in a transaction, you run them serially in each process and either DELETE or TRUNCATE CASCADE in between for cleanup. DELETE is a bit faster, but then you have to deal with foreign key issues yourself.

There's more that can go wrong when relying on transaction rollback. But in terms of speed, I don't know of a faster way.

Yeah, I've generally recommended using test transactions during test cases [1] as they're extremely fast. I'm open to alternatives like Peter's approach here though because the template approach has some major advantages. The biggest one is that because a database isn't rolled back, state is left around for a failing test, and that can occasionally be really useful when trying to debug a particularly difficult test bug.

Test transactions do occasionally cause other trouble too. DDL is theoretically transaction-safe in Postgres, but when running concurrent schema changes even in test isolation, you can still have tests that leak into each other. Testing anything based on listen/notify is also difficult in a test transaction.

Probably not a bad strategy is to have both tools available in your test helpers: (1) test transactions for the common case, and (2) template databases when you need more isolation.

However, as I alluded to in the second part of the article, in River we're currently using an approach similar to template databases in that every test case operates in its own isolated schema, but with the twist that we also reuse schemas after successful tests, saving us a lot of time in setup costs and bringing us back closer to test transaction performance. Great isolation and our tests are extremely fast, so it's working well.

---

[1] https://brandur.org/fragments/go-test-tx-using-t-cleanup

The original developer of a Rails project I inherited decided to seed the test db with db/seed.rb and tie every test to the content of the seed. There were a lot of tests so rewriting then was not an option, not immediately.

I wrote the new tests in the canonical way but I still had the problem of all those seconds spent seeding the db even when I run a single test. I wrote a couple of scripts that dumped the db at the end of the tests (if a dump did not exist yet) and reloaded it at the beginning of the tests. This is much faster. I still have to clear the test db and reseed it when I switch branch, because I don't have a dump list branch.

Maybe I can create a template with the data in it. Or finally rewrite every single old test.

As long as you're using Postgres, I would definitely try out an approach based on template databases in your situation. It should check all the boxes that a run with `db/seed.rb` would, except an order of magnitude or two compared to what Rails would be doing (i.e. parsing a big Ruby file and loading schema in table by table).

One of the best things about LLMs is they make these sorts of refactors entirely plausible even if you're not a subject area expert. You could probably prototype this and have a patch ready in an hour or two.

I feel the template approach has a kind of awkward performance level. As the article points out, 100ms of overhead is still a lot if you incur it for each test. But once you re-use the database, it doesn't matter much if creating it takes 100ms or 10s, since you only do it approximately once per CPU core.

And the article doesn't explain how to clean the re-used database between tests in a performant way. But that's actually the most challenging part of database/schema re-use. Or should the database simply not be cleaned between tests, relying on the assumption that the test won't rely on data it didn't create (e.g. because it's in a different tenant)?

Our db clearing takes like 5ms (large schema from mature company, not a toy). We start by restoring a production schema dump which ensures our test db / devdb schema is basically identical to what we run in production. Any migrations you are working on in your branch get added to the restore of the prod schema after it runs. Building a schema from ORM definitions is what you do if you don't care about your life or time. Clearing the test db takes something similar. This is fine, using template db's is a good way to make a scratch copy of a local database to test migrations (rsync is better if you are technical enough to use it to restore after destructive changes).

Timings:

  - create testdb: 9ms
  - restore prod schema: 500ms (done once per test process)
  - clear test data in 96 tables between tests that write to db (5ms)

The fastest way to clear a test db is to run a query to get every schema/table name, then run ";".join("`DELETE FROM {schema}.{tablename};" for schema, table in my_tables) after putting the db in replica mode. This takes single digit ms a lot of the time even with a decent amount of test data. I've done it every which way and this is by far the fastest way to clear data between tests.

    -- This will work on basically any postgresql database with basically any schema so just use it.
    test_db_2235191=# CREATE OR REPLACE PROCEDURE public.delete_all_table_data()
        LANGUAGE plpgsql
        AS $procedure$
        DECLARE
            target record;
            previous_replication_role text;
        BEGIN
            previous_replication_role :=
                current_setting('session_replication_role');
        PERFORM set_config('session_replication_role', 'replica', true);

        BEGIN
            FOR target IN
                SELECT namespace.nspname AS schema_name,
                       relation.relname AS table_name
                FROM pg_catalog.pg_class AS relation
                JOIN pg_catalog.pg_namespace AS namespace
                  ON namespace.oid = relation.relnamespace
                WHERE relation.relkind = 'r'
                  AND namespace.nspname NOT LIKE 'pg\_%' ESCAPE '\'
                  AND namespace.nspname <> 'information_schema'
                ORDER BY namespace.nspname, relation.relname
            LOOP
                RAISE NOTICE 'Deleting %.%',
                    target.schema_name,
                    target.table_name;

                EXECUTE format(
                    'DELETE FROM %I.%I',
                    target.schema_name,
                    target.table_name
                );
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                PERFORM set_config(
                    'session_replication_role',
                    previous_replication_role,
                    true
                );
                RAISE;
        END;

        PERFORM set_config(
            'session_replication_role',
            previous_replication_role,
            true
        );
    END;
    $procedure$;
    CREATE PROCEDURE
    Time: 0.840 ms


    test_db_2235191=# CALL public.delete_all_table_data();
    NOTICE:  ... (notices removed for 96 tables)
    CALL
    Time: 5.855 ms

Interesting. How many database copies do you bring up when the test suite starts running, and how is parallelism handled?

We use pytest with xdist and we run as many as the system it is running on can handle. Each xdist process creates and sets up it's own test_db with a unique name (and drops it at the end of its run if possible). Setup and teardown are done via hooks in pytest. The advantage of this is the test run just needs a db running it can create a testdb on and connect to, so I can have tests running on 5 different branches or workdirs and they don't interact at all. On my threadripper machine with 256GB I could run about 60 concurrent tests, on my 9955hx machine I use day to day I can run 13. On a MBP I think it's about 8-16. There is diminishing returns with more processes.

If I was designing it from scratch I would use a single testdb and point all the python processes at it, and never clean up between tests or even between test runs. This is both faster and a better test, as I feel that clearing the db makes it very hard to detect overly broad queries unless you go out of your way to pack in a lot of extra harness data which people almost never do and is a chore.

I concur with this approach. TRANSACTION-y tests (the default in Django) often don't quite line up with reality and make it hard to eg. drop in a breakpoint and run a server against the test's db state.

I've experimented (see below) with TEMPLATE dbs and such in Python (with inspiration from this library). IMHO the "around 100ms" mark is pretty slow for a big test suite. Interestingly, pg_restore is only twice as slow as TEMPLATEs.

https://github.com/leontrolski/postgresql-testing

I'd be interested about how all this compares to snapshotting the postrgres dir with ZFS and restoring to that, but don't have a Linux box to hand.

Django has built in support for templates https://docs.djangoproject.com/en/6.0/ref/databases/#test-da...

I thought it had a way to do concurrent sharded tests on the same database instance by automatically creating a templated db per process then each process using transactions. I'm having a hard time finding the docs tho

Being to lazy to think or test it - does the above reset SEQUENCEs?

actually no, and we have something weird for that too:

We have code that creates one master sequence then replaces every sequence in the testdb with that master sequence, so all tables pull from the same sequence. That way you can never accidentally swap two id's in an api response and have the tests pass because you coincidentally both had id 5 or whatever. We can run the tests either way (with sequences swapped out or not). We almost always leave the master sequence in place because the id-swap bug is very common and the alternative (bug caused by two id's being the same on different tables) basically never comes up.

We do similar, although lean into our strict "every table as a sequence" and "all FKs are deferred" conventions and only issue DELETEs for tables that actually were inserted by the test

https://github.com/joist-orm/joist-orm/blob/16cc73f148b6f962...

I forgot the speedup this got us on a 400-500 table schema, but it was noticeable -- curious if you could do the same / what the perf impact would be.

tracking the table inserted to isn't reliable without some kind of trigger based registry as it requires all db interactions to go through some kind of orm or something which we don't do because it's a bad thing to do. Sometimes CTE's that modify stuff are 1000x faster than the alternative and it's hard to track what is doing modifications vs not. We do track at the psycopg2 level whether a query has INSERT in it somewhere, which is a pretty good heuristic.

But lets say we just always cleared all the tables: 5ms per test * 6000 tests == 30s, across 15 test processes it is 2s of overhead to the test run. Meh. You are better off auditing your test setup functions that get reused (create_test_user etc) for how many queries they do, you might find that your overall test setup spends 20% of it's runtime creating users. When I did this I found that 50% of our test runtime was processing stack traces in logging statements (to show where in the code it was being logged from), modifying it to only put tracebacks on INFO and above cut our total testing time by almost 50%.

> tracking the table ... isn't reliable without [trigger] or [orm]

We use sequences, which is neither of those, and has been very reliable for us.

I included the disclaimer that you need a schema that follows strict sequence name => table name conventions, but that's what we have.

> 20% of runtime creating users

Right -- we also have ~2-3 "stable" rows of users that every test needs, so we can skip re-creating for each test.

Using sequences is clever if you always increment a sequence for every insert, but again my analysis says that unless your table clearing is much slower than mine it's hardly worth it to check, in fact checking the value of every sequence can't take much less than 5ms which is how long it takes to clear every table if empty but is less general.

Integresql has been a very helpful tool for us - we spin up many temporary postgres dbs during e2e testing

https://github.com/allaboutapps/integresql

I've yet to be convinced all of this effort is worth it, compared to the ease of using a repository pattern and just using a fake for tests.

And, like, I don't think we shouldn't be doing these efforts, I guess, as they may still pay technical advancement dividends down the road or help with cheaper, faster QA envs, all-in-one e2e envs, etc... but for the unit test and service test layers, those bottom several layers of your testing pyramid, fakes for your repository interfaces is so much easier and orders of magnitude cheaper.

Once you've done it a time or two, setting up the "clone the db"/"erase the db for each test" pattern isn't that much work (plenty of libraries to help too).

And of course once its set up for a project, adding more tests to it is pretty straightforward. It is slower for each test run, but I had hundreds of tests running serially erasing a MySQL DB before each one and it only took a minute or so, which was well within my tolerance.

So overall I'm a fan; I think there's more benefits than drawbacks. Especially if it's SQLite, where setup is even easier.

Some very significant disadvantages to that approach:

* It's common these days for a single operation to manipulate dozens or even hundreds of database records. Often these records are interrelated because they reference each other. So with fakes, you're faking initial inserts and then faking inserts based on other fake inserts, creating a fragile tree structure of fakes, which models reality very poorly.

* No data type validation on data inserts or updates. Put a string in the integer field? Find out in production.

* No foreign key validation (or just general capacity for checking referential integrity) so you don't find out that you're rows aren't referencing each other correctly until production.

* Similarly, no checks on primary keys, check constraints, triggers do not run, etc.

* Since you're not doing real inserts, you're not doing real updates or deletes on inserted rows. So if those latter operations are referencing the wrong ID, you don't find out until ... you guessed it, production.

* You can try to build up the fidelity of your repository/fake framework, but the more effort you put into it, the closer you are to just rebuilding a database and the slower it'll get. You'll also never achieve actual parity with what your database is doing.

* Building out these big fake frameworks is a lot of work relatively speaking (you didn't need to build out anything for your DB because your non-test code is already using it), and gets you negative gain.

There was a time a long time ago when disk I/O was a lot slower than it is now and maybe there was some argument for a repository/stub system, but that was at least a decade ago, and even then the rationale was thin. These days we have NVMes, and if you're a real speed demon and think those are too slow, you can just put an in-memory SQLite or Postgres in place for your testing and get all the performance advantages with none of the downside.

* You're just describing arranging the test setup, which is independent of storage medium.

* Is your repository interface untyped?

* Depends on the fidelity of your fake, but generally I find FKs to be an antipattern these days.

* What are you checking primary keys FOR? Uniqueness is easy and I avoid more complex constraints and triggers.

* ... I'm beginning to suspect we have a difference in terminology. I'm not saying a mock. A typical DB fake would be array or hash table backed in memory. So an insert is "real" and an update or delete would be too.

* Well, sure, but I can get 95% fidelity for 1% of the resources.

Even if the fake is super-fast compared to postgres (1%) in CPU resources: Why spend extra engineering effort to build something that has less fidelity?

CPU for test runs is cheap... and if you use any AI agent at all, tests runs faster than the agent does work. Why does it matter how much faster they run?

The goal of a regression test suite is to catch issues before you deploy to prod. That 95% is a nagging source of doubt. CAN you just release the code straight to prod? Or not?

Many times integration tests including the real postgres and real migration catches bugs for me before they go to prod.

Personally I would just never go with 95% fidelity in tests. Testing with the real postgres is just so good.

And just to get test coverage for the migrations themselves?

(In my case I also use stored procedures, RLS etc that needs those; with your setup that is just not on the table I think or you loose coverage of critical code.)

If you are unit testing something then sure mock out everything you are not concerned with.

But for service layer tests I don't agree with you, including the actual production repository implementation and the DB is very useful.

It is like e2e tests, just leaving the frontend out. I really like having a lot of such tests to be productive with backend development.

To have a 100 such e2e tests complete quickly, spinning up SQL DBs cheaply is essential.

Also for actual e2e tests with frontend I prefer cheap DB clones rather than reusing DB between tests and having to worry about state between tests.

I think perfect layering was more relevant in the 2000-2010 with less powerful machines. Just making pseudo-integration tests and including more than may be strictly needed is fine. It completes quickly enough. When it breaks it is usually obvious what broke without limiting was code is included in the test.

How do you ensure your SQL queries (either raw or ORM generated) retrieve the correct data?

A lot of business apps are mostly SQL with a thin layer of glue to integrate everything.

I think it depends on what the app does--is it data manipulation heavy (lots of complex relations) or is it compute/algorithm heavy (simpler relations, lots of logic).

Cloning a template is IO-heavy. You can speed it up further by putting postgres on a ramdisk.

Just turning off fsync is basically just as fast as a ramdisk and you can't use up all your memory with one big test.

Turning off synchronous_commit gets you most of the way without ever worrying about data corruption if something crashes mid way through.

https://www.postgresql.org/docs/current/wal-async-commit.htm...

Agreed, benchmarking, I only see a slight speedup on MacOS - https://github.com/leontrolski/postgresql-testing#:~:text=ra...

Peter actually makes this exact point in the project's README. See this section here:

https://github.com/peterldowns/pgtestdb#how-do-i-make-it-go-...

I'd just say that a nice thing about it on disk (even if you disable fsync) is that in case of a failing test, you can examine the post-run state which is occasionally extremely valuable.

Yup! If you keep your laptop on, failing dbs are investigable on tmpfs too — works fine for debugging a few tests in a loop, just can’t run the tests, sleep the computer, have lunch, and come back.

Ah yep, good point. I was confusing "ramdisk" versus just "ephemeral in-memory".

Anything similar possible with MySQL?

It doesn't look like MySQL has any concept like a template database. You could still migrate up a pristine database, use mysqldump to produce a schema, then load that into a series of test databases. It of course won't be as quick as the low level copies that Postgres is doing with a template database, but it'd be faster than re-running all your migrations.

OOC — I've been trying to gather real world anecdotes on who is using MySQL these days given that even some of its biggest traditional champions like PlanetScale are talking a lot more about Postgres recently. Are you using MySQL as part of an existing project that was started years ago, or do you still intend to use it for new things going forward?

MySQL has a lot of broken "features" that sadly some existing legacy code might be relying upon and there just isn't engineering capacity required to move to a saner DB, so they have to make do.

I had a legacy project on MySQL that turned out to only "work" because string lookups were case-insensitive in that particular version or our configuration. Moving to Postgres and its correct behavior suddenly exposed a lot of bugs we needed to fix before we could complete the migration.

Case sensitivity/insensitivity is a property of the collation, which is configurable on a per-column basis.

In Postgres the default collation is case-sensitive, and in MySQL the default collation is case-insensitive, but this does not mean one is "correct behavior" and the other is not. MySQL's out-of-the-box collation support is arguably a lot more thorough than Postgres's!

In theory you can do filesystem-copy-speed population of large seed data using InnoDB's transportable tablespaces feature. For the test db use-case specifically though, unfortunately it's far less ergonomic than Postgres template databases.

Reply on news.ycombinator.com