Tag Archives: size

PostgreSQL: useful commands

show count of active connections

select datname, count(*) from pg_stat_activity group by datname;

show size of db

SELECT pg_size_pretty(pg_database_size('DATABASE_NAME'));

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;