Warren's Cheat Sheet

Postgresql

Database sizing report

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;

Report on tables with bloat over 25%

select
	coalesce(nspname,'Grand Totals',nspname) AS "SCHEMA",
	coalesce(nspname || '.' || relname,'----------------------------------------',nspname || '.' || relname) as table_name,
	pg_size_pretty(sum(pg_relation_size(nspname || '.' || relname))) as actual_size,
	--  pg_relation_size(nspname || '.' || relname) a,
	pg_size_pretty(sum(pg_total_relation_size(nspname || '.' || relname))) as bloat_size,
	pg_size_pretty(sum(pg_total_relation_size(nspname || '.' || relname) - pg_relation_size(nspname || '.' || relname))) as reclaim_size_pretty,
	case
		when sum(pg_total_relation_size(nspname || '.' || relname)) > 0
    then (sum(pg_total_relation_size(nspname || '.' || relname) - pg_relation_size(nspname || '.' || relname)) * 100 / sum(pg_total_relation_size(nspname || '.' || relname)))::integer
		else 0
	end as bloat_percentage,
	sum(pg_total_relation_size(nspname || '.' || relname) - pg_relation_size(nspname || '.' || relname)) as reclaim_size,
	'VACUUM FULL VERBOSE ANALYZE ' || nspname || '.' || relname || ';' "sql"
from
	pg_class c
join pg_namespace n on
	c.relnamespace = n.oid
where
	relkind = 'r'
	and nspname not like 'pg_%'
	and nspname != 'information_schema'
	and reltuples > 0
	and (pg_total_relation_size(nspname || '.' || relname) - pg_relation_size(nspname || '.' || relname)) * 100 / pg_total_relation_size(nspname || '.' || relname) > 25
--	and pg_relation_size(nspname || '.' || relname) > 10033438720
group by
	rollup(nspname,
	relname)
having
	grouping(nspname) = 0
	or grouping(relname) = 1
order by
	nspname,
	case
		when grouping(relname) = 0 then 0
		else 1
	end,
	reclaim_size ASC;

Mysql

select
	table_schema "DB Name",
	ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) "DB Size in GB"
from
	information_schema.tables
group by
	table_schema;