Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get the size of different tables and other relations in a PostgreSQL database
SELECT
schema_name, rel_name, table_size,
pg_size_pretty(table_size) AS size
FROM (
SELECT
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..
SELECT
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;
@peterc

This comment has been minimized.

Copy link
Owner Author

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
You can’t perform that action at this time.