pgbench

Initialize a database with a scale factor of 50 times the default size

$ pgbench --initialize --scale=[50] [database_name]

$ pgbench --client=[10] --jobs=[2] --transactions=[10000] [database_name]

pgbench [OPTION]... [DBNAME [SCALE]]

-b script
    add "script" after builtin scripts

-c NUM
    number of concurrent clients (default: 1)

-C
    new connection per transaction

-d
    print debugging output

-D var=value
    define custom variable

-f file
    read script from file

-F factor
    table fillfactor (default: 100)

-h host
    database host or socket dir

-i
    initialize database tables

-I
    single-command account initialization

-j jobs
    number of worker threads (default: 1)

-l
    write transaction times to log

-L limit
    rate limit in TPS

-M {plain|extended|prepared|simple}
    query submission protocol

-n
    skip database creation

-N
    skip pgbench_accounts updates

-O
    no per-command latency stats

-p port
    database port

-P interval
    progress report interval (s)

-r
    report latency per command

-R maxrate
    aggregate rate limit in TPS

-s scale
    scaling factor

-S
    SELECT-only transactions

-t N
    transactions per client (default: 10)

-T time
    duration in seconds

-U username
    connect as user

-v
    vacuum tables before tests

-z
    show processed transactions

-V, --version
    print version and exit

--help
    show help and exit

pgbench is a point-estimate benchmark tool for evaluating PostgreSQL database performance. It simulates a banking transaction workload based on the deprecated TPC-B standard, testing throughput (transactions per second, TPS) and latency under concurrent client loads.

It requires initialization with the -i option to create tables like pgbench_accounts, pgbench_branches, pgbench_tellers, and pgbench_history, scaled by a factor for data volume. Tests run predefined scripts (TPC-B-like: tpcb-simple, tpcb-like, tpcb-complex) or custom SQL scripts.

Key metrics include total transactions, TPS, latency percentiles, and connect/setup times. Supports multiple threads, rate limiting, logging, and various query protocols (plain, prepared, extended). Ideal for comparing hardware, configurations, or PostgreSQL versions, but not for production simulation due to its synthetic nature.

Usage involves initializing a test database, then running with client count, transaction count, or time-based duration.

Requires PostgreSQL installation and superuser access for init; synthetic workload not representative of real apps; high loads may overload small systems; logs can grow large with -l.

TPC-B like: tpcb-simple (1 SELECT, 1 UPDATE, 1 INSERT); tpcb-like (more complex); tpcb-complex (joins); select-only with -S.

SQL files with meta-commands like \set, \if; variables from -D; executed via -f or -b.

Reports: latency average/min/max, TPS (incl/excl connections), percentiles (90,95,99), SQL statistics with -r.

Introduced in PostgreSQL 7.4 (2003) as pgbench; enhanced in 8.3 with custom scripts, 9.0 with rate limits, 9.1 with throttling, and recent versions adding latency histograms and protocol options. Maintained in postgresql-contrib.

psql(1), createdb(1), pg_dump(1)