Postgres table sizing

We’ve already looked at CPU related optimizations. Aligned with the goal of cost optimization, say you were also looking into storage optimization, to eventually slim down the overprovisioned RDS Postgres instances by few TBs. During the investigations, you encounter the following, seeming discrepancy between the “Total” and “Actual” size, courtesy of a query you got off the web. Whence the difference? Let’s investigate.

db=> SELECT
db->    relname  as table_name,
db->    pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
db->    pg_size_pretty(pg_indexes_size(relid)) as "Index Size",
db->    pg_size_pretty(pg_relation_size(relid)) as "Actual Size"
db->    FROM pg_catalog.pg_statio_user_tables
db-> ORDER BY pg_total_relation_size(relid) DESC;

db-> pgsizepretty(pgrelationsize(relid, ‘main’)), db-> pgsizepretty(pgrelationsize(relid, ‘vm’)), db-> pgsizepretty(pgrelationsize(relid, ‘fsm’)), db-> pgsizepretty(pgrelationsize(relid, ‘init’)) db-> FROM pgcatalog.pgstatiousertables db-> ORDER BY pgtotalrelation_size(relid) DESC db-> limit 3;