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;
|table_name||Total Size||Index Size||Actual Size|
|some_table||301 GB||4641 MB||33 GB|
|second_table||103 GB||58 GB||45 GB|
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;