Wednesday, September 18, 2013

Finding size of database table - postgresql

CREATE OR REPLACE FUNCTION pg_partition_table_size(text) returns text as  
select pg_size_pretty(sum(pg_relation_size(inhrelid))::bigint) from pg_inherits where inhparent=$1::regclass;  
$$ language sql;  

postgresql# select pg_partition_table_size as Size from pg_partition_table_size('url_daily_aggregate');
 19 GB
(1 row)

Finding top 20 largest table (non-partitioned)

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size"  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') and relname = '$TOKEN' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

No comments: