How to use pgbench for PostgreSQL benchmark testing

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements .   There are various configurables such as sessions (single or multiple) and clients.   The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing.

The measurement is based on transactions per second.  In other words - you'll define how many transactions to execute per client and this will result in a rate per second. 

There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise different testing approaches

Notes

• I normally use pgbench for hardware and infrastructure benchmarking. For applications I use different methodology, based on custom transaction scripts and often application specific
• Tps – transactions per second
• Run from a remote server if network latency is trivial

Create a test database

CREATE DATABASE mydb

Initialise the pgbench db – this will create the sample tables in the db

pgbench -U postgres -p 5555 -i -s 50  mydb

-I initialize the db. Creates a bunch of default tables

-s  scaling option. i.e take the default rows and x 50 or whatever scaling number you require

This step will create 4 tables - which host the test date rows and other metadata

Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres

Establish a baseline

pgbench -U postgres -p 5555 -c 10 -j 2 -t 10000 mydb

-c number of clients

-j 2 number of threads

-t amount of transactions

These values are 10000 transactions per client. So : 10 x 10000 = 100,000 transactions

Get Results

A sample resultset 

Read more on benchmark performance testing 

TPC-H generate test data , test queries and sql database benchmark ...

What is acceptable database server performance testing