My top queries to analyze the DB on PostgreSQL
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
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
(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))
order by pg_total_relation_size(relid) desc,
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.