Storage Sizing Report for Postgres

This report is for purpose of find larger tables for potential pruning. Table are organized largest to smallest with total sizing rolledup per schema.

WITH schema_table_index_sizes AS (
    SELECT 
        nspname AS schemaname, 
        relname, 
        CASE 
            WHEN relkind = 'r' AND reltoastrelid = 0 THEN 'table'
            WHEN relkind = 'i' THEN 'index'
            WHEN relkind = 'r' AND reltoastrelid != 0 THEN 'toast_table'
            ELSE 'other'
        END AS reltype, 
        pg_total_relation_size(pg_class.oid) AS total_size 
    FROM 
        pg_class 
    JOIN 
        pg_namespace ON pg_namespace.oid = pg_class.relnamespace 
    WHERE 
        nspname NOT LIKE 'pg_%' 
        AND nspname != 'information_schema' 
        AND relkind IN ('r', 'i')
)
SELECT 
    COALESCE(schemaname, 'All Schemas') AS schemaname,
    reltype,
    pg_size_pretty(SUM(total_size)) AS total_size
FROM 
    schema_table_index_sizes
GROUP BY 
    ROLLUP(schemaname, reltype)
HAVING 
    GROUPING(schemaname) = 0 
    OR GROUPING(reltype) = 1
ORDER BY 
    schemaname, 
    CASE 
        WHEN GROUPING(reltype) = 0 THEN 0
        ELSE 1
    END, 
    reltype DESC;