pgbench

A utility for running benchmark tests

pgbench is a core utility which runs benchmarking tests on PostgreSQL.

pgbench was added in PostgreSQL 7.0.

Installation

pgbench consists of a single binary file which is usually installed into the same location as other PostgreSQL binaries such as psql. pgbench can be installed from source by a simple "make && make install" from the contrib/pgbench directory, or depending on packaging system may be available as a separate package or part of a package containing other client utilities.

Usage

pgbench provides a default set of sample data and tests (loosely based on TPC-B), however customised script files can also be used.

Change history

work-in-progress

  • PostgreSQL 14
    • libpq pipeline support added via new commands \startpipeline and \endpipeline (commit 9aa491ab)
  • PostgreSQL 13
    • options --partitions and --partition-method added (commit b1c1aa53)
    • --init-steps option accepts G to generate data on the server side, rather than have it generated by the client (commit a386942b)
    • --show-script option added to dump contents of built-in scripts (commit 5823677a)
    • \aset command added, which is like \gset but able  to store all results from combined SQL queries into separate variables (commit 9d8ef988)
  • PostgreSQL 12
    • \gset command added (initial commit 6260cc55)
    • --rate option precision improved (commit 5b7e0367)
    • error reporting improved (commit 5b75a4f8)
    • maximum number of variables/arguments increased from 10 to 256 (commit a4784152)
  • PostgreSQL 11
    • major scripting language improvements (commit bc7fa0c1)
    • pow() / power() function added (commit 7a727c18)
    • \if support added (commit f67b113a)
    • non-ASCII characters permitted in variable names (commit 9d36a386)
    • hashing functions hash(), hash_murmur2() and hash_fnv1a() added (commit e51a0484)
    • random_zipfian() function added (commit 1fcd0ade)
    • option --init-steps added (commit 591c504f)
    • option --random-seed added (commit 64f85894)
    • accuracy of statistics generated wth --latency-limit and --rate improved (commits c23bb6ba and16827d44)
  • PostgreSQL 9.5
  • PostgreSQL 9.4
    • --rate option added (initial commit fc9f4e9f)
    • --progress option added (initial commit 4a87f308)
    • hard-wired line length limit in custom script files removed (commit 61a07bae)
    • long options added (commit 79cddb18)
  • PostgreSQL 9.3
  • PostgreSQL 9.2
    • option --tablespace added (commit 7c263956)
    • option --index-tablespace added (commit 7c263956)
    • option --unlogged-tables added (commit commit2d6fee09eba8474d9a69c08bf716f3e2d31e5fdf)
  • PostgreSQL 8.4
    • -M option added for specifying the query protocol type to use (commit 49639a7b)
    • -T option added for specifying duration in seconds (commit cff4aa6a)
    • pgbench_ prefix added to the standard tables accounts, branches, history, and tellers (commit 48caf91b)
  • PostgreSQL 8.3
  • PostgreSQL 7.0

Examples

Default tests

The default tests provided with pgbench require the installation of a set of sample tables and data into the target database; this is done with:

pgbench -i  [ other-options ] dbname

where other-options should include the same connection parameters used by psql and other libpq-based clients.

Executing pgbench again without the -i option will cause a basic test to be executed, e.g.:

postgres:~$ pgbench -U postgres testdb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 108.049703 (including connections establishing)
tps = 110.750557 (excluding connections establishing)

The default options do not provide particularly useful output; the most useful options for tuning pgbench are:

  • -c (number of clients)
  • -t (number of transactions)
  • -T (time limit)
  • PostgreSQL documentation: pgbench
  • PostgreSQL community wiki: Pgbench

Categories

Contrib module, Performance