Hot to Get the Index and Data Size of a Table In PostgreSQL

loading views

You can get the data and index sizes of all tables in a PostgreSQL database with the following query:

Query

SELECT
    table_name AS table_full_name,
    pg_size_pretty(pg_table_size(table_name)) AS "Data Size",
    pg_size_pretty(pg_indexes_size(table_name)) AS "Index Size",
    pg_size_pretty(pg_total_relation_size(table_name)) AS "Total Size"
FROM
    information_schema.tables
WHERE
    table_schema = 'public'
ORDER BY
    pg_total_relation_size(table_name)
    DESC;

Output

TableData Size (MB)Index Size (MB)Total Size (MB)
entries127 MB161 MB288 MB
blocks69 MB36 MB105 MB
events79 MB20 MB99 MB
entry_views16 MB34 MB50 MB
files25 MB3904 kB29 MB
comments9800 kB3192 kB13 MB
streaks944 kB4312 kB5256 kB