How to get PostgreSQL tables total disk size under a schema

Question

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

Answer 1

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