Listing table size of all schema tables.

posted Apr 28, 2017, 3:46 PM by Sachchida Ojha
How to check the size of a table?
Table Level:
psql> select pg_size_pretty(pg_relation_size('schema.tablename')); 
Replace schema.tablename with your search table.
Table and Index: 
psql> select pg_size_pretty(pg_total_relation_size('schema.tablename')); 
Replace schema.tablename with your search table.

How to check the Schema size?
Schema Level:
psql> select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB"
from pg_tables where schemaname='SCHEMANAME' group by 1;
Replace SCHEMANAME with your schema name.

How to check the database size?
To see size of specific database:
psql> select pg_size_pretty(pg_database_size('DATBASE_NAME'));
Example: gpdb=# select pg_size_pretty(pg_database_size('gpdb'));
24 MB
(1 row)
To see all database sizes:
psql> select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

psql>select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database; 

How to check partitioned table size including indexes and partitions?
Table size with partitions:
The following SQL gives you employee_dailly table size, which includes partitions.
select schemaname,tablename,round(sum(pg_total_relation_size(schemaname || '.' || partitiontablename))/1024/1024) "MB"
from pg_partitions where tablename='employee_daily' group by 1,2;
schemaname | tablename | MB
public | employee_daily | 254