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_nameTotal SizeIndex SizeActual Size
some_table301 GB4641 MB33 GB
second_table103 GB58 GB45 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;

At least this isn't a full screen popup

That'd be more annoying. Anyways, subscribe to my newsletter to get new posts by email! I write about AWS, Elixir, and more.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.