Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Get the size of different tables and other relations in a PostgreSQL database
schema_name, rel_name, table_size,
pg_size_pretty(table_size) AS size
nspname AS schema_name,
relname AS rel_name,
pg_table_size(pg_class.oid) AS table_size
FROM pg_class, pg_namespace
WHERE pg_class.relnamespace = pg_namespace.oid
) _
WHERE schema_name NOT LIKE 'pg_%'
AND schema_name != 'information_schema'
ORDER BY table_size DESC;
-- or if just tables is OK, then this simpler query..
relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total,
pg_size_pretty(pg_relation_size(relid)) AS internal,
pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Copy link

peterc commented Feb 26, 2019

Tweaked so it includes the TOAST data as well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment