Database, schema, and table sizes in Greenplum

Starting in the 4.x release, you get size info from the gp_toolkit schema. To get the size of all databases and their size in bytes:

select sodddatname, sodddatasize from gp_toolkit.gp_size_database;

To see the database size in GB, TB, and/or MB.

TB:
select sodddatname, (sodddatsize/1073741824)/1024 AS sizeinTB from gp_toolkit.gp_size_of_database;

GB:
select sodddatname, (sodddatsize/1073741824) AS sizeinGB from gp_toolkit.gp_size_of_database;

MB:
select sodddatname, (sodddatsize/1048576) AS sizeinMB from gp_toolkit.gp_size_of_database;

For schema sizes, connect to your database and run:

TB:
select sosdnsp, (sosdschematablesize/1073741824)/1024 AS schemasizeinTB from gp_toolkit.gp_size_of_schema_disk;

GB:
select sosdnsp, (sosdschematablesize/1073741824) AS schemasizeinGB from gp_toolkit.gp_size_of_schema_disk;

MB:
select sosdnsp, (sosdschematablesize/1048576) AS schemasizeinMB from gp_toolkit.gp_size_of_schema_disk;

If you want a specific schema only, add WHERE sosdnsp='SCHEMA'

Take a given schema and find table sizes:

GB:
select sotdschemaname as SCHEMA, sotdtablename,(sotdsize/1073741824) as tableGB FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'yourschema' AND (sotdsize/1073741824) > 0 ORDER BY sotdtablename;

MB:
select sotdschemaname as SCHEMA, sotdtablename,round((sotdsize/1048576),2) as tableMB FROM gp_toolkit.gp_size_of_table_disk WHERE sotdschemaname = 'yourschema' AND (sotdsize/1048576) > 0 ORDER BY sotdtablename;

For an individual table you can get the size of the table as:

select pg_size_pretty(pg_relation_size('schema.table_name'));

You can get table and index size(total) with:

select pg_size_pretty(pg_total_relation_size('schema.table_name'));

For just index size use:
select (pg_size_pretty( pg_total_relation_size('schema.table_name') - pg_relation_size('schema.table_name'))) AS IndexSize;