Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Pgtestdb's template cloning approach to testing is fast

brandur.org

Post reply on HN
Print view View original

31–40 of 54 posts

Re: Pgtestdb's template cloning approach to testing is fast

#31

Post by jtwaleson »

rgbrgb wrote: post #16

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. Integr…

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!

Re: Pgtestdb's template cloning approach to testing is fast

#32

Post by brandur »

pmontra wrote: post #28

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 t…

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.

Re: Pgtestdb's template cloning approach to testing is fast

#33

Post by TexanFeller »

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.

Re: Pgtestdb's template cloning approach to testing is fast

#35

Post by eximius »

brandur wrote: post #18
eximius wrote: post #14

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…

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 t…

* 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.

Re: Pgtestdb's template cloning approach to testing is fast

#36

Post by dagss »

eximius wrote: post #14

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…

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.

Re: Pgtestdb's template cloning approach to testing is fast

#37

Post by dagss »

eximius wrote: post #35
brandur wrote: post #18

Earlier quoted context omitted.

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 t…

* 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 say…

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.)

Re: Pgtestdb's template cloning approach to testing is fast

#38

Post by arialdomartini »

rgbrgb wrote: post #16

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. Integr…

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...

Re: Pgtestdb's template cloning approach to testing is fast

#39

Post by spellboots »

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...

Re: Pgtestdb's template cloning approach to testing is fast

#40

Post by ltbarcly3 »

stephen wrote: post #21
ltbarcly3 wrote: post #3

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 ti…

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%.

Post reply on HN