pgbench: how to benchmark and load test PostgreSQL

pgbench is the benchmark program that ships with PostgreSQL: it runs the same SQL transaction over and over in several concurrent database sessions and reports transactions per second and latency. You create its test tables with pgbench -i -s 50 mydb, then run something like pgbench -c 10 -j 2 -T 300 mydb for ten clients on two threads for five minutes. It measures the database alone, so it says nothing about what users feel through the application in front of it.

We have not run pgbench for this guide. The facts come from its page in the PostgreSQL 18 documentation, the version postgresql.org marks as current, checked on 31 October 2026, and the judgements are ours.

What does pgbench measure?

It runs a transaction script again and again in a set number of database sessions and reports the average transaction rate and latency. The default script is loosely based on the TPC-B banking benchmark.

The pgbench documentation says the default scenario is inspired by TPC-B and is “not actually TPC-B, hence the name”: the output labels it TPC-B (sort of) (checked on 31 October 2026).

How do you set up a pgbench database?

Run pgbench -i against an existing database, with -s for the scale factor. It creates four tables and drops any existing tables with the same names, so give it a database of its own.

At scale 1 the tables hold 1 branch, 10 tellers, 100,000 accounts and an empty history table, and -s multiplies those rows: -s 100 gives 10,000,000 rows in pgbench_accounts. The documentation gives no size on disk, so measure it after initializing (checked on 31 October 2026).

Which built-in scripts does pgbench include?

Three: tpcb-like (the default), simple-update and select-only. You pick one with -b, or with the shortcuts -N and -S.

The default runs seven commands per transaction: BEGIN, an update and a select on a random account, teller and branch updates, a history insert, and END. simple-update drops the teller and branch updates, which avoids contention on those small tables and, in the documentation’s words, makes the test “even less like TPC-B”. select-only issues only the select.

How do you write a custom pgbench script?

Put your SQL in a file, set variables with \set and the random functions, and pass the file with -f. Each run of the file counts as one transaction.

\set aid random(1, 100000 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta
  WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
END;

A weight after @ sets how often each script is picked: -f update.sql@9 -b select-only@1 picks the file about nine times in ten. The documentation also lists random_exponential, random_gaussian and random_zipfian, which can make a few rows hot. \sleep adds a pause, the database version of think time. When you run only your own files, pass the database’s scale with -s, or :scale stays 1 and the script above touches only the first 100,000 accounts. With your own tables, leave out -i and add -n so pgbench does not vacuum the standard ones (checked on 31 October 2026).

Which options make pgbench behave like a load test?

Concurrency comes from -c and -j, duration from -T, and the shape of the load from -R. Without -R, each client starts its next transaction as soon as the last one ends.

That default is a closed model, explained in our article on the open vs closed workload model. With -R, pgbench starts transactions on a Poisson-distributed schedule that moves forward from the client’s start time, whatever happened to earlier transactions. Reported latency then counts from each scheduled start, so it includes the schedule lag, and the average and maximum lag are shown separately: the correction our coordinated omission article describes. The client count stays fixed, so a lag that keeps growing means the system cannot process that rate with the chosen clients and threads: lower -R or add clients.

--latency-limit (-L) counts transactions slower than a limit in milliseconds as late; with -R it also skips transactions already too far behind schedule and reports them as skipped. -M prepared uses prepared statements, which the documentation says runs faster than the other modes (checked on 31 October 2026).

How do you read pgbench output?

The summary lists the settings, transactions processed and failed, average latency, initial connection time and tps. The shape below comes from the documentation, numbers left out:

transaction type: <builtin: TPC-B (sort of)>
number of transactions actually processed: ...
number of failed transactions: ... (...%)
latency average = ... ms
latency stddev = ... ms   (only with -P, -R or -L)
initial connection time = ... ms
tps = ... (without initial connection time)

Failed transactions are serialization or deadlock errors, which --max-tries can retry. Other SQL errors abort that client and make the exit status 2. The summary has no percentiles, so for tail latency log each transaction with -l, which records its time in microseconds, and compute p95 or p99 yourself.

Unless you pass -P, -R or -L, the summary’s average latency is not measured: pgbench derives it from run time and client count, Little’s law run backwards, failures included. Add -P when latency matters.

How do you get pgbench numbers you can trust?

Run for minutes, repeat, keep the scale factor at least as large as the client count, and watch vacuum. If pgbench becomes the bottleneck, move it to another machine.

The first guideline in the documentation’s “Good Practices” section is “never believe any test that runs for only a few seconds” and adds that reproducible numbers can take hours (checked on 31 October 2026). With the default script, a scale below the client count mostly measures update contention, because pgbench_branches has only -s rows and every transaction updates one. Dead rows and autovacuum shift results between runs. With many sessions pgbench can saturate its own host: run it on a separate machine with low network latency, or spread several instances over several machines.

When is pgbench the wrong tool?

When the question is about users, another database or a standard OLTP mix. pgbench measures PostgreSQL alone, without your connection pool, ORM, cache or HTTP layer.

To see what users feel, load the HTTP or API layer in front of it. LoadFocus, which publishes this site, does not run pgbench and cannot connect to PostgreSQL directly: it runs JMeter test plans and k6 scripts from 25 cloud regions against web and API endpoints, and its template for the endpoints behind heavy queries is a starting point.

Choose sysbench instead for MySQL: its repository describes a scriptable multi-threaded benchmark tool with OLTP-like database tests. Choose HammerDB for a TPC-C style mix: its documentation describes TPROC-C, a workload derived from TPC-C, which it runs on PostgreSQL and other engines. Choose JMeter with its JDBC Request sampler to mix SQL and HTTP in one test plan on your own machines (all checked on 31 October 2026).

FAQ

What does the -s option do in pgbench? It sets the scale factor at initialization. Each unit adds 100,000 rows to pgbench_accounts, so -s 100 creates 10,000,000 rows. With the default script, keep it at least as large as your client count.

What is the difference between -c and -j in pgbench? The -c option sets the number of simulated clients, each a concurrent database session. The -j option sets how many worker threads inside pgbench share those clients.

Can LoadFocus run pgbench? No. LoadFocus runs JMeter test plans and k6 scripts and has no direct PostgreSQL connection, so with it you load the web or API layer that sits in front of the database.

Related reading

• Coordinated omission: what it is and how to avoid it
• Open vs closed workload model: which one should you use?
• Tail latency: what it is and how to measure it
• Little’s law in load testing: size and check your tests
• How to use sysbench for CPU, disk and MySQL benchmarks
• YCSB: workloads A-F, how to run it and its limits
• redis-benchmark: how to use it and read its numbers
• TPC-H: what the benchmark measures and how to run it

The technical claims in this article have not been independently reviewed.