PostgreSQL Performance Monitoring Queries

-- Show open PostgreSQL database transactions
select * from pg_stat_activity;

-- Show statistics for each database in the cluster
select * from pg_stat_database;

-- Show statistics related to the background writer process
select * from pg_stat_bgwriter;

-- Show statistics about user-defined indexes
select * from pg_stat_user_indexes;

-- Show statistics about user-defined tables
select * from pg_stat_user_tables;

-- Show statistics about table and index I/O
select * from pg_statio_all_tables;
select * from pg_statio_all_indexes;

-- Show statistics about query execution times
-- CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
select * from pg_stat_statements;

-- Show information about locks held by active processes
select pg_database.datname as database_name, pg_locks.*
from pg_locks
left join pg_database on pg_locks.database = pg_database.oid;

-- Show replication statistics
select * from pg_stat_replication;

-- Show statistics about database size
select pg_size_pretty(pg_database_size(current_database())) as db_size;

-- Show statistics about table sizes
select relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc;

-- Show statistics about index usage
select relname as index_name, idx_scan, idx_tup_read, idx_tup_fetch
from pg_stat_user_indexes
order by idx_scan desc;