BLOG // July 19, 2022
See the disk usage of your largest PostgreSQL tables
Managing database storage usage can be challenging with Postgres. It's not always easy to see how much is in use, and even when you can it's hard to tell what can be re-used vs actual used space. The command below will show you your top 20 tables by total_size - e.g. disk storage used.
SELECT relname AS "relation", 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' ORDER BY pg_total_relation_size (C .oid) DESC LIMIT 20;
Remember, PostgreSQL does not free up disk space after a delete. You need to do a full vacuum to force that.
Subscribe to new articles
If you enjoy my content, consider subscribing. You will only receive new blog stories, no other email.