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