Schema Size

select

sosdnsp as schemaname,

sosdschematablesize/(1024*1024*1024) as SchemaTableSizeGB,

sosdschemaidxsize/(1024*1024*1024) as SchemaIndxSizeGB,

round((sosdschematablesize/power(1024,4)+sosdschemaidxsize/power(1024,4)):: numeric,2) as SchemaSizeTB

from schema_size() where sosdschematablesize+sosdschemaidxsize >0;

Lets look into more details about schema size scripts.

1. select * from gp_toolkit.gp_size_of_schema_disk;

output

"sosdnsp";"sosdschematablesize";"sosdschemaidxsize"

"gpcmdr_instance_gpperfmon";557056;0

"public";186917552128;0

2. select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))) "Size" from pg_tables group by 1;

output

"schemaname";"Size"

"public";186940325888

"gpcmdr_instance_gpperfmon";557056

"pg_catalog";2902949888

"information_schema";44498944

"gp_toolkit";0

========================

3. SELECT schema_name,

       pg_size_pretty(sum(table_size)::bigint),

       (sum(table_size) / pg_database_size(current_database())) * 100

FROM (

  SELECT pg_catalog.pg_namespace.nspname as schema_name,

         pg_relation_size(pg_catalog.pg_class.oid) as table_size

  FROM   pg_catalog.pg_class

     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid

) t

GROUP BY schema_name

ORDER BY schema_name;

output

"schema_name";"pg_size_pretty";"?column?"

"gp_toolkit";"0 bytes";0.0000000000000000000000000000

"gpcmdr_instance_gpperfmon";"544 kB";0.000294944629295107733700

"information_schema";"21 MB";0.01178043548772812700

"pg_catalog";"2720 MB";1.51006445293872304800

"pg_toast";"640 MB";0.35525213114391917400

"public";"174 GB";98.66381906107079193400

If you are using the sql #2 (directly using pg_total_relation_size), it misses out on the indexes as well as some required size components of any append-only tables. It also needs usage grants on all the schemas.

If you are using sql #1 (gp_toolkit.gp_size_of_schema_disk), it will return without error if the user running the SQL has usage on all the schemas. It will, however, not return correct data if the user does not have select access on all the tables (it will simply exclude the tables to which the user does not have select access).

The best way to use a service account to get schema size correctly without having to grant any other unnecessary privileges is to use a stored function with security definer construct like below.

As gpadmin:

-- Schema Size

create or replace function schema_size()

returns setof gp_toolkit.gp_size_of_schema_disk

as

$$

select * from gp_toolkit.gp_size_of_schema_disk;

$$

language sql volatile security definer;

-- Table Size

create or replace function table_size()

returns setof gp_toolkit.gp_size_of_table_disk

as

$$

select * from gp_toolkit.gp_size_of_table_disk;

$$

language sql volatile security definer;

-- Table and Index Size

create or replace function tableandindex_size()

returns setof gp_toolkit.gp_size_of_table_and_indexes_disk

as

$$

select * from gp_toolkit.gp_size_of_table_and_indexes_disk;

$$

language sql volatile security definer;

grant execute on function schema_size() to sachi;

grant execute on function table_size() to sachi;

grant execute on function tableandindex_size() to sachi;