postgresqlsize

How to get PostgreSQL tables total disk size under a schema


Our PostgreSQL database was getting bigger and we wanted to identify what tables are guilty.


Solution

  • Solution 1:

    SELECT
        table_schema || '.' || table_name AS table_full_name,
        pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_size
    FROM
        information_schema.tables
    WHERE
        table_schema NOT IN ('information_schema', 'pg_catalog')
    ORDER BY
        pg_total_relation_size(table_schema || '.' || table_name) DESC;
    

    Solution 2: if you only want to check a certain table use:

    SELECT pg_size_pretty(pg_total_relation_size('table123'));
    

    Output sample:

    table1,25 GB

    table2,14 GB

    table3,11 GB