Get the disk space used by tables and indexes on all the tables in a PostgreSQL database.
WITH indexsizes AS (
SELECT C .oid,
relname AS "relation",
nspname,
pg_size_pretty (
pg_total_relation_size (C .oid)
) AS "total_size"
FROM
pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE
nspname NOT IN (
'pg_catalog',
'information_schema'
)
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast')
SELECT nspname,
relation,
pg_size_pretty(pg_table_size(oid)) AS table_size,
pg_size_pretty(pg_indexes_size(oid)) AS index_size,
pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM indexsizes
ORDER BY pg_total_relation_size(oid);