5 ms·

Pgtestdb's template cloning approach to testing is fast

69 points by brandur 2mo ago

hugodutka 2mo ago
Cloning a template is IO-heavy. You can speed it up further by putting postgres on a ramdisk.
- ltbarcly3 2mo ago
  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.
  - koolba 2mo ago
    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.html https://www.postgresql.org/docs/current/wal-async-commit.htm...
  - leontrolski 2mo ago
    Agreed, benchmarking, I only see a slight speedup on MacOS - https://github.com/leontrolski/postgresql-testing#:~:text=ramdisk https://github.com/leontrolski/postgresql-testing#:~:text=ra...
- brandur 2mo ago
  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-faster 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.
  - peterldowns 2mo ago
    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.
    - brandur 2mo ago
      Ah yep, good point. I was confusing "ramdisk" versus just "ephemeral in-memory".
ltbarcly3 2mo ago
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
Natalia724 2mo ago
[dead]
tux3 2mo ago
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.
- brandur 2mo ago
  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 https://brandur.org/fragments/go-test-tx-using-t-cleanup
eximius 2mo ago
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.
- bbkane 2mo ago
  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.
- brandur 2mo ago
  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.
rgbrgb 2mo ago
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.
- benoau 2mo ago
  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.
- jtwaleson 2mo ago
  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!
- arialdomartini 2mo ago
  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-up https://arialdomartini.github.io/when-im-done-i-dont-clean-u...
peterldowns 2mo ago
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.
- stephen 2mo ago
  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/ https://boringsql.com/posts/instant-database-clones/
  - peterldowns 2mo ago
    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!
- brandur 2mo ago
  No worries Peter. And thanks for putting together pgtestdb — such a great project! This conversion sprint was a fun little experiment.
- CodesInChaos 2mo ago
  The pgtestdb readme claims 20ms setup, while OP claims 100ms. Where does that difference come from? A different postgres setup (in memory vs disk)?
_1tan 2mo ago
Anything similar possible with MySQL?
- brandur 2mo ago
  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?
  - Nextgrid 2mo ago
    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.
    - shadim80 2mo ago
      [flagged]
    - evanelias 2mo ago
      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!
pmontra 2mo ago
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.
- brandur 2mo ago
  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.
TexanFeller 2mo ago
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.
Lapalux 2mo ago
Integresql has been a very helpful tool for us - we spin up many temporary postgres dbs during e2e testing https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql
spellboots 2mo ago
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.html#GUC-FILE-COPY-METHOD https://www.postgresql.org/docs/18/runtime-config-resource.h...
- brandur 2mo ago
  Thanks for that — I didn't know about `clone` (and important to note it's non-default) and now intend to try it out.
  - spellboots 2mo ago
    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
- CodesInChaos 2mo ago
  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?
  - spellboots 2mo ago
    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.
CodesInChaos 2mo ago
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)?

69 points by brandur 2mo ago

hugodutka 2mo ago

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

ltbarcly3 2mo ago
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.
- koolba 2mo ago
  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.html https://www.postgresql.org/docs/current/wal-async-commit.htm...
- leontrolski 2mo ago
  Agreed, benchmarking, I only see a slight speedup on MacOS - https://github.com/leontrolski/postgresql-testing#:~:text=ramdisk https://github.com/leontrolski/postgresql-testing#:~:text=ra...
brandur 2mo ago
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-faster 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.
- peterldowns 2mo ago
  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.
  - brandur 2mo ago
    Ah yep, good point. I was confusing "ramdisk" versus just "ephemeral in-memory".

ltbarcly3 2mo ago

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

Natalia724 2mo ago

[dead]

tux3 2mo ago

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.

brandur 2mo ago
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 https://brandur.org/fragments/go-test-tx-using-t-cleanup

eximius 2mo ago

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.

bbkane 2mo ago
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.
brandur 2mo ago
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.

rgbrgb 2mo ago

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.

benoau 2mo ago
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.
jtwaleson 2mo ago
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!
arialdomartini 2mo ago
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-up https://arialdomartini.github.io/when-im-done-i-dont-clean-u...

peterldowns 2mo ago

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.

stephen 2mo ago
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/ https://boringsql.com/posts/instant-database-clones/
- peterldowns 2mo ago
  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!
brandur 2mo ago
No worries Peter. And thanks for putting together pgtestdb — such a great project! This conversion sprint was a fun little experiment.
CodesInChaos 2mo ago
The pgtestdb readme claims 20ms setup, while OP claims 100ms. Where does that difference come from? A different postgres setup (in memory vs disk)?

_1tan 2mo ago

Anything similar possible with MySQL?

brandur 2mo ago
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?
- Nextgrid 2mo ago
  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.
  - shadim80 2mo ago
    [flagged]
  - evanelias 2mo ago
    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!

pmontra 2mo ago

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.

brandur 2mo ago
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.

TexanFeller 2mo ago

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.

Lapalux 2mo ago

Integresql has been a very helpful tool for us - we spin up many temporary postgres dbs during e2e testing https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql

spellboots 2mo ago

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.html#GUC-FILE-COPY-METHOD https://www.postgresql.org/docs/18/runtime-config-resource.h...

brandur 2mo ago
Thanks for that — I didn't know about `clone` (and important to note it's non-default) and now intend to try it out.
- spellboots 2mo ago
  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
CodesInChaos 2mo ago
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?
- spellboots 2mo ago
  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.

CodesInChaos 2mo ago

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