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

41–50 of 54 posts

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

#41

Post by nijave »

leontrolski wrote: post #11
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…

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

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

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

#42

Post by Nextgrid »

brandur wrote: post #29
_1tan wrote: post #25

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…

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.

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

#43

Post by nijave »

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…

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

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

#44

Post by brandur »

spellboots wrote: post #39

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

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

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

#45

Post by CodesInChaos »

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

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

#46

Post by CodesInChaos »

peterldowns wrote: post #22

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

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

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

#47

Post by CodesInChaos »

spellboots wrote: post #39

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

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?

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

#48

Post by spellboots »

CodesInChaos wrote: post #47
spellboots wrote: post #39

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

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.

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

#49

Post by spellboots »

brandur wrote: post #44
spellboots wrote: post #39

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

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

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

#50

Post by stephen »

ltbarcly3 wrote: post #40
stephen wrote: post #21

Earlier quoted context omitted.

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

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

Post reply on HN