We have migrated to new Google sites!
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