How to benchmark on PostgreSQL with pgbench

What is pgbench?

Pgbench is a built-in benchmarking tool in PostgreSQL that simulates database workloads and tests the server performance. It is perfect for:

• Stress testing and optimizing parameters.

• Comparing the performance before and after configuration changes.

Setting up pgbench

By default, pgbench is included with PostgreSQL, so you don't install it separately. Since all benchmarking and load tests will be done from a remote client server, I need to install pgbench on this client server.

sudo apt install postgresql-contrib-14

Check with:

pgbench --version

Connect to the server.

pgbench -h <host> -p 5432 -U <user> -d <dbname> -c 10 -T 60

with:

• -c: number of clients (concurrency)

• -T: transactions per client

Using with custom scripts

While pgbench provides a built-in TPC-B–like workload, real-world applications rarely behave like this default scenario. To simulate more realistic traffic, pgbench allows you to define custom SQL scripts that reflect your actual queries and access patterns.

You can write a custom script in a file and run it with pgbench like this:

pgbench -f <script.sql> -c <clients> -T <duration_seconds> -h <host> -p <port> -U <user> -d <database>

Conclusion

Benchmarking is a critical step in understanding how a database performs under load, and pgbench provides a simple yet powerful way to do this with PostgreSQL. From basic tests using the default workload to more advanced scenarios with custom scripts, pgbench allows you to simulate real-world traffic and measure key performance metrics such as throughput and latency.