feat(postgres): use faster sql.DB instead of docker exec psql for snapshot/restore [rebased for main] by cfstras · Pull Request #2600 · testcontainers/testcontainers-go

cfstras · 2024-06-21T09:26:28Z

See description of #2599

This is the same PR, but rebased for main.

The original description follows, just with latest benchmarks

What does this PR do?

The postgres module has functions for saving snapshots and restoring them later.
This feature is helpful for integration testing without having to manually clear the database.

This PR speeds up the snapshot/restore functionality by executing the necessary SQL commands via a regular sql.DB connection.

If the driver is not available in the test environment, or the commands fail for some other reason (SSL config, etc), it falls back to the old variant of calling docker exec.

Why is it important?

docker exec can be unnecessarily slow, especially when using docker in a VM with Docker Desktop or colima.

On my laptop (MacBook Air M1), each call to Container.Restore() takes about 150~200ms.
In a small subset of 20 of our tests, where each test restores the database before starting, with creating the initial container, this brings the total time up to 12 seconds, which is quite a lot for a few small database tests.

After these changes, the same subset of tests now takes 6 seconds.

Related issues

How to test this PR

cd modules/postgres
go test ./...

Follow-ups

I've also considered caching the DB pool that is created to manage the databases.
After running some benchmark with this code (from branch X), I noticed it does not have a big impact on the whole test suite - my internal test suite does not change in execution time (since the time is dominated by re-connecting the actual test client, which will be terminated when the database gets deleted during the restore).
The module/postgres test suite even gets slower:

cached: main...cfstras:postgres-snapshot-sql-cached-rebased-main
not cached: this PR

testcontainers main, sql, not cached: 42.227 s ± 0.635

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     42.227 s ±  0.635 s    [User: 1.213 s, System: 1.034 s]
  Range (min … max):   41.832 s … 44.006 s    10 runs

testcontainers main, sql, cached: 43.207 s ± 1.549

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     43.207 s ±  1.549 s    [User: 1.247 s, System: 1.057 s]
  Range (min … max):   41.745 s … 45.664 s    10 runs

testcontainers main, without PR (but amended tests): 45.298 s ± 5.610 s

Branch: cfstras:testcontainers-go:main-with-postgres-sql-test-fixes

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     45.298 s ±  5.610 s    [User: 1.218 s, System: 1.005 s]
  Range (min … max):   41.243 s … 57.096 s    10 runs

internal testsuite, with testcontainers main, sql, cached, with ryuk: 14.123 s ± 1.795 s

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     14.123 s ±  1.795 s    [User: 9.589 s, System: 2.749 s]
  Range (min … max):   11.407 s … 16.748 s    10 runs

internal testsuite, with testcontainers main, sql, cached, without ryuk: 11.299 s ± 1.503 s

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     11.299 s ±  1.503 s    [User: 9.818 s, System: 2.728 s]
  Range (min … max):    9.928 s … 13.781 s    10 runs

internal testsuite, with testcontainers main, sql, not cached, without ryuk: 10.597 s ± 0.641 s

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     10.597 s ±  0.641 s    [User: 9.812 s, System: 2.820 s]
  Range (min … max):    9.894 s … 12.029 s    10 runs

internal testsuite, with testcontainers main, without my PR, not cached, without ryuk: 14.245 s ± 0.639 s

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     14.245 s ±  0.639 s    [User: 9.671 s, System: 2.760 s]
  Range (min … max):   13.742 s … 15.980 s    10 runs

internal testsuite, with testcontainers main, docker-exec fallback, not cached, without ryuk: 14.216 s ± 0.450 s

➜ hyperfine "go test ./... -count 1"
Benchmark 1: go test ./... -count 1
  Time (mean ± σ):     14.216 s ±  0.450 s    [User: 9.566 s, System: 2.654 s]
  Range (min … max):   13.735 s … 15.278 s    10 runs

…reating & restoring snapshots

Remove all commented lines

…hat don't register as "postgres"

netlify · 2024-06-21T09:26:46Z

✅ Deploy Preview for testcontainers-go ready!

Name	Link
🔨 Latest commit	`9330f0b`
🔍 Latest deploy log	https://app.netlify.com/sites/testcontainers-go/deploys/66840a28177e55000813692d
😎 Deploy Preview	https://deploy-preview-2600--testcontainers-go.netlify.app
📱 Preview on mobile	Toggle QR Code... Use your smartphone camera to open QR code link.

To edit notification comments on pull requests, go to your Netlify site configuration.

docs/modules/postgres.md

modules/postgres/postgres_test.go

modules/postgres/postgres.go

mdelapenya · 2024-06-24T11:49:38Z

docs/modules/postgres.md

+No default is supplied, so you need to set it explicitly.
+
+<!--codeinclude-->
+[Example Wait Strategies](../../modules/postgres/postgres_test.go) inside_block:waitStrategy


Let's update the docs path:

Suggested change

[Example Wait Strategies](../../modules/postgres/postgres_test.go) inside_block:waitStrategy

[Example Wait Strategies](../../modules/postgres/wait_strategies.go) inside_block:waitStrategy

Once this is done and the CI passes, I think we can merge this one 🚀

Cool! Fixed those two nits, LMK how it goes.

FYI going on holiday in a few hours, so if there's more things you want me to change it'll have to wait until next week.

Thanks so much for engaging with this :)

modules/postgres/postgres_test.go

…rs-go into postgres-snapshot-sql-rebased-main

modules/postgres/postgres.go

mdelapenya · 2024-06-28T10:33:36Z

@cfstras @ash2k I went ahead and added d8468ec converting the global variable into a container option. PLMK what you think.

Cheers!

cfstras · 2024-07-02T12:45:45Z

Looks good to me! :) Thanks for making the change.

modules/postgres/postgres.go

mdelapenya

LGTM, thanks!

* main: chore(deps): bump mkdocs-include-markdown-plugin from 6.0.4 to 6.2.1 (#2617) chore(deps): bump peter-evans/slash-command-dispatch from 3.0.2 to 4.0.0 (#2561) docs: document ryuk timeouts for compose (#2620) chore: use self-hosted worker for Windows tests (#2619) feat(postgres): use faster sql.DB instead of docker exec psql for snapshot/restore [rebased for main] (#2600)

cfstras added 6 commits June 21, 2024 09:55

feat(postgres): use faster sql.DB instead of docker exec psql for c…

51f83ff

…reating & restoring snapshots

test(postgres): Make tests work reliably on slower computers

6b54ac0

chore(postgres): simplify testdata

c827da4

Remove all commented lines

feat(postgres): add postgres.SQLDriverName for using go SQL drivers t…

097be95

…hat don't register as "postgres"

test(postgres): Add test for snapshot fallback to docker-exec

3529658

docs(postgres): Update docs for wait strategies and custom SQL drivers

9f8a9d0

cfstras requested a review from a team as a code owner June 21, 2024 09:26

cfstras mentioned this pull request Jun 21, 2024

feat(postgres): use faster sql.DB instead of docker exec psql for snapshot/restore #2599

Closed

mdelapenya reviewed Jun 21, 2024

View reviewed changes

docs/modules/postgres.md Show resolved Hide resolved

mdelapenya reviewed Jun 24, 2024

View reviewed changes

modules/postgres/postgres_test.go Outdated Show resolved Hide resolved

mdelapenya reviewed Jun 24, 2024

View reviewed changes

modules/postgres/postgres.go Outdated Show resolved Hide resolved

cfstras added 3 commits June 24, 2024 13:26

feat(postgres): expose postgres.BasicWaitStrategies

4e11583

docs(postgres): include docs marker of release

8f6d922

chore(postgres): safeguard nil check

fbbf348

mdelapenya reviewed Jun 24, 2024

View reviewed changes

modules/postgres/postgres_test.go Outdated Show resolved Hide resolved

cfstras added 2 commits June 25, 2024 14:45

style: small comments from review

4b62dc7

Merge branch 'main' of https://github.com/testcontainers/testcontaine…

9b2eac4

…rs-go into postgres-snapshot-sql-rebased-main

ash2k reviewed Jun 26, 2024

View reviewed changes

modules/postgres/postgres.go Outdated Show resolved Hide resolved

chore: convert SQLDriverName global into a container options

d8468ec

Merge branch 'main' into postgres-snapshot-sql-rebased-main

2f825cc

chore: update container struct for snapshotting

9330f0b

mdelapenya reviewed Jul 2, 2024

View reviewed changes

modules/postgres/postgres.go Show resolved Hide resolved

mdelapenya self-assigned this Jul 2, 2024

mdelapenya added the feature New functionality or new behaviors on the existing one label Jul 2, 2024

mdelapenya approved these changes Jul 2, 2024

View reviewed changes

mdelapenya merged commit 0d7ebaf into testcontainers:main Jul 2, 2024

kiview mentioned this pull request Sep 17, 2024

fix(postgres): Apply default snapshot name if no name specified #2783

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

feat(postgres): use faster sql.DB instead of docker exec psql for snapshot/restore [rebased for main]#2600

feat(postgres): use faster sql.DB instead of docker exec psql for snapshot/restore [rebased for main]#2600
mdelapenya merged 14 commits intotestcontainers:mainfrom
cfstras:postgres-snapshot-sql-rebased-main

cfstras commented Jun 21, 2024

Uh oh!

netlify bot commented Jun 21, 2024 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mdelapenya Jun 24, 2024 •

edited

Loading

Uh oh!

cfstras Jun 25, 2024

Uh oh!

Uh oh!

Uh oh!

mdelapenya commented Jun 28, 2024

Uh oh!

cfstras commented Jul 2, 2024

Uh oh!

Uh oh!

mdelapenya left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

	[Example Wait Strategies](../../modules/postgres/postgres_test.go) inside_block:waitStrategy
	[Example Wait Strategies](../../modules/postgres/wait_strategies.go) inside_block:waitStrategy

Uh oh!

Conversation

cfstras commented Jun 21, 2024

What does this PR do?

Why is it important?

Related issues

How to test this PR

Follow-ups

Uh oh!

netlify bot commented Jun 21, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

✅ Deploy Preview for testcontainers-go ready!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mdelapenya Jun 24, 2024 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

cfstras Jun 25, 2024

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

mdelapenya commented Jun 28, 2024

Uh oh!

cfstras commented Jul 2, 2024

Uh oh!

Uh oh!

mdelapenya left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

netlify bot commented Jun 21, 2024 •

edited

Loading

mdelapenya Jun 24, 2024 •

edited

Loading