On Wed, Oct 5, 2011 at 2:38 PM, Venkat Balaji <venkat.balaji@xxxxxxxx> wrote:
Hello,I was attempting to calculate the actual occupied space by a Table.Below is what i did -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_classselect 277*reltuples/1024 as occupied_space from pg_class where relname='tablename'; == 552 KBoccupied_space-------------------------552.6474609375Calculated 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 KBSELECT 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.
Its typo 600 - 14 = 586 KB
552 KB is the actual occupied size by the rows (taken by calculating avg row size ). This is based on number of rows with in the pages.586-552 = 34 KB -- is still free some where with in the occupied pages ( calculated through pg_stats and pg_class )34 KB is still free within the pages ( each 8K ) which is basically taken as occupied space.
One more point to add to this good discussion, each row header will occupy 24 bytes + 4 bytes pointer on page to tuple.
