where datname = 'DATABASE_NAME'
show count of active connections
select datname, count(*) from pg_stat_activity group by datname;
show size of db
show size of all dbs
SELECT pg_size_pretty(sum(pg_database_size(datname))) from pg_database;
show table size
SELECT relname, pg_total_relation_size(oid) / (1024 * 1024) as sizeM from pg_class order by 2 desc limit 100
show tables size, also it calculates sizes of segmented tables and doesn't show segments in result.
select s.schemaname, s.relname,pg_size_pretty(sm) from (select oid, sum(sm) as sm from (select oid,pg_total_relation_size(oid) as sm from pg_class where relkind='r' and oid not in (select inhrelid from pg_inherits) union (select c.oid,sum(pg_total_relation_size(i.inhrelid)) as sm from pg_class c, pg_inherits i where c.oid=i.inhparent group by c.oid)) t group by oid) tt, pg_stat_user_tables s where s.relid=tt.oid order by sm desc;
show current requests and they duration time
SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity ORDER BY duration DESC;
show list of blocks
SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid FROM pg_locks AS l LEFT JOIN pg_database AS d ON l.database= d.oid LEFT JOIN pg_class AS c ON l.relation = c.oid;