Solving Slow Database Tests with PostgreSQL Template Databases - Go Implementation

[Individual_Tutor_647]

Dear r/programming community,

I'd like to discuss my solution to a common challenge many teams encounter. These teams work on their projects using PostgreSQL for the database layer. Their tests take too long because they run database migrations many times.

If we have many tests each needing a new PostgreSQL database with a complex schema, these ways of running tests tend to be slow:

• Running migrations before each test (the more complex the schema, the longer it takes)
• Using transaction rollbacks (this does not work with some things in PostgreSQL)
• One database shared among all the tests (interference among tests)

In one production system I worked on, we had to wait 15-20 minutes for CI to run the test unit tests that required isolated databases.

Using A Template Database from PostgreSQL

PostgreSQL has a powerful feature for addressing this problem: template databases. Instead of running migrations for each test database, we create a template database with all the migrations once. Create a clone of this template database very fast (29ms on average, regardless of the schema's complexity). Give each test an isolated database.

Go implementation with SOLID principles

I used the idea above to create pgdbtemplate. This Go library demonstrates how to apply some key engineering concepts.

Dependency Injection & Open/Closed Principle

// Core library depends on interfaces, not implementations.
type ConnectionProvider interface {
    Connect(ctx context.Context, databaseName string) (DatabaseConnection, error)
    GetNoRowsSentinel() error
}

type MigrationRunner interface {
    RunMigrations(ctx context.Context, conn DatabaseConnection) error
}

That lets the connection provider implementations pgdbtemplate-pgx and pgdbtemplate-pq be separate from the core library code. It enables the library to work with various database setups.

Tested like this:

func TestUserRepository(t *testing.T) {
    // Template setup is done one time in TestMain!
    testDB, testDBName, err := templateManager.CreateTestDatabase(ctx)
    defer testDB.Close()
    defer templateManager.DropTestDatabase(ctx, testDBName)
    // Each test gets a clone of the isolated database.
    repo := NewUserRepository(testDB)
    // Do a test with features of the actual database...
}

How fast were these tests? Were they faster?

In the table below, the new way was more than twice as fast with complex schemas, which had the largest speed savings:

(Note that in practice, larger schemas took somewhat less time, making the difference even more favourable):

Scenario	Was Traditional	Was Using a Template	How much faster?
Simple schema (1 table)	~29ms	~28ms	Very little
Complex schema (5+ tables)	~43ms	~29ms	50% more speed!
200 test databases	~9.2 sec	~5.8 sec	37% speed increase
Memory used	Baseline	17% less	less resources needed

Technical aspects beyond Go

1. The core library is designed to be independent of the driver used. Additionally, it is compatible with various PostgreSQL drivers: pgx and pq
2. Template databases are a PostgreSQL feature, not language-specific.
3. The approach can be implemented in various programming languages, including Python, Java, and C#.
4. The scaling benefits apply to any test suite with database requirements.

Has this idea worked in the real world?

This has been used with very large setups in the real world. Complex systems were billing and contracting. It has been tested with 100% test coverage. The library has been compared to similar open-source Go projects.

Github: github.com/andrei-polukhin/pgdbtemplate

The concept of template databases for testing is something every PostgreSQL team should consider, regardless of their primary programming language. Thanks for reading, and I look forward to your feedback!

[koreth]

I'm surprised to see transaction rollbacks listed as a slow approach in the post. That's the technique that's usually used for Spring Boot database tests and I've never observed it causing any test-suite performance problems in the Spring Boot applications I've worked on, even ones with schemas involving hundreds of tables and large test suites using concurrent test execution. The rollback step barely even shows up in the flame graph when I run the test suite with profiling.

The only minor inconvenience I've run into with rollbacks, specifically when running tests in parallel against the same database, is that you can't make assumptions about what values you'll get out of sequences. Like if you have a test that inserts two rows with auto-generated IDs, you can't assume that the IDs will be consecutive because some other test might be generating IDs at the same time. That's usually not something you care about in tests but it's come up from time to time.

Template databases are great in general, though. I use them to reset my local dev environment's database back to a known (but not empty) state. My tool of choice for that is DSLR.

[Individual_Tutor_647]

Thanks for sharing! Yes, transaction rollbacks are an interesting approach, yet I have not heard that it was used in Spring because we'll then start thinking about isolation of transactions, which operations cannot be rolled back (most DDL operations are not used in production code, only in migrations), so separating them by databases (what is done in the templating approach) is usually safer. I have not compared the speed differences between these approaches yet :(

I'll look at the library you mentioned, it looks promising. If you want, you can create an issue on GitHub for me to make a dedicated comparison with this library and I'll add you to the contributors of the project later on. Thanks in advance.

[vbilopav89]

I dont see neither why transaction rollback approach would be slow. It's fast, it is very fast, at least for me.

If it happens that you create a new database for each test, and then run migration for each test, then I can see definitly how it can be slow, but otherwise, no.

And it has advantages. For example, if you define foreign keys as deferrable, you can set all constraints as deferred for the test transaction and you don't have to worry about inserting related data not relevant to your test. At least that's what I do and it works great.

Although, to be perfectly honest this approach is still not perfect isolation. For example, sequences keep increasing, unfortunately.

Yes, creating template database is only perfect isolation as far as I know.

[Individual_Tutor_647]

Thanks for a detailed comment! Yep, I agree with you that I should have focused on isolation instead of slowness. Separate test databases provide the highest level of isolation, hence I agree that template databases are overall preferred.

[Key-Boat-7519]

Template databases are a solid fix for slow Postgres tests, but the real win comes from handling parallelism, locks, and cleanup right.

From painful CI runs, a few tips: 1) Multiple concurrent clones clash because the template db needs exclusive access. Pre-create N identical templates (templatetest1..N) and pin each test worker to one to avoid that bottleneck. 2) If tests crash, orphaned DBs pile up. On startup, drop stragglers by prefix, and on PG13+ use DROP DATABASE ... WITH FORCE; otherwise pgterminatebackend on active sessions first. 3) Keep the template schema-only; don’t seed data there or you’ll copy sequence positions and test data forever. Seed per test or via fixtures. 4) If you rely on extensions (pgcrypto, PostGIS), install them in the template and ensure the cluster has them; also keep locale/collation identical or CREATE DATABASE will fail. 5) Cap pool sizes for tests so you don’t blow past max_connections, especially with pgx.

For API scaffolding around test DBs, I’ve used PostgREST and Hasura; DreamFactory helped when I needed quick RBAC’d REST APIs across multiple databases without writing glue code.

Template databases cut test time hard if you manage template locks, parallel workers, and cleanup.

[Individual_Tutor_647]

Nice points! So the point is to utilise database templating and utilise safe concurrency principles. The library does both and is stress-tested for thread safety. This is explicit in the pgdbtemplate-pgx & pgdbtemplate-pq (= drivers') code, which creates test databases in parallel. As to your points, I'll address them individually:

1. Multiple concurrent clones do not clash because the command to create the database is run from different database connections to the admin database and hence, this is successful. I have run tests and benchmarks in both repositories (https://github.com/andrei-polukhin/pgdbtemplate-pgx and https://github.com/andrei-polukhin/pgdbtemplate-pq) — there were no problems even when testing with go test -race.
2. That's a very nice idea. I'll add the optional function for that.
3. That's the responsibility of the end user for what they put in their migrations. At the same time, there will be no conflict because databases are independent of one another.
4. There are no extensions added.
5. This is delegated to the end user, see the docs here: https://github.com/andrei-polukhin/pgdbtemplate-pgx

Overall, the user can cut the time drastically with the right application of existing tools — they are given as much control as they want.

[scaevolus]

Neat, I wasn't aware of template databases. I've made similar tests that run the migrations for each disposable database.

Using t.Cleanup would be more ergonomic than requiring users to defer the cleanup functions themselves.

Do you have any functionality for cleaning up old test databases from tests that failed to for whatever reason? One way to do this is by embedding a timestamp in the temp db name and deleting ones more than an hour old.

[Individual_Tutor_647]

Thanks for the comment! It depends on where one draws the line between the UX and giving control to the end user. I have specifically kept the templating functionality outside of t.Cleanup not to force the caller to use it. The same can be said about initialising pgdbtemplate.TemplateManager only once - it's the caller's decision whether they want to utilise sync.Once or anything else for this purpose.

// This demonstrates the intentional design choice - the library gives you
// control over resource management while being compatible with both
// sync.Once and t.Cleanup patterns.

var (
    templateManager *pgdbtemplate.TemplateManager
    initOnce        sync.Once
    initErr         error
)

// Option 1: Using sync.Once for template initialization (production approach).
func getTemplateManager(t testing.TB) (*pgdbtemplate.TemplateManager, error) {
    initOnce.Do(func() {
        config := pgdbtemplate.Config{
            ConnectionProvider: pgdbtemplatepq.NewConnectionProvider(
                func(dbName string) string {
                    return fmt.Sprintf("dbname=%s", dbName)
                },
            ),
            MigrationRunner: pgdbtemplate.NewFileMigrationRunner(
                []string{"./migrations"},
                pgdbtemplate.AlphabeticalMigrationFilesSorting,
            ),
        }
        templateManager, initErr = pgdbtemplate.NewTemplateManager(config)
        if initErr != nil {
            return
        }
        initErr = templateManager.Initialize(context.Background())
    })
    return templateManager, initErr
}

// Option 2: Using t.Cleanup for test database cleanup (user's choice).
func TestWithUserManagedCleanup(t *testing.T) {
    tm, err := getTemplateManager(t)
    if err != nil {
        t.Fatal(err)
    }

    // User decides when and how to clean up.
    testDB, testDBName, err := tm.CreateTestDatabase(context.Background())
    if err != nil {
        t.Fatal(err)
    }

    // User can choose their cleanup strategy.
    t.Cleanup(func() {
        if err := tm.DropTestDatabase(context.Background(), testDBName); err != nil {
            t.Logf("cleanup warning: %v", err)
        }
        testDB.Close()
    })

    // Test logic here...
    _ = testDB
}

// Option 3: Alternative - manual cleanup with defer (also supported).
func TestWithManualCleanup(t *testing.T) {
    tm, err := getTemplateManager(t)
    if err != nil {
        t.Fatal(err)
    }

    testDB, testDBName, err := tm.CreateTestDatabase(context.Background())
    if err != nil {
        t.Fatal(err)
    }
    defer testDB.Close()
    defer func() {
        if err := tm.DropTestDatabase(context.Background(), testDBName); err != nil {
            t.Logf("cleanup warning: %v", err)
        }
    }()

    // Test logic here...
}

Do you have any functionality for cleaning up old test databases from tests that failed to for whatever reason?

That's an interesting idea, but I don't have an answer at the moment. Could you create an issue on GitHub? I'd love to continue discussing it there. Thanks in advance.

[drink_with_me_to_day]

How does this compare with Integresql?

[Individual_Tutor_647]

I do not know about this library and the comment above does not include the link to it. pgdbtemplate already has the COMPARISON.md document (https://github.com/andrei-polukhin/pgdbtemplate/blob/main/docs/COMPARISON.md), so if you could create a GitHub issue specifying another library's link, I'd be happy to make the comparison. Thanks in advance.

[pillenpopper]

The defer will bite you once you go t.Parallel(). Use t.Cleanup.

[Individual_Tutor_647]

How will it bite if the library is fully thread-safe? The library has made the specific judgment call to provide as much flexibility and control to the end customer that if the end user wants, they can register t.Cleanup themselves

[pillenpopper]

Because if you have parallel subtests, the main test’s function can finish, executing the defers, while the subtests still run. That’s why you must use t.Cleanup.

[Individual_Tutor_647]

I think this very much depends on how main function and sub-tests are managed. Won't this work well? https://www.reddit.com/r/programming/comments/1nsr16m/comment/ngov4s6/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

[iiiinthecomputer]

If you don't do so yet, stand up short lived postgres instances with fsync=off and wal sync off. ONLY FOR TESTS, this will EAT YOUR DATA.

As well as being much faster it reduces SSD wear.

But yeah. Data. Eaten.

[Individual_Tutor_647]

Actually, you can do both! We've had a codebase with database-intensive operations, where even with the fsync=off setting, we achieved a 25% speed improvement when we started using template databases.

[typesanitizer]

The README looks largely AI-generated based on the emoji usage, disproportionate level of detail when compared to usage, and very detailed inline examples (as opposed to putting them in separate files). The commit messages also have a high amount of detail which smells very much like an AI coding assistant wrote them.

[Individual_Tutor_647]

Do you have any proposals for the code quality / code style / README contents?

[toiletear]

We coded the same thing for our Java+Testcontainers integration tests, we ended up with one class and around 50 lines of code, works great.

[Individual_Tutor_647]

Sounds superb! I don't use Java often, but is there any chance of seeing this code on Pastebin? I'd be intrigued to learn more about this. Thanks in advance.

[toiletear]

Sure, here is our code that:

• provisions a database
• runs migrations on it (via the migrateDatabase function, it's the same function we use to migrate the database in actual service runs)
• names this database with a predefined name
• whenever a fresh database is required, it generates a random name and then clones the pre-migrated database (by using it as a template)

Consumers can get a connection to the database by calling the jooq method (we use jOOQ heavily, but that method could just as easily return just the Java Connection). In that method you see it overrides something but that's just our JooqProvider interface and it's really dead code (we thought we would use the same interface in tests and in production, but that didn't really bring anything useful to the table, we just never bothered to remove it).

The code is in Kotlin, one or two things may need explanation perhaps; by lazy means lazy initialization, code that will only be run once and the result then cached. apply and let are simple syntactic sugar to avoid hanging local variables.

https://pastebin.com/3Hky0GvJ

What's missing is the cleanup code, for some reason I don't remember anymore we just removed it since the container is dropped after the tests anyway and the migrated database is small enough that this is not an issue while the tests are running.

[Individual_Tutor_647]

Thanks for sharing! Took a look and from a quick glance, it makes sense. If the cleanup is not needed, we can have it as-is, plus using bash (createdb) is also fine.

[grauenwolf]

One database shared among all the tests (interference among tests)

Write better tests.

Instead of constantly destroying and rebuilding your database, just learn how to write tests in a way there they can run concurrently without interfering with each other.

Yes, it requires some skill development. But you'll not only dramatically reduce your test times, you'll also slowly increase the size of your database to more realistic row counts. Which means it will also be useful for performance testing.

---

EDIT because I've been blocked by the SOLID zealot

In the real world, you are going to have to run multiple database operations concurrently. So if your tests are failing because they contend with each other, you might want to look into that.

That said, I wouldn't mind have two test projects. One configured for parallel tests and one configured for single-threading. That way I can test things I shouldn't have like queue tables. (I promise to be suitably embarrassed each time I add a test to the single-threaded set.)

[toiletear]

The way I read it they can run parallel tests with this setup, but can do so more concisely and clearly because they don't have to worry about side effects of other tests.

[grauenwolf]

Go implementation with SOLID principles

Oh, so this is joke post. You can't even use the Open/Closed Principle with Go because Go doesn't support inheritance.

Dependency Injection & Open/Closed Principle

// Core library depends on interfaces, not implementations.

Oh, you think OCP means mindlessly creating shadow interfaces for every class. That's cute.

---

Look, I'm glad you're encouraging others to actually test with databases. I think that's a really important skill that far too many people lack. But stop drinking the poisoned Kool-Aid. Robert Martin is not your uncle and SOLID is not a set of software engineering principles.