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