Configurations

Tweak Postgres performance settings

postgresql.conf file has the performance settings and we can tweak them.

sudo -u postgres psql -c 'SHOW config_file;'

Reference Article - https://www.enterprisedb.com/postgres-tutorials/how-tune-postgresql-memory

This tool https://pgtune.leopard.in.ua suggest the values based on the system. Must see!

Active Connections

Number of active connections

SELECT COUNT(*) FROM pg_stat_activity WHERE state='active';

Index Performance per table

Number of index scans (per index and per table)

SELECT indexrelname, relname, idx_scan FROM pg_stat_user_indexes;