Francesco Tisiot: How to use pgbench to test PostgreSQL® performance

Testing a database performance is a must in every company. Despite everyone's needs beings slightly different, a good starting point for PostgreSQL® database is using pgbench: a tool shipped with the PostgreSQL installation that allows you to stress test a local or remote database. This blog post showcases how to install (on a Mac) and use pgbench to create load on a remote PostgreSQL database on Aiven. If you need a FREE PostgreSQL database? 🦀 Check Aiven's FREE plans! 🦀 If you need to optimize your SQL query? 🐧 Check EverSQL! 🐧 Install pgbench locally In a Mac, pgbench comes with the default PostgreSQL installation via brew. Therefore to have pgbench all you need is to: brew install postgresql Create a test PostgreSQL environment While you could create a test PostgreSQL environment locally (the 1brc challenge blog post contains all the details), this time we'll create an Aiven for PostgreSQL service in minutes: Navigate to Aiven Console Create an account Create an Aiven for PostgreSQL service on your favorite cloud provider and region Select the startup-4 plan, it will be sufficient for the test. Use pgbench to load test the a PostgreSQL database The Aiven for PostgreSQL database comes with a defaultdb database we can use for our testing. Step 1: Initialize the database All we need is to grab the connection details from the Aiven Console and we are ready to initialize the database with: pgbench -h \ -p \ -U \ -i \ -s \ Where: is the database hostname is the database port is the database user is the database name SCALEFACTOR is the test scale factor, 100 could be a good place to start. The default is 1 which will create a 16MB database, the 100 scale will create a 1.6GB database. We'll be prompted to write the password, that[...]