PostgreSQL查询数据库、表大小

列出所有数据库大小：

SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

列出所有schema大小：

SELECT
    schemaname,
    pg_size_pretty(sum(pg_total_relation_size(relid))) AS total_size
FROM pg_catalog.pg_statio_user_tables
GROUP BY schemaname
ORDER BY sum(pg_total_relation_size(relid)) DESC;

列出当前schema所有表大小（含 TOAST）：

SELECT
    s.schemaname AS schema,
    s.relname    AS table_name,
    pg_size_pretty(pg_total_relation_size(s.relid)) AS total_size,
    pg_size_pretty(pg_relation_size(s.relid))       AS data_size,
    pg_size_pretty(pg_indexes_size(s.relid))        AS index_size,
    pg_size_pretty(pg_total_relation_size(s.relid)
                   - pg_relation_size(s.relid)
                   - pg_indexes_size(s.relid))      AS toast_size
FROM pg_catalog.pg_statio_user_tables s
ORDER BY pg_total_relation_size(s.relid) DESC;

说明：
pg_relation_size：表的数据部分大小
pg_indexes_size：索引大小
pg_total_relation_size：总大小（数据+ 索引 + TOAST）
toast_size存放超大字段（TEXT / JSONB / BYTEA等）的TOAST区大小（隐式部分）
结果按总大小降序排列，方便找“胖表”