Test PostgreSQL With the Queries Your App Actually Runs

The first number from my local PostgreSQL 16 test was roughly 1,600 statements per second. It looked impressive. It was also the least useful result in the run.

The useful part was the workload. It came from queries the demo app had actually sent: the same prepared statements, parameters, reads and writes. A synthetic benchmark tells you how PostgreSQL handles a synthetic workload. It does not tell you whether your migration just broke the UPDATE your app depends on.

That workload was already sitting in a proxymock recording. The recording can mock the database, drive load against a real one, or replay once against a migrated schema to show exactly which statements broke. I use it for three related jobs, with either an AI coding assistant or the equivalent proxymock command:

1. A quick mock check: record, run the app against a mocked database, and replay the app’s own traffic.
2. A database regression test: replay the recorded queries once against a new schema or PostgreSQL version.
3. A database load test: replay the recorded queries with many concurrent sessions.

flowchart LR
    A[Your app] -->|real queries| R[proxymock recording]
    R --> M[Mock check<br/>no database needed]
    R --> G[Regression test<br/>new schema or version]
    R --> L[Load test<br/>many sessions]

Record once

Start the recorder with a port mapped to your database, and point your app at that port.

Copy this into an AI assistant with the proxymock MCP server installed:

Record my app's traffic with proxymock. My app listens on port 8080 and talks to PostgreSQL on localhost:5432, so map a local port to it and start my app pointed at that port.

Or run the CLI commands:

proxymock record --map 15432=localhost:5432 --app-port 8080
PGUSER=<user> PGPORT=15432 go run main.go   # in a second terminal

Use the app for a minute so it runs its usual queries, then stop the recorder. The proxymock directory now holds the requests your app received and every PostgreSQL statement it sent.

The quick check: run the mock and replay

This is my “get some traffic and make sure the plumbing works” path. Stop your local PostgreSQL, run the app against the mock, and replay the app’s inbound traffic at it.

Copy this into your AI assistant:

Run a proxymock mock server and start my app against it. Then replay the recorded traffic against localhost:8080 and tell me whether every response matched and every database call hit a mock.

Or run the CLI commands:

proxymock mock -- go run main.go
proxymock replay --test-against http://localhost:8080   # in a second terminal

If every request matches, the app works end to end with no database running. That is the same setup covered in Mocking PostgreSQL the Easy Way, just used here as a starting point rather than the destination. You now have a quick regression test for the app and, more importantly for what comes next, a recording of its real database workload.

Flip the recording around

By default, a replay sends inbound traffic to your app as tests. It serves outbound traffic, including every PostgreSQL call, as mocks. To test the database, the PostgreSQL side needs to switch jobs: the recorded queries become the tests, and the database becomes the system under test.

That is what a tests filter does. It chooses which recorded traffic runs as tests. Everything else is mocked. The recording never changes direction; only its role in this replay changes.

flowchart TD
    subgraph D[Default replay]
        D1[Inbound HTTP] -->|test| D2[Your app]
        D2 -->|mocked| D3[PostgreSQL]
    end
    subgraph F[With a tests filter]
        F1[Recorded queries] -->|test| F2[PostgreSQL]
    end

• CLI: --tests-filter '(direction IS OUT) AND (tech IS Postgres)'
• AI assistant: ask for the Postgres traffic to be replayed against the database, or pass the tests-filter parameter of the replay_traffic tool.
• proxymock web: the Tests filter field on the Replay tab.
• Speedscale dashboard: Choose replay tests in a snapshot’s actions menu, then check the database under Outbound dependencies.

If you used the old reverse services snapshot setting, this replaces it. Reverse services flipped every recorded request at once. A tests filter can promote just the database, keep your inbound tests alongside it with (direction IS IN) OR (tech IS Postgres), and leaves the recording as it was captured.

The replay opens its own PostgreSQL sessions and reads credentials where psql does: PGUSER and PGPASSWORD, or ~/.pgpass. It rejects credentials inside the postgres:// address, keeping passwords out of shell history and reports. Before load starts, it connects once to each database. A bad password stops the run immediately instead of failing every statement.

Regression test a PostgreSQL migration or upgrade

Point the recorded queries at a copy of the database with the new schema or the new PostgreSQL version, and replay them once.

Copy this into your AI assistant:

Replay only the recorded Postgres traffic against postgres://localhost:5432/tasks_db once, and list every statement whose result differs from the recording, with the SQL error.

Or run the CLI command:

PGUSER=<user> PGPASSWORD=<password> proxymock replay \
  --tests-filter '(direction IS OUT) AND (tech IS Postgres)' \
  --test-against postgres://localhost:5432/tasks_db \
  --fail-if "requests.result-match-pct < 100"

Any statement that succeeded when recorded but now returns an error appears under RESULT MISMATCH for its query. I tested this by dropping a column the demo app still writes. Every INSERT and UPDATE that touched it became a mismatch. The replay output kept PostgreSQL’s SQLSTATE 42703 error, and the --fail-if gate returned exit code 1. That is a useful CI check before a migration ships, and a sharper signal than watching API tests pass while the database quietly changes underneath them.

Load test PostgreSQL

Same recording, more sessions, fixed duration:

Copy this into your AI assistant:

Load test my Postgres database at postgres://localhost:5432/tasks_db with the recorded Postgres traffic: 10 virtual users for one minute in load-test mode. Summarize latency and failures by query.

Or run the CLI command:

PGUSER=<user> PGPASSWORD=<password> proxymock replay \
  --tests-filter '(direction IS OUT) AND (tech IS Postgres)' \
  --test-against postgres://localhost:5432/tasks_db \
  --vus 10 --for 1m --load-test

Each virtual user opens a database session and replays the statements in order, so prepared statements carry over as they did in the app. The results table breaks latency percentiles, throughput and failures down by query. It shows both failure count and share, so a few failures cannot hide inside a long run.

Back to that number from the opening: the demo recording reached roughly 1,600 statements per second with 10 sessions against a local PostgreSQL 16 container. The replay sessions appeared in pg_stat_activity with application_name = 'speedscale-generator', so I could watch them from the database side. The throughput was useful. Knowing exactly which queries produced it was better. For the wider question of whether a slowdown belongs to the database or the API, the same recording drives both sides.

From the Speedscale dashboard

For traffic recorded in Kubernetes, the same test runs from the dashboard. Open the snapshot, choose Choose replay tests, check the database under Outbound dependencies, and save. Speedscale reanalyzes the snapshot, and the database becomes a service you can replay against. Put its credentials in the test config’s generator.postgres section, with the password stored as a secret reference.

Things to know

• Use a disposable database. Replays run real inserts, updates and deletes.
• Row ids come from the recording. Updates and deletes carry the original row ids, so on a fresh copy some of them match nothing, and repeated inserts can hit unique constraints. These are reported as SQL results, not replay failures.
• Transactions are replayed statement by statement. Recorded traffic mixes many app connections, so a replay can send a COMMIT without its BEGIN. proxymock logs a warning when that happens.
• Not replayed yet: COPY and function calls.

Proving that PostgreSQL can go fast in the abstract is the easy part. The harder and more useful question is whether your schema survives the statements your app really sends, and how those same statements behave under load. One recording answers both.

The PostgreSQL Load and Regression Testing guide covers the full setup, including credentials in test configs and the dashboard flow.