Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ephemeral Postgres Databases

eradman.com

Post reply on HN
Print view View original

11–20 of 39 posts

Re: Ephemeral Postgres Databases

#11

Post by latch »

> For some time proponents of unit testing have asserted that unit tests should not touch a real database

Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind).

Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION, but I just bake the following in local/test/CI:

     fsync = off
     synchronous_commit = off
Some other things I've seen / done in the name of testing with PG:

- Use unlogged tables. Maybe it's a bit faster, never really seemed to make much a different.

- Drop all foreign keys. This has significant non-performance impact. On the downside, it materially changes the environment that your tests are running vs your system: test could now pass for otherwise invalid code. On the plus side, it makes setting up fake data _a lot_ easier.

- Run tests with a pool_size of 1. This catches cases where you start a transaction, but forget to use the transaction object, e.g.:

    db.begin_transaction(tx ->
       tx.query("update x")
       db.query("delete y") // should have been tx.query
    )
- A robust Factory library (NIH or OSS) is pretty probably the most important thing you can do

- If you can't pre-seed all the test data, try to write _all_ your tests without ever having to truncate/delete tables between tests. This (a) avoids the slow delete/truncate and (b) lets you run tests in parallel. This means using random keys to avoid duplicates (obviously uuid is the simple answer here) and not selecting all data from a table when verifying/asserting (since who knows what's going to be in there).

Re: Ephemeral Postgres Databases

#12

Post by kardianos »

I also do something like this, but with SQL Server. Highly recommended. Except rather then going from a seed or backup, the schema and default data is defined in the application, then the data is entered in the test setup, the tables are sorted topographically, and inserted all at once, then the unit test runs (can be combined with a table driven test).

Robust, highly recommended.

Re: Ephemeral Postgres Databases

#13

Post by Foobar8568 »

mborch wrote: post #2

Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.

Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)

Re: Ephemeral Postgres Databases

#14

Post by gregplaysguitar »

latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

> write _all_ your tests without ever having to truncate/delete tables between tests

This is exactly what we do, and it works really well. We essentially spin up a new tenant for each test suite. Forcing all tests to pass regardless of what other unrelated data is in the db is a great way to ensure cross-tenant data isolation.

The other underrated benefit is that you don't need a separate test database for local development - local dev instance and tests can use the same db. This means you can work on db changes in one place and see their impact on both tests and the running application.

Re: Ephemeral Postgres Databases

#15

Post by Kinrany »

stansler wrote: post #8

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

This sounds like a different problem, not a different approach: "in seconds" is only good for integration tests, and the topic is about (sub-second) unit tests.

Re: Ephemeral Postgres Databases

#16

Post by switchbak »

latch wrote: post #11

> For some time proponents of unit testing have asserted that unit tests should not touch a real database Is that still a belief in some circles? I feel like the shift away from this started like 15 years ago (largely because of RoR in my mind). Anyways, this essentially launches a pg instance with a postgresql.conf that is aimed for speed (at the risk of possible data loss/corruption). DO NOT DO THIS IN PRODUCTION,…

For real _unit_ tests? I would argue it's still a good distinction.

We use an embedded postgres in our DB tests, and we call those 'Integration Tests' and run them separately than the pure unit tests. While still tremendously valuable, they do take a bit longer to run, and currently aren't written to allow parallel tests running.

We've had a typical habit of writing most tests that hit the DB. Since applying a bit more discipline to remove the DB requirement, we've found it's a more pleasant experience with the quicker feedback loop in place.

Your last point is a good one - tests that can run in isolation and are agnostic to the presence of other data (and ideally clean up after themselves) tend to be handy.

Re: Ephemeral Postgres Databases

#18

Post by luhn »

Foobar8568 wrote: post #13
mborch wrote: post #2

Another trick is to use (possibly nested) savepoints and rollback after each test, never actually committing any data.

Sql server has snapshot database for something similar, and you don't have to play with transactions (that may or not breaks everything in case of errors)

If there's an error, the transaction just rolls back. I've been using this strategy for years and I have never once had a transaction "break everything."

Re: Ephemeral Postgres Databases

#20

Post by stickfigure »

tln wrote: post #6

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

I do the same thing, though it's quite a bit faster than 1s (perhaps my schema is smaller).

No docker though. I just run my migration scripts against the template database. In CI it runs every time; for local testing, I drop the template db and run the migrations.

The only annoying thing is that my local pg instance fills up with test runs. The good thing is that it's really easy to go back and inspect the database after a test run. But periodically I have to run a script that drops them all.

Post reply on HN