LF BLOG // January 21, 2022 Show the disk usage of your largest tables in PostgreSQL

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.