Pgbench Performance Testing

Deploying OCI - Volumez Stack for pgbench

At Volumez we use Pgbench to test loads and achieve performance benchmarks.

Pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second).

Based on the pgbench running PostgresSQL with Volumez on OCI, Volumez provide ultra fast performance peaking at almost 69,000 TPS.

Follow the below guide to run your own Pgbench tests to achieve the same results:

Before running pgbench, ensure the OCI - Volumez Stack is deployed as described here.

PgBench Command Quick Reference

Command 1: Initialize Test Database

pgbench -i -s 50000 -F 70

This command initializes the test database with the following settings:

• -i: Creates fresh test tables (accounts, branches, tellers, history).

• -s 50000: Sets the scale factor to 50,000.

  • Creates ~5 billion rows in the accounts table.

  • Requires ~775GB of storage.

• -F 70: Sets the table fill factor to 70%.

  • Leaves 30% free space in tables.

  • Improves VACUUM efficiency.

Command 2: Run Benchmark

pgbench -c 768 -j 64 -T 3600 -M prepared -P 10

This command executes the benchmark with the following parameters:

• -c 768: Runs 768 concurrent clients.

• -j 64: Uses 64 worker threads to handle connections.

• -T 3600: Runs the test for 1 hour (3600 seconds).

• -M prepared: Uses prepared statements for better performance.

• -P 10: Reports progress every 10 seconds.

Step-by-Step Deployment

1. Deploy OCI - Volumez Stack

Follow the deployment steps as described here. Once connected to SSH continue to step 2.

2. Switch to the Postgres User

sudo -i -u postgres

3. Generate the pgbench Dataset

Run the following command and wait for completion:

pgbench -i -s 50000 -F 70

• This will generate a dataset of 700–800GB.

• Duration: Several hours, depending on the system performance.

• Recommendation: Use tmux to avoid disruptions (e.g., sleep mode).

Example 1 - Start:

Example 2 - End:

4. Run the pgbench Benchmark

pgbench -c 768 -j 64 -T 3600 -M prepared -P 10

• The test runs for 1 hour (3600 seconds).

• Example output: ~68k TPS.

5. Destroy Stack when finished

   

Combining Commands

To streamline the process, you can combine the dataset generation and benchmark run into a single command:

pgbench -i -s 50000 -F 70 && pgbench -c 768 -j 64 -T 3600 -M prepared -P 10