A while ago, I saw this post on Paul’s blog, about a query to get database size. I looked into it, but I was pretty unhappy with the query, as pg_database_size appears to include the size of pg_catalog.

The actual query in question is this:

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname))
        AS size FROM pg_database;

The query I came up with, has a much more accurate usage of what a user can actually control, the downside being you have to connect to each database to get usage:

SELECT
    CURRENT_DATABASE(),
    pg_size_pretty(sum(pg_total_relation_size(tablename))::int8)
        AS size
    FROM pg_tables
    WHERE schemaname != 'pg_catalog'
    AND schemaname != 'information_schema';

For a better breakdown of which tables are hogging disk space, I came up with this:

SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename)) AS total_usage,
    pg_size_pretty((pg_total_relation_size(tablename)
        - pg_relation_size(tablename))) AS external_table_usage
    FROM pg_tables
    WHERE schemaname != 'pg_catalog'
        AND schemaname != 'information_schema'
    ORDER BY pg_total_relation_size(tablename);

The external_table_usage column shows how much space is being used for things such as indexes, very useful if you’re looking to free up some space by dropping unneeded indexes.