Run a Repeatable PostgreSQL Benchmark with pgbench

You will initialise PostgreSQL's standard benchmark tables in a database you have chosen, run a controlled test, and record the transaction rate and latency that pgbench reports. This guide uses the installed PostgreSQL 16.15 build, packaged on this machine as Ubuntu 16.15-0ubuntu0.24.04.1.

Allow 10 to 20 minutes for a small smoke test, plus longer if you want useful numbers. You need a running PostgreSQL server, a database you can alter, the pgbench client, and permission to connect to that database. No command below needs root privileges. Database ownership or suitable PostgreSQL privileges are enough.

1. Confirm the client and choose a test database

Check the binary before preparing anything. The version matters because option details and benchmark behaviour can differ between PostgreSQL releases.

$ pgbench --version
pgbench (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

Set connection details as shell variables so the commands are easy to review. Replace every placeholder with a real value. The database must already exist.

$ export PGHOST='127.0.0.1'
$ export PGPORT='5432'
$ export PGUSER='benchmark_user'
$ export PGDATABASE='pgbench_lab'
$ pgbench --help | sed -n '1,12p'

Using PGDATABASE is optional, but it prevents an easy mistake: if no database name is supplied, libpq falls back through its normal connection defaults. Keep the test database separate from application data so that benchmark setup cannot remove application tables.

2. Initialise the standard tables

Initialisation creates the four tables used by the built-in TPC-B-like scenario: pgbench_accounts, pgbench_branches, pgbench_history and pgbench_tellers. At scale 1, the account table receives 100,000 rows. Choose a scale that gives the workload enough rows for the client count you plan to use.

$ pgbench -i -s 10 "$PGDATABASE"
starting vacuum...end.
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping

The notices and progress lines vary with the server and client output, so do not compare them byte for byte. A successful initialisation returns status 0. The command's default initialisation steps include dropping old tables, creating tables, generating data, vacuuming and creating primary keys. The client-side data generator is the default; use -q if progress output is distracting.

If you need to remove the benchmark objects later, do it deliberately in the test database with SQL after checking the names:

$ psql -d "$PGDATABASE" -c 'dt pgbench_*'

Do not run a guessed DROP TABLE command against a production database. Reinitialising the same database with pgbench -i is the normal reset for these standard tables, but it also destroys their current contents.

3. Run a measured baseline

Use either a fixed transaction count with -t or a time limit with -T, never both. This example uses 10 clients and 10,000 transactions per client. It is long enough to expose some noise, but a real comparison should run for minutes and be repeated.

$ pgbench -c 10 -t 10000 -j 2 "$PGDATABASE"
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 2
number of transactions actually processed: 100000/100000
number of failed transactions: 0 (0.000%)
latency average = 12.000 ms
tps = 800.000000 (without initial connection time)

The numbers above are illustrative. Your machine will produce different latency and TPS values. Read the processed count and failed count before looking at TPS. The final TPS excludes initial connection time, while the reported average latency describes successful transactions.

For a duration-based run, replace -t with -T 120. The report then describes transactions completed during 120 seconds. Add -P 10 for progress every 10 seconds when you need to see whether the run is still moving.

4. Control the workload you are measuring

The default test is not a generic read-only benchmark. It updates account, teller and branch rows, inserts history, and normally vacuums some standard tables before the run. Its results therefore depend on contention, table age and autovacuum as well as raw query speed.

To measure a read-only built-in workload, use -S:

$ pgbench -S -c 10 -T 120 -j 2 "$PGDATABASE"

To control the offered rate rather than run as fast as possible, add -R. A high schedule lag means the chosen clients and threads cannot sustain the requested rate.

$ pgbench -S -c 10 -T 120 -R 500 -P 10 "$PGDATABASE"

Do not use a client count larger than the scale factor for the default scenario without understanding the result. Every transaction updates one branch row, and the branch table has only -s rows, so excessive concurrency can mostly measure lock contention. Keep the server workload, connection settings, vacuum state and client host consistent between comparisons. For high client counts, the pgbench process itself can become the bottleneck.

5. Try a custom script without changing the schema

Custom scripts are useful when the built-in transaction does not represent your application. A script contains SQL statements terminated by semicolons. In this safe example, the query only reads a constant, so it does not need the pgbench tables or initialisation.

$ cat > /tmp/pgbench-read.sql <<'SQL'
SELECT 1;
SQL
$ pgbench -n -f /tmp/pgbench-read.sql -c 2 -t 1000 "$PGDATABASE"
$ rm -f /tmp/pgbench-read.sql

-n disables the pre-test vacuum and is necessary for a custom scenario that does not use the standard tables. Remove the temporary script when finished. For a real custom test, make the setup, transaction boundaries and rollback behaviour explicit. If the script ends with an incomplete transaction block, the client is aborted.

6. Handle logs, retries and failures carefully

Add -l to write per-transaction log files. The default prefix is pgbench_log; choose a directory and prefix that will not fill a filesystem.

$ pgbench -S -c 4 -T 60 -l --log-prefix=/tmp/pgbench-run "$PGDATABASE"
$ ls -lh /tmp/pgbench-run.*

For a long run, --sampling-rate=0.05 records about five per cent of transactions. Do not treat the sampled row count as TPS without correcting for the sampling fraction. --aggregate-interval=10 produces interval summaries, but it requires -l.

Serialization and deadlock errors are not retried by default: --max-tries defaults to 1. If your test needs retries, choose the limit deliberately, for example --max-tries=10, and inspect the retry counts. Retrying a script that contains multiple transactions can repeat transactions that already succeeded, and shell commands in a script are not rolled back.

Exit status 0 means success. Status 1 covers static or early startup failures such as an invalid option or an initial connection failure. Status 2 indicates an error during the run, such as a database or script failure, and may come with partial results.