Another issue to get off my chest. A few weeks back, I was struggling to find why the production PostgreSQL DB size was significantly larger than the sum of table sizes. I was bamboozled. However, it turns out I was running the “wrong” SQL query the whole time. So, here’s the deal.
Step 1. List DB size. For instance:
Step 2. Next thing, to list all table sizes in details, run the following query:
SELECT relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) As "Total Size", pg_size_pretty(pg_relation_size(relid)) as "Relation Size (main fork)", pg_size_pretty(pg_relation_size(relid, 'fsm')) as "Relation Size (fsm fork)", pg_size_pretty(pg_relation_size(relid, 'vm')) as "Relation Size (vm fork)", pg_size_pretty(pg_relation_size(relid, 'init')) as "Relation Size (init fork)", pg_size_pretty(pg_table_size(relid)) as "Table Size", pg_size_pretty(pg_indexes_size(relid)) as "Index Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Note: However, you need to understand the overall differences between pg_table_size, pg_relation_size, pg_indexes_size and pg_total_relation_size though.
Step 3. From here on, it depends on what table, relation or even index is bloated. Yet, this will be the symptom only. You’ll need to further identify the actual underlying cause. Alas, there’s no all-round solution.
Related post: Show database and table size in PostgreSQL.
Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.