I was attempting to calculate the actual occupied space by a Table.
I summed up the avg_width of each column of a table from pg_stats, which gives me the average size of a row (277 bytes).
select sum(avg_width) as average_row_size from pg_stats where tablename='tablename'
average_row_size
---------------------------
277
(1 row)
Calculated the actual occupied space by rows in the table as below -
Took the average_row_size * number_of_rows from pg_class
select 277*reltuples/1024 as occupied_space from pg_class where relname='tablename'; == 552 KB
occupied_space
-------------------------
552.6474609375
Calculated the actual Table size (600 kb)
select pg_size_pretty(pg_relation_size('tablename'));
pg_size_pretty
----------------
600 KB
(1 row)
Calculated the free space with in the table (by scanning the pages - as suggested by Shaun Thomas) -- 14 KB
SELECT pg_size_pretty(free_space) AS mb_free FROM pgstattuple('tablename');
mb_free
---------
14 KB
(1 row)
600 KB is the size of the table (taken through pg_size_pretty)
14 KB is the free space (taken through contrib modules)
600+14 = 586 KB -- is the occupied space by normal calculation through contrib modules. This is based on number of pages allocated to the table.