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

21–30 of 54 posts

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

#21

Post by stephen »

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.

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

#22

Post by peterldowns »

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.

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

#23

Post by stephen »

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…

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/

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

#24

Post by brandur »

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…

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

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

#26

Post by peterldowns »

brandur wrote: post #8
hugodutka wrote: post #2

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

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.

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

#27

Post by peterldowns »

stephen wrote: post #23
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…

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!

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

#28

Post by pmontra »

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.

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

#29

Post by brandur »

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

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

#30

Post by brandur »

peterldowns wrote: post #26
brandur wrote: post #8

Earlier quoted context omitted.

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".
Post reply on HN