Hot to Get the Index and Data Size of a Table In PostgreSQL
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
Table | Data Size (MB) | Index Size (MB) | Total Size (MB) |
---|---|---|---|
entries | 127 MB | 161 MB | 288 MB |
blocks | 69 MB | 36 MB | 105 MB |
events | 79 MB | 20 MB | 99 MB |
entry_views | 16 MB | 34 MB | 50 MB |
files | 25 MB | 3904 kB | 29 MB |
comments | 9800 kB | 3192 kB | 13 MB |
streaks | 944 kB | 4312 kB | 5256 kB |