Benchmarking Honestly with pgbench

pgbench ships with Postgres, runs out of the box, and will hand you a transactions-per-second number in ninety seconds that tells you nothing about your application. The tool is behaving correctly. A general-purpose load generator answers the question it was given, and by default it was given somebody else's.

A benchmark earns its time only when it answers something narrow: does moving shared_buffers from 128 MB to 16 GB change checkout latency at Cartwheel's Saturday rate of 3,000 orders a minute? Does random_page_cost = 1.1 alter anything, given the plans this workload actually uses? Each of those questions carries four things with it: a workload, a data size, a warm-up and a measurement.

What pgbench Does by Default

Initialization builds four tables of its own. pgbench_accounts gets 100,000 rows per unit of scale factor, pgbench_tellers gets 10, pgbench_branches gets 1, and pgbench_history starts empty. The default script, named tpcb-like, then runs seven commands per transaction: a BEGIN, an UPDATE of one account row, a SELECT of that account's balance, an UPDATE of a teller row, an UPDATE of a branch row, an INSERT into history, and an END.

$ pgbench -i -s 500 cartwheel_bench
# 50,000,000 rows in pgbench_accounts, 5,000 tellers, 500 branches

$ pgbench -b list
Available builtin scripts:
  tpcb-like: <builtin: TPC-B (sort of)>
  simple-update: <builtin: simple update>
  select-only: <builtin: select only>

Read that transaction shape and the bias is obvious: four of the seven commands are writes, three updates and an insert, and one of those updates a single row chosen from only 500 branches. Run it with 100 clients at scale 10 and you are measuring row-level contention on ten branch rows rather than the database, which is the reason the documentation asks for a scale factor at least as large as the largest client count you intend to test. As a model, the default is a write-heavy banking simulation. It resembles Cartwheel's checkout in outline and the analytics dashboard not at all, and it resembles a read-mostly API even less. -S gives the select-only variant and -N drops the two contended updates, which narrows the bias without removing it.

Custom Scripts Are the Point

The version of pgbench worth running executes your own statements. -f takes a script file, \set computes variables before each transaction, and an @weight suffix mixes several scripts in a chosen ratio. The three statements at the top of the pg_stat_statements ranking are the obvious candidates, and the parameter distribution matters as much as the statements do: random() is uniform, while real customers are not, and random_zipfian(), random_exponential() and random_gaussian() exist so the hot rows are hot in the benchmark too.

-- a few products are ordered far more than the rest: zipfian, not uniform
\set pid random_zipfian(1, 240000, 1.2)
\set wid random(1, 4)
\set cid random(1, 3800000)

BEGIN;
SELECT on_hand FROM inventory WHERE product_id = :pid AND warehouse_id = :wid;
INSERT INTO orders (customer_id, placed_at, status, total)
  VALUES (:cid, now(), 'pending', 41.20);
COMMIT;

# seven checkouts for every three history reads
$ pgbench -f checkout.sql@7 -f order_history.sql@3 -c 32 -j 8 -T 1200 -P 30 cartwheel

That is two scripts weighted seven to three, running for twenty minutes with a progress line every thirty seconds. The zipfian distribution on product id is the detail that separates a useful run from a decorative one: with uniform random ids across 240,000 products, every lookup misses the cache and the benchmark reports the behaviour of a workload nobody has. With skew, the same few hundred products stay resident exactly as they do in production, and the cache hit ratio in the run resembles the one on the dashboard. Writing the two scripts took an afternoon.

Sizing and Warm-Up

If the question involves disk, the dataset has to be larger than shared_buffers, or the run measures memory speed and reports it as database throughput. Do not estimate that from the scale factor — initialize, then ask the database how big the result is with pg_relation_size(), and compare it against the buffer pool directly. For the opposite kind of question, where you want everything cached, the same check confirms you got what you wanted rather than assuming it.

Then warm up. The first pass over a freshly loaded table sets hint bits and populates both caches, so the opening minutes of a run measure a state the server will never be in again; reporting that first cold pass as "the number" is the classic error. The manual's guidance on duration is specific: never believe a test that runs for a few seconds, make it last at least a few minutes, and expect to need hours for reproducible figures. Cartwheel's runs are twenty minutes. That spans several checkpoints at the five-minute default and still contains one at the fifteen minutes Topic 55 settles on, so the periodic write cost a three-minute run would have skipped entirely is inside the measurement either way.

Concurrency and Measurement

The measurement rests on four flags. -c sets client sessions and -j sets the threads that drive them; one thread cannot keep 32 clients busy, and the manual warns that pgbench can become the bottleneck itself, which is the argument for running it from app-01 rather than from pg-primary. -T runs for a duration instead of a transaction count, which is the form that makes two runs comparable. -M prepared removes parse and plan overhead from every iteration, matching what a driver with prepared statements actually does. And --rate generates a fixed arrival rate on a Poisson schedule, which is how you ask "what is latency at 3,000 orders a minute" instead of "what is latency when the server is saturated".

$ pgbench -f checkout.sql -c 32 -j 8 -T 1200 -M prepared \
          --rate=50 --latency-limit=200 -l --aggregate-interval=10 cartwheel

transaction type: multiple scripts
scaling factor: 500
query mode: prepared
number of clients: 32
number of threads: 8
duration: 1200 s
number of transactions actually processed: 59981
number of failed transactions: 0 (0.000%)
latency average = 9.183 ms
latency stddev = 4.412 ms
initial connection time = 41.881 ms
tps = 49.984167 (without initial connection time)

Read the last four lines carefully, because one thing is missing from them. pgbench reports throughput, an average latency and a standard deviation, and no percentiles at all. The tail that every user actually notices is not in that summary, and quoting "latency average = 9.183 ms" as the result of a benchmark is how a p99 of 400 milliseconds goes unmentioned. Two flags fix it: --latency-limit counts and reports transactions that exceeded a deadline, and -l writes one line per transaction with its elapsed time in microseconds, from which any percentile you want is a sort away. --aggregate-interval gives per-interval minimum and maximum instead when the full log would be too large.

Changing One Thing at a Time

One setting, one run, the same data, the same warm-up, and a line written down. It sounds pedantic until the third run, when the numbers move and the log has no way to say which of the three changes did it. Re-initializing between runs is sometimes necessary and is itself a variable: pgbench vacuums pgbench_tellers and pgbench_branches and truncates pgbench_history before each run unless you pass -n, and -v vacuums all four — so two runs that differ only in whether the tables had accumulated dead rows are not comparable, and the documentation flags exactly that sensitivity.

Cartwheel's sequence produced one expected result and one that saved a week. Raising shared_buffers from 128 MB to 16 GB took the checkout script from 1,240 to 3,510 transactions a second on the same hardware. Changing random_page_cost from 4.0 to 1.1 afterwards changed nothing measurable, not because the setting is wrong, but because every statement in this particular script was already using an index scan, so no plan had a decision to make. Made together, the two changes would have produced one number and one attribution, and the attribution would have been wrong.

What It Cannot Tell You

Twenty minutes of synthetic load is silent on most of what breaks a database. It says nothing about plan stability as the data grows past a histogram boundary, nothing about how vacuum copes after a week of updates, nothing about failover, nothing about the first Saturday of the month, and nothing whatsoever about the queries you did not put in the script. It also cannot tell you about a workload that changes shape at 3 a.m. because a batch job starts.

So treat a benchmark result as a hypothesis with a number attached, and confirm it where the truth lives: reset pg_stat_statements, apply the change to one production host, and compare total execution time for the statements you predicted would move. The benchmark's job is to make the change safe to try and to rule out the ones that were never going to help. Production is the only place the answer is final, and the next topic is the configuration file all of this measurement is arguing about.