How to benchmark PostgreSQL with pgbench

Why pgbench is useful for PostgreSQL performance testing

pgbench is part of PostgreSQL and provides a fast way to test transactional performance. It helps you evaluate read-heavy and write-heavy behavior under controlled concurrency, and compare different instance sizes, storage types, and config changes.

The default pgbench benchmark models small transactional operations against four core tables: accounts, branches, tellers, and history. This makes it useful for stress-testing OLTP-style workloads where transaction latency, WAL performance, and concurrency behavior matter.

How the pgbench workload behaves

• select workload: read-only point lookups, often latency-sensitive at low concurrency.
• simple-update workload: frequent small writes, typically bound by durable storage latency.
• Concurrency ramp: increased clients/jobs can improve throughput until CPU, locks, or I/O saturate.
• Scale factor impact: larger datasets expose cache limits and disk access behavior.

0. Get a PostgreSQL backend (Vela fast path)

If you do not already have a PostgreSQL server, Vela is a quick path to provision one. In Vela, you create a project, then a branch, and the branch is your database endpoint.

Create a sandbox backend at vela.run/sandbox.

1. Create a Project in Vela.
2. Create a Branch inside that project.
3. Copy the branch connection string.

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxxxxxxxxxxxxxxxxxxxxxxx.demo.vela.run:36034/postgres

Export it once and reuse it in all commands:

export PGBENCH_CONN='postgresql://postgres:[YOUR-PASSWORD]@db.xxxxxxxxxxxxxxxxxxxxxxxxxx.demo.vela.run:36034/postgres'

If you benchmark over the public internet, results are end-to-end (client to server) and RTT can dominate TPS and latency.

For server-throughput numbers, run pgbench from a VM in the same region/VPC as the database.

Laptop benchmarks are best for “what my app feels like”; same-region benchmarks are best for “max DB throughput”.

1. Install and verify pgbench

Install the PostgreSQL client tools, then verify pgbench is available.

macOS

brew install postgresql
pgbench --version

Debian/Ubuntu

sudo apt-get update
sudo apt-get install -y postgresql-client
pgbench --version

2. Initialize benchmark data with an appropriate scale factor

Initialize the default pgbench schema and data. Choose scale intentionally: small scales can hide storage and memory behavior, while larger scales surface realistic bottlenecks.

Quick connectivity check (cloud)

Run this first to verify the connection string works before initialization.

psql "$PGBENCH_CONN" -c "select version();"

Localhost

pgbench --initialize -h localhost -U postgres postgres --scale=10

Cloud backend (Vela)

pgbench --initialize --scale=10 "$PGBENCH_CONN"

Scale 1 inserts 100k rows. For meaningful PostgreSQL benchmark comparisons, use a size that reflects your expected working set and I/O profile.

Accounts rows are approximately 100,000 × scale (so scale 100 is about 10,000,000 accounts rows).

Use --scale=100 (~10M accounts rows) for bigger, I/O-heavy runs. Tip: pgbench -i is shorthand for pgbench --initialize.

3. Run a read-only baseline

Start with one client to establish baseline latency and TPS. Then increase parallelism to observe how throughput scales under concurrent reads.

Localhost

pgbench --protocol=prepared --builtin=select -c 1 -j 1 -T 30 -h localhost -U postgres postgres
pgbench --protocol=prepared --builtin=select -c 8 -j 4 -T 60 -h localhost -U postgres postgres

Cloud backend (Vela from laptop)

pgbench --protocol=prepared --builtin=select -c 1 -j 1 -T 30 "$PGBENCH_CONN"
pgbench --protocol=prepared --builtin=select -c 4 -j 2 -T 60 "$PGBENCH_CONN"
pgbench --protocol=prepared --builtin=select -c 8 -j 4 -T 60 "$PGBENCH_CONN"
pgbench --protocol=prepared --builtin=select -c 16 -j 4 -T 60 "$PGBENCH_CONN"

Use --report-latencies on at least one measured run to capture latency distribution:

pgbench --protocol=prepared --builtin=select --report-latencies -c 8 -j 4 -T 60 "$PGBENCH_CONN"

Warm-up before measured read-only runs (optional but recommended)

pgbench -T 20 --protocol=prepared --builtin=select -c 8 -j 4 "$PGBENCH_CONN" >/dev/null

4. Run an update-heavy baseline

Update workloads stress WAL, fsync, and storage latency. Run them separately from read tests to avoid mixing signals.

Localhost

pgbench --protocol=prepared --builtin=simple-update -c 1 -j 1 -T 30 -h localhost -U postgres postgres
pgbench --protocol=prepared --builtin=simple-update -c 8 -j 4 -T 60 -h localhost -U postgres postgres

Cloud backend (Vela)

pgbench --builtin=simple-update --protocol=prepared -c 8 -j 4 -T 60 "$PGBENCH_CONN"
pgbench --builtin=simple-update --protocol=prepared -c 16 -j 4 -T 60 "$PGBENCH_CONN"

Shorthand: -N is equivalent to --builtin=simple-update.

pgbench -N -c 8 -j 4 -T 60 "$PGBENCH_CONN"
pgbench -N -c 16 -j 4 -T 60 "$PGBENCH_CONN"

Warm-up before measured update-heavy runs (optional but recommended)

pgbench -T 20 --protocol=prepared --builtin=simple-update -c 8 -j 4 "$PGBENCH_CONN" >/dev/null

5. Compare and interpret PostgreSQL benchmark results

• TPS: Use for throughput comparison across server/config variants.
• Latency: Track average and percentiles (p95/p99) for user-facing performance.
• Scaling curve: Look for where additional clients stop improving TPS.
• Stability: Repeat runs and compare medians, not only best-case numbers.
• Remote tests: Account for internet RTT when comparing local vs hosted backends.
• Record for repeatability: server version, scale factor, -c/-j/-T, and where the load generator ran (laptop vs same-region VM).

Example results and how to interpret them

Example: remote laptop run (latency-bound)

If you run pgbench over the public internet, results are end-to-end (client to server). A common pattern is that latency (RTT) dominates.

How to interpret this:

• Latency-bound: when average latency is high, each client can only complete about 1 / latency transactions per second.
• Rule of thumb: TPS ≈ clients / latency_seconds. Example: 16 / 0.214 ≈ 74 TPS.
• This does not represent max server throughput; it represents max throughput from your current location and network path.
• For server-throughput numbers, run pgbench from a VM in the same region/VPC as the database.

Common pgbench mistakes to avoid

• Benchmarking with an unrealistically small dataset.
• Comparing results from different PostgreSQL versions without noting changes.
• Changing multiple parameters at once and losing causal clarity.
• Publishing one-off numbers without repeated runs.

Quick command reference

Use these flags frequently when benchmarking with pgbench:

• -T: benchmark duration in seconds
• -c / --client: number of concurrent client connections
• -j / --jobs: worker threads driving load
• --protocol=prepared: prepared statement mode
• --builtin: workload profile such as select or simple-update

Set -j (jobs) less than or equal to CPU cores on the load generator; on laptops, start with -j 2-4.

FAQ

What is a good starting duration?

Use short runs for quick tuning loops, then validate with longer runs to reduce variance.

Should I warm up before benchmarking?

Yes. Warm-up helps stabilize caches and avoids misleading cold-start measurements.

Can I compare across cloud providers?

Yes, but keep workload, PostgreSQL version, dataset size, and config equivalent.

Why do I get low TPS from my laptop?

If your database is remote, network round-trip time can dominate transaction time. Quick diagnostic: if average latency is around 200ms, each client can only do about 5 TPS, so 16 clients gives roughly 80 TPS. For closer-to-server throughput numbers, run pgbench from a VM in the same region/network as the database.