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.

Comments

Subscribe to new articles

If you enjoy my content, consider subscribing. You will only receive new blog stories, no other email.

Work

Site

© 2022 Dave Blakey. All rights reserved.

Development, startup and tech tips from Gatsby to Laravel, Strapi to PHP.