Uncover valuable insights into the disk space usage of PostgreSQL tables with a simple query. To retrieve detailed information about the disk space occupied by each table, execute the following query
select relname as "table_name",
pg_size_pretty(pg_total_relation_size(relid)) as "table_plus_index_size",
pg_size_pretty(pg_table_size(relid)) as "table_size",
pg_size_pretty(pg_indexes_size(relid)) as "index_space"
from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc;
This query provides a comprehensive view of size details for all user tables in the current PostgreSQL database. The results are formatted for easy readability using kB, MB, GB, or TB, thanks to the pg_size_pretty function. For a quick method to estimate the approximate number of rows in each table, additional details are available here.https://heyitissimple.com/fast-method-to-find-postgresql-tables-approximate-row-count/