PostgreSQL Performance Tuning Lab with pg_profile and pgbench

Steps

if you looking to get oracle AWR report in postgresql look no further than using pg_profile , its extension help you get full statistics report of postgresql instance that you view in your browser to check lots of statistics regarding I/O memory blocking and long running query 

Step 1 – Enable required extensions at server level

Execute (in postgresql.conf):

• go to /etc/postgresql/16/main/postgresql.conf
  update the below config 

shared_preload_libraries = ‘pg_stat_statements,’ track_io_timing = on track_functions = ‘all’ track_wal_io_timing = on

restart postgresql sudo systemctl restart [email protected]

Step 2 – Install pg_profile files

cd /tmp VER=4.10 wget https://github.com/zubkov-andrei/pg_profile/releases/download/$VER/pg_profile–$VER.tar.gz tar xzf pg_profile–$VER.tar.gz –directory /usr/share/postgresql/16/extension

Step 3 – Create DB-level extensions

login to postgres and enable the extension either on postgresql or create seprate database for report 

CREATE EXTENSION dblink; CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE EXTENSION pg_profile;

Step 4 – Verify “local” server

check if extension is working fine by running the below query ,ne row with server_name = local, enabled = t.

SELECT * FROM show_servers();

Step 5 – Schedule sampling (cron)

we will now schedule jop that execute sampling of report daily using cron jop
switch to postgres user and type crontab -e 

add the below schedule 

*/30 * * * * psql -d postgres -Atqc “SELECT take_sample();” > /dev/null 2>&1

Step 6 – test report using pgbench DB

first create database and the run the following command to create tables for testing 

sudo -u postgres pgbench -i -s 20 [db-name]

Step 7 – Take baseline sample

run the following command to take baseline report 

SELECT * FROM take_sample(); SELECT * FROM show_samples();

take_sample()

• This function collects a snapshot of your server’s statistics (from pg_stat_statements, pg_stat_kcache, etc.) and stores it in the pg_profile schema.
  show_samples()
• Lists all samples that have been taken so far.
• Each row has a sample ID, timestamp, and server reference.
• Result: After your first run, you’ll see at least one sample with sample_id = 1. After each new take_sample(), another row appears.

Step 8 – Run write-heavy load

sudo -u postgres pgbench -c 20 -j 4 -T 180 bench

Step 9 – Take post-load sample

sudo -u postgres psql SELECT * FROM take_sample(); SELECT * FROM show_samples();

Step 10 – Generate HTML report

psql -d postgres -Aqtc “SELECT get_report(‘local’, 1, 2)” -o /tmp/pgprof_1_2.html

open the report you will get report similar to oracle AWR that help you troublshoot the performance issue