pgbench

runs TPC-B-like benchmarks on PostgreSQL databases

TLDR

Initialize benchmark database

$ pgbench -i [database]

Run benchmark

$ pgbench -c [10] -t [100] [database]

Run benchmark for duration

$ pgbench -c [10] -T [60] [database]

Run with custom script

$ pgbench -f [script.sql] [database]

Run read-only benchmark

$ pgbench -S -c [10] -T [30] [database]

Run with multiple threads

$ pgbench -c [20] -j [4] -T [60] [database]

Initialize with scale factor

$ pgbench -i -s [10] [database]

SYNOPSIS

pgbench [-i] [-c clients] [-t transactions] [-T seconds] [-f script] [options] database

pgbench runs TPC-B-like benchmarks on PostgreSQL databases. It measures transaction throughput under concurrent load.Initialization (-i) creates benchmark tables. Scale factor multiplies data size: scale 10 creates 1 million rows. Larger scales test different workload characteristics.The benchmark simulates banking transactions: updates to accounts, tellers, and branches. It measures transactions per second (TPS) under concurrent client load.Client count affects concurrency. More clients test parallel execution and locking. Thread count should match available CPU cores.Custom scripts test specific workloads. They can use variables, conditionals, and multiple statements to simulate application behavior.Progress output shows real-time TPS and latency. Final report includes average TPS, latency distribution, and any errors.

PARAMETERS

-i

Initialize benchmark tables.

-s SCALE

Scale factor for initialization.

-c CLIENTS

Number of concurrent clients.

-t TRANSACTIONS

Transactions per client (default: 10).

-T SECONDS

Duration in seconds.

-j THREADS

Number of threads.

-f FILE

Custom SQL script.

-S

Select-only (read-only). Shorthand for -b select-only.

-N

Skip updates to pgbench_tellers and branches. Shorthand for -b simple-update.

-b scriptname[@weight]

Built-in script: tpcb-like, simple-update, select-only. Optional weight for mixing.

-C

Establish a new connection for each transaction (measures connection overhead).

-M protocol

Query protocol: simple, extended, or prepared (default: simple).

-R rate

Target transaction rate in TPS. Adds sleep to maintain rate.

-L limit

Report transactions exceeding limit milliseconds as late.

-r

Report latency per statement.

-P SECONDS

Show progress every N seconds.

-l

Log transactions to file.

-n

Skip vacuuming before running the test.

-I steps

Initialization steps to perform (default: dtgvp). Characters: d=drop, t=tables, g=generate, v=vacuum, p=primary keys, f=foreign keys.

-h HOST

Database host.

-p PORT

Database port.

-U USER

Database user.

CAVEATS

Not a full TPC-B implementation. Results depend on hardware, configuration, and workload. Initialize with appropriate scale for meaningful tests.

HISTORY

pgbench has been part of PostgreSQL since version 7.0, originally written by Tatsuo Ishii. It provides a standard way to test PostgreSQL performance and compare configurations.