Ephemeral Postgres Databases
eradman.com
Ephemeral Postgres Databases
1–10 of 39 posts
Re: Ephemeral Postgres Databases
#2Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
Re: Ephemeral Postgres Databases
#3Or in Go, with full databases: https://github.com/rubenv/pgtest/
Re: Ephemeral Postgres Databases
#4Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
At a $previous_job I basically also did what the post is describing.
The "best" thing we did was actually using a "template database": https://www.postgresql.org/docs/14/manage-ag-templatedbs.htm...
We would start a Postgres Process. We would create a new database, run all of our migrations and basic data bring up. Then we would create a new Database per Test Suite, using the one we just ran migrations as the Template.
This meant the initial bring up was a few seconds, but then each test suite would get a new database in a dozen milliseconds (IIRC).
Re: Ephemeral Postgres Databases
#5Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
At a $previous_job I basically also did what the post is describing. The "best" thing we did was actually using a "template database": https://www.postgresql.org/docs/14/manage-ag-templatedbs.htm... We would start a Postgres Process. We would create a new database, run all of our migrations and basic data bring up. Then we would create a new Database per Test Suite, using the one we just ran migrations as the Templat…
That's probably more or less the same thing in terms of what actually happens in PostgreSQL.
All things considered, an actual database probably gives you the least gray hair, but with some careful test setup I have had good success using the savepoint/rollback trick (and it trivially supports nested fixtures as well).
Re: Ephemeral Postgres Databases
#6My test suite has the schema and seed data in a template database then runs
CREATE DATABASE testX TEMPLATE seed;
Re: Ephemeral Postgres Databases
#7Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
The catch with this is you can't test any behaviour with deferred constraints. Any activity that doesn't take place until the commit will never run.
Re: Ephemeral Postgres Databases
#8Another approach is to use Database Lab (https://gitlab.com/postgres-ai/database-lab). Our tool allows deploying disposable Postgres databases in seconds using REST API, CLI, or GUI. The difference is that we provide clones with snapshots of full-sized data. For example, you may have dozens of clones of your production databases (with masked sensitive data) to use as staging servers, database migration verification, tests, and all that provisioned in a couple of seconds.
More about using full-sized Postgres clones for migration testing and why is that important: https://postgres.ai/products/database-migration-testing
Re: Ephemeral Postgres Databases
#9Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.
The catch with this is you can't test any behaviour with deferred constraints. Any activity that doesn't take place until the commit will never run.
Should be possible with "SET CONSTRAINTS".
Re: Ephemeral Postgres Databases
#10My test suite has the schema and seed data in a template database then runs CREATE DATABASE testX TEMPLATE seed; ...which takes about 1 second per test run. The seed data and schema is baked into a docker image, and recreated whenever there are new migration files. Starting the docker image is slow but that doesn't happen on every test run.
That's a pretty reasonable approach, and similar in spirit to what I find to work well:
- Create a template with necessary extensions, or just install them to "template1" which is the template if you don't explicitly specify a template. Installing PostGIS, for example, takes a few seconds - which is annoying if you create the schema from scratch in test runs. (`create extension if not exists postgis` can still be around in your schema, it'll just return right away) - Create a template for the test session, based on the template you've pre-installed extensions in, and apply the schema there. - Create a database based on the second template for whatever scope makes sense for your test.
If your Postgres cluster is only serving test workloads, `fsync=off` can speed up things as well. (Which a stock postgresql.conf will point out can cause irrecoverable data loss, which I don't care about for test data)