How to check the size of a table? Answer: 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? Answer: 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? Answer: 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')); pg_size_pretty ---------------- 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? Answer: 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 |
|
|