PostgreSQL Load Testing with pgbench

Use pgbench for PostgreSQL load testing at fixed, rising rates with your own queries, so you find the database's breaking point before your users do.

Published

Applies to pgbench and PostgreSQL 15 and later; --max-tries and the failed count need pgbench 15

pgbench ships with PostgreSQL and can push a database at a fixed transaction rate. Fed with your own queries, it finds the rate where your Postgres stops meeting its latency budget, and tells you why. The built-in TPC-B test says little about your product. Script your real traffic first, then raise the rate step by step until something gives out.

Where it breaks

You’re looking for the highest transaction rate that still meets your latency target with no errors. Past it, one of these runs out first:

  • CPU. Every active session is on a core, and new queries wait their turn. Latency climbs in a smooth curve.
  • Disk I/O. The working set no longer fits in memory, or the commit path waits on WAL flushes. Latency jumps at each checkpoint.
  • Locks. Many transactions update the same rows, such as a counter or a hot account. Throughput flattens while CPU sits idle.
  • Connections. The app opens more sessions than Postgres can serve. Logins fail with FATAL: sorry, too many clients already, which the PgBouncer recipe fixes.

At a fixed arrival rate, the first sign shows in pgbench’s lag field. pgbench schedules each transaction for a set time. Once the database can’t keep up, transactions start late, and lag grows through the step. Next, p99 latency passes your budget, and at last transactions fail.

Set up the test

The tool. Run pgbench from a staging host in the same zone as the database, never on the database host itself. pgbench uses CPU too, and on the same machine it steals from Postgres. Give it a host with enough cores for -j threads, and check its CPU during the run.

The data. Use a staging database restored from a production snapshot, with personal data masked. Table sizes, index sizes and value distributions decide whether a query hits memory or disk. Run ANALYZE after the restore so the planner has fresh statistics.

The script. Find the queries that make up your load. pg_stat_statements lists them with their call counts:

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 15;

Turn the top entries into pgbench scripts. One file per transaction type keeps the weights easy to change. This read reflects a skewed access pattern, where a few users get most of the traffic:

-- read.sql
\set uid random_zipfian(1, :users, 1.1)
SELECT id, amount_cents, created_at
FROM orders
WHERE user_id = :uid
ORDER BY created_at DESC
LIMIT 20;

The write holds a transaction, like your app’s checkout:

-- write.sql
\set uid random(1, :users)
\set amount random(100, 50000)
BEGIN;
INSERT INTO orders (user_id, amount_cents) VALUES (:uid, :amount);
UPDATE users SET order_count = order_count + 1 WHERE id = :uid;
COMMIT;

random_zipfian picks low IDs far more often than high ones, the way real traffic picks popular items. With random, each row has the same odds, so locks and cache misses spread out and the test looks better than production. -D users=... passes the row count of your users table.

Weight the scripts by your production mix. If pg_stat_statements shows eight reads per write, run -f read.sql@8 -f write.sql@2.

The flags that make it realistic:

  • -R holds a fixed rate in transactions per second. Leave it out and each client waits for its previous transaction, so a struggling database receives less load and hides its own slowdown.
  • -c sets the client sessions. Little’s law gives the floor: rate × latency in seconds. Size it to the connection count your app’s pools open in production. I set -c to the production pool total, not the pod count, because 400 direct sessions test a different database than 40 pooled ones.
  • -j sets pgbench threads. Use the load host’s core count, up to -c.
  • -M prepared matches apps that use prepared statements, which most ORMs and drivers do. Leave it at simple if yours doesn’t.
  • -L counts transactions slower than your budget in milliseconds. With -R, pgbench also skips transactions that would start more than that late, and reports them as skipped.
  • --max-tries=3 retries serialization and deadlock failures, as a careful app would. --failures-detailed splits failures by type in the report.

Managed Postgres. Run pgbench from a VM in the same region and zone as the primary. That holds for Amazon RDS, Aurora, Azure Database for PostgreSQL, Cloud SQL and OCI Database with PostgreSQL. For the built-in tables, pgbench -i -I dtGvp builds the data on the server side, which saves sending every row over the network. Burstable instance classes and some disk types run on credits. A short test can pass on burst capacity, so hold each step long enough to drain the credits, or watch the provider’s credit metric. Aurora Standard bills each storage I/O, so a long write test costs money there.

Run it in steps

Start at half your current peak and climb by a quarter of it each step. Hold each step for ten minutes, so checkpoints, autovacuum and cache churn all show up inside it. Rest between steps so one step’s backlog doesn’t spill into the next:

#!/usr/bin/env bash
set -u
PEAK=2000            # your current peak transactions per second
BUDGET_MS=200        # your p99 latency budget
CLIENTS=64           # your production pool total
DB="-h pg-staging.internal -U app app"

for pct in 50 75 100 125 150 175 200 250 300; do
  rate=$((PEAK * pct / 100))
  psql $DB -qc "SELECT pg_stat_statements_reset();"
  pgbench $DB -n -M prepared -D users=1000000 \
    -f read.sql@8 -f write.sql@2 \
    -c "$CLIENTS" -j 8 -R "$rate" -T 600 -P 30 \
    -L "$BUDGET_MS" --max-tries=3 --failures-detailed \
    -l --log-prefix="step_${rate}" --sampling-rate=0.1 \
    > "step_${rate}.txt" 2>&1
  sleep 120
done

-n skips the vacuum pgbench runs on its own tables, which don’t exist in your schema. The -l logs give per-transaction latency for p99. The PgBouncer recipe has an awk line that reads p99 from them.

While each step runs, watch three things.

pgbench’s progress lines. Every 30 seconds, each line prints tps, average latency, failures and lag. Healthy steps keep tps at your -R value with lag flat. A rising lag means the queue is growing.

Session wait events. Sample this every few seconds on the database:

SELECT coalesce(wait_event_type, 'CPU') AS waiting_on,
       coalesce(wait_event, 'running') AS event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active' AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;

A session with no wait event is running on a CPU. Run it with \watch 5 in psql.

The host. CPU, disk latency and IOPS on the database server, from node_exporter or your provider’s console. Add pgbench host CPU too. If the load host passes about 80% CPU, its numbers stop meaning anything.

Stop the climb at the first step that fails your targets. Write down that step’s rate and the one before it. The one before is your tested capacity.

Read the results

Each step’s step_<rate>.txt ends with a summary: the transactions processed, failed and skipped, the count above your latency limit, and the average latency and lag. Compare the last passing step with the first failing one, then match the wait events to the resource:

Most common wait in the failing stepWhat gave outWhere to look next
CPU (no wait event)CPUpg_stat_statements ordered by total_exec_time. A missing index or a bad plan tends to cost more than the hardware.
IO with DataFileReadMemory, so reads go to diskCache hit ratio from pg_stat_database, then shared_buffers and the instance’s RAM.
IO with WALSync or WALWriteThe commit pathDisk write latency on the WAL volume, and checkpoint frequency against max_wal_size.
Lock with transactionid or tupleHot rowsThe UPDATE in your write script. Spread the counter, or batch the updates.
LWLockInternal contentionThe event name points the way: WALInsert is commit volume, LockManager is many partitions or indexes per query.
Postgres idle, lag risingThe load host or the networkFix the test before you trust it. Add threads or a second load host.

Then check what the step left behind. Run the dead-row query from the autovacuum recipe after the write-heavy steps. If dead rows climbed through the test, a long run at that rate will bloat your hot tables. Your capacity is then lower than the step result.

I trust a breaking point only after a second run lands on the same step. One clean run can come from a warm cache or a lucky checkpoint.

pgbench tests the database alone. To find where the whole request path breaks, from load balancer to database, use the k6 breaking point recipe and compare the two.

Make it a habit

Rerun the steps before a launch or a sale, after a major PostgreSQL upgrade, and after schema changes to your hottest tables. Keep the scripts in the same repository as the migrations, so a schema change and its script change land together.

A nightly or weekly job on staging catches regressions early. Run only the step at your current peak, and fail the job when the summary shows failures, skips or too many slow transactions:

pgbench $DB -n -M prepared -D users=1000000 \
  -f read.sql@8 -f write.sql@2 -c 64 -j 8 -R 2000 -T 300 -L 200 \
  > nightly.txt 2>&1 || exit 1
grep -E 'failed|skipped|above the' nightly.txt

Parse those lines in your CI tool and set the limits from your own budget.

Then alert before production reaches the tested limit. Scrape Postgres with postgres_exporter, as the postgres_exporter recipe shows, and compare the live transaction rate with your tested capacity:

groups:
  - name: postgres-capacity
    rules:
      - alert: PostgresNearTestedCapacity
        expr: |
          sum by (instance) (
            rate(pg_stat_database_xact_commit{datname="app"}[5m])
            + rate(pg_stat_database_xact_rollback{datname="app"}[5m])
          ) > 1400
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "{{ $labels.instance }} is above 70% of its tested transaction capacity"
          description: "Replace 1400 with 70% of the last passing pgbench step. Plan the fix the test pointed to (index, instance size or pooling), and rerun the steps after it."

pgbench counts a script as one transaction, while Postgres counts each statement outside BEGIN and COMMIT as its own. Compare the two on staging once and adjust the threshold, so the alert matches what your scripts measured.

More recipes

Further reading