8 ms·
PostgreSQL Templates
- KingOfCoders 6y agoWe're using PostgreSQL teams to create a database from a template for every integration test worker (Jest). This way every worker works on their own database. The speedup was 5x-7x as tests are now running in parallel.
- jayd16 6y agoDo you store test data in the template? My team uses a migration framework (in our case Flyway) such that we can easily create completely fresh DBs on a factory fresh(+ user roles) instance of Postgres. We do this for our testing as well. This means the schema tools are always tested as well. Is there a benefit to using templates? They're faster perhaps?
- flavor8 6y agoWe use flyway to create a template. Then each test that mutates the DB automatically invokes a cleanup routine which recreates the test DB from the template. You get into hot water around the connection pooling, but it's much, much faster than refreshing using flyway.
- tln 6y agoI'm using templates as well for the speedup in testing. We build a postgresql docker image with the migrations + seed data, then create a few databases for parallel testing. When migration or seed files have changed, rules rebuild the image. That way the price of migration (about 2 minutes for our Django app) isn't paid on every test run. Creating a DB from template is 2 seconds.
- KingOfCoders 6y agoFor 10 workers it takes <0.5 seconds to create 10 databases from the template. Test data is created from within tests.
- Xeronate 6y agoI’ve seen two people mention this for integration tests and that sounds great, but how do you handle system tests? As an example of the difference, my team would use an integration to test a repository class individually and a system test to check everything from the top level api endpoint and down. I think the top level thing is important because it makes sure we are getting the right connection string from our database provider and what not.
- KingOfCoders 6y agoWhere do you see problems with system tests? Shouldn't they work the same with one database per worker/thread?
- thinkingkong 6y agoI've used this in the past for testing depending on how much seeding was required prior to the test, and how much parallelism was required. Other than that I haven't been able to find any useful real world scenarios to exploit this functionality. I suppose if you wanted to have a source database and a bunch of children and could prevent connections to the source it might be helpful... somehow?
- Squid_Tamer 6y agoI worked on a SaaS project who's customers are companies in an old-fashioned industry. A common requirement was that their data shouldn't be stored in the same database tables as any other customer. They were fine with it being co-located on the same cloud servers, using the same credentials... as long as their data wasn't on the same tables. So I used postgres templates to make a database per customer. Didn't make much sense to me either but whatever pays the bills I guess.
- Tostino 6y agoThere are good reasons for that. At an old company I was at, over a couple years I was around there were multiple (~5) instances of clients asking for custom reports or data dumps, and they were sent data from other clients (as well as their own) because a tenant id was missed in some joins, or something similar. Yes, sloppy practices all around allowed that to happen. That being said, it would have been much less likely to happen if data segregation were enforced at the database level (using either RLS, or separate database/schema/tables per-client).
- outworlder 6y agoJust don't fall into the trap of creating a database schema per customer. Having different databases is better (as TXID is not shared)
- chrismorgan 6y agoCan you elucidate or provide some reference about this? I’m not familiar with any problems in this space (it’s not clear to me what problems a shared TXID could cause), and schemas seemed neat.
- thinkingemote 6y agoi use templates to create a "quick save" backup of a database quickly during development: createdb -T old_db new_db
- silviogutierrez 6y agoNeat trick. Is this any faster than pg_dump and pg_load? Note: there's plenty of optimization you can apply to pg_dump and even use pg_restore, none applied below. Like so: function save_database() { mkdir -p /tmp/database-snapshots DATABASE_NAME=$PROJECT_NAME pg_dump -Fc "$DATABASE_NAME" >"/tmp/database-snapshots/$DATABASE_NAME.dump" } function restore_database() { DATABASE_NAME=$PROJECT_NAME DATABASE_DUMP="/tmp/database-snapshots/$DATABASE_NAME.dump" if [ ! -f "$DATABASE_DUMP" ]; then echo "No dump to restore" return else psql postgres -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid()' psql postgres -c "DROP DATABASE IF EXISTS \"$DATABASE_NAME\"" psql postgres -c "CREATE DATABASE \"$DATABASE_NAME\"" pg_restore -Fc -j 8 -d "$DATABASE_NAME" "$DATABASE_DUMP" fi }
- gkop 6y agoYes, it's significantly faster because it doesn't require rebuilding indexes (among other reasons probably). Downside is it consumes more disk space. Stellar is a tool that wraps the template mechanism, and has some benchmarks: https://github.com/fastmonkeys/stellar https://github.com/fastmonkeys/stellar
- FanaHOVA 6y agoIs this similar to doing something like `rails db:schema:load` and then `rails db:seed`? i.e. set a brand new db to a certain schema + preload a set of fixtures into it?
- mccolin 6y agoThe effect will be relatively similar -- you arrive at a new database with the proper schema/structure in place, but the difference is in execution. Doing this _within_ the database engine is likely 10x faster than loading the schema through a client library making a series of CREATE and ALTER statements (ala the schema:load rake task).
- majodev 6y agoWe are using PostgreSQL templates to speed up our integration tests. This allow us to have full blown (and non mocked) databases which are isolated to each parallel running test. We have recently open-sourced our core-server managing this (go): https://github.com/allaboutapps/integresql https://github.com/allaboutapps/integresql Projects already utilizing Go and PostgreSQL may easily adapt this concept for their testing via https://github.com/allaboutapps/integresql-client-go https://github.com/allaboutapps/integresql-client-go
- miohtama 6y agoThis is super nice! I have seen similar solutions for Python in the past, but having an easy API + Docker makes this generic and language agnostics. Have you looked to tweak PSQL parameters to make it faster for test runs e.g. by disabling disk writes?
- majodev 6y agoYep, disabling fsync, synchronous_commit and full_page_writes ONLY for this PostgreSQL integration testing strategy gives us an additional boost of ~35% in overall testing speed. https://github.com/allaboutapps/go-starter/blob/master/docker-compose.yml#L69 https://github.com/allaboutapps/go-starter/blob/master/docke... Regarding being language agnostic: YES, clients for languages other than go are very welcome. The integresql server actually provides a trivial RESTful JSON api and should be fairly easy to integrate with any language. This is our second iteration regarding this PostgreSQL test strategy, the last version came embedded with our legacy Node.js backend stack and wasn't able to handle tests in parallel, which became an additional hard requirement for us. See our go-starter project for infos on how we are using integresql, e.g. our WithTestDatabase/WithTestServer utility function we use in all tests to "inject" the specific database: https://github.com/allaboutapps/go-starter/blob/master/internal/test/testing.go#L39 https://github.com/allaboutapps/go-starter/blob/master/inter...
- progval 6y agoHow does this compare (performance-wise) to simply truncating all tables?
- deleted 6y ago[deleted]
- stansler 6y agoAnother approach is to use thin clones of Postgres to create disposable databases for fast iterations. We've made Database Lab tool working on top of CoW file systems (ZFS, LVM) that is capable of provision multiterabyte Postgres instances in seconds. The goal is to use such clones to verify database migrations and optimize SQL queries against production-size data. See our repo: https://gitlab.com/postgres-ai/database-lab https://gitlab.com/postgres-ai/database-lab.
- spapas82 6y agoDoes anybody know why template1 isn't utf8 by default? Because of that I always need to create new databases from template0 to use utf8 f.e like this: `createdb -T template0 -E UTF8 newdb`