My top queries to analyze the DB on PostgreSQL

Hi community, 

 

Today I would like to share a small snippet to investigate DB and RDBMS PostgreSQL. 

Before starting to check and do “explain analyze” of slowest and frequent slow queries I do:

select sum(xact_commit) as commits, sum(xact_rollback) as rollbacks
from pg_stat_database;

Just to understand how often we meet with rollbacks.

I do recommend to run a few times that query with a small timeout to understand frequency. 

 

Next one is used and remained connections query:

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
from
 (select count(*) used from pg_stat_activity) t1,
 (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
 (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;

After we are ready to check the status of connections. Helpful to plan pooling.

with states as
   (select datname, client_addr, case
       when now() - state_change < interval '10 seconds' then '10sec'
       when now() - state_change < interval '30 seconds' then '30sec'
       when now() - state_change < interval '60 seconds' then '60sec'
       else 'idle' end
   as stat from pg_stat_activity)
select datname, client_addr, stat, count(*)
from states group by datname, client_addr, stat;

It helps to understand the counts and duration. 

Then I do a review of quantitative parameters of databases usually of Atlassian systems. 

It’s size of database, number of rows and indexes. 

-- This query returns list of largest (by data size) tables.
select schemaname as table_schema,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as data_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as external_size
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc
limit 10;

 

All those queries you can checkout from repo. https://github.com/gonchik/cleanup-scripts/tree/master/sql/postgresql

Hope it helps. 

Feel free to share some interesting snippets of DB, RDBMS analysis.