Re: diskspace

On 6 February 2013 11:12, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> On 6 February 2013 11:04, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
>
>> Have you tried using pg_filedump
>> (http://pgfoundry.org/frs/?group_id=1000541)
>> to dump a page or two of your table and figure
>> out what is where and where the space went?
>>
>
> I haven't; I will do for interest's sake, thanks for the suggestion.
>

A simple hexdump output shows that I can see each row taking an extra 28 to
31 (depending on the number of bytes padding to align after the varchar)
bytes inline, which is about we thought. There's also a variable amount of
wasted space in each page where the next row won't fit in the page, varying
between 0 and (I guess) the largest row size + 30.

pg_filedump -a 22212| grep -i 'Free space' | cut -c46- | perl -nle '$sum +=
$_ } END { print $sum'

gives us a total of the "free space" values for all blocks in the 22212
table at 1875964 bytes.

Given that there are 11367253 rows and we accept (being overly generous) an
extra 35 bytes per row (379MB) plus 178MB real data plus just under 2MB
free space plus 78494 block headers of 60 bytes each (ignoring the
4-bytes-per-row in the block header because we've already included that in
the "35" bytes-per-row value) of 4MB gives a total of 563MB. I'm still 65MB
short of the 618MB space taken.

Not that that's going to make any significant difference but I am now
intrigued as to where it's gone. :)

Geoff