Re: diskspace

On Tue, Feb 5, 2013 at 3:33 PM, Geoff Winkless <pgsqladmin(at)geoff(dot)dj> wrote:

> Thanks for all the responses....
>
> On 5 February 2013 19:36, Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)com> wrote:
>
>> On Tue, Feb 5, 2013 at 11:00 AM, Scott Mead <scottm(at)openscg(dot)com> wrote:
>>
>>> Agreed, don't forget, you have indexes, free space, vacuum-able stuff,
>>> etc... all laying in your datafiles. Your measurements are telling you
>>> what you have purely in a raw form.
>>>
>>
> Indexes are stored separately. There should be no free space (why would
> there be? FILLFACTOR is 100!) and I've vacuumed already.
>
>
Have you done any updates / deletes on this dataset? Or is this just pure
insert / bulkload? If you haven't done any update / delete, the following
doesn't help at all, but:

Vacuuming doesn't reclaim space, it only marks it as available for re-use
for other updates. You can compact your datafiles by either a (WARNING)
VACUUM FULL or CLUSTER. Just remember, these both require a full table
lock during their execution.

--Scott Mead

> Additionally, postgres has a number of "metadata" columns (e.g., xmin,
>> xmax, cmin, cmax, &c). Those can add up, particularly when their net size
>> is greater than the user data size of a row.
>>
>
> That's a fair point.
>
> 611MB (actual disk size) - 138MB (summed data size) = 483MB
> 11M rows (ish).
>
> That's nearly 48 bytes extra per row.
>
> http://www.postgresql.org/docs/9.2/static/storage-page-layout.html gives
> detail...
>
> Let's say around 249MB (23 bytes per row, according to that page) for the
> columns you mention, so that leaves 234MB unexplained.
>
> I can see 44 bytes per page header (given 5 columns, so 20 bytes
> ItemIdData, with 24 bytes PageHeaderData). Given page size of 8kb that
> would imply page headers of about 3.3MB over a 611MB table.
>
> Even with some bytes for alignment I'm Still not seeing where 230MB (more
> than the space taken by the data itself) of my disk is gone.
>
> Thanks!
>
> Geoff
>