Listing table size of all schema tables.

Post date: Dec 12, 2013 5:15:24 PM

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