postgresql,

PostgreSQL DB size larger than the sum of table sizes

Dec 10, 2022 · 1 min read · Post a comment

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.

Prerequisites

  • PostgreSQL

Solution

Step 1. List DB size. For instance:

SELECT pg_size_pretty(pg_database_size('devcoops'));

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.

Conclusion

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.

postgresql