Re: diskspace - Mailing list pgsql-admin

From Geoff Winkless
Subject Re: diskspace
Date
Msg-id CAEzk6fdxpe6hipOwSvVeFbkPhQdQ0WRrUJm+NJ21dNa5nK6_wA@mail.gmail.com
Whole thread Raw
In response to Re: diskspace  (Geoff Winkless <pgsqladmin@geoff.dj>)
Responses Re: diskspace  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-admin
On 5 February 2013 20:33, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

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.

Of course I got that slightly wrong: ItemIdData is for each row, not for each column; an extra 4 bytes for each row makes the per-row space 290MB, leaving 167MB unexplained.

In answer to other questions: the data was inserted in bulk, so there have been no updates; even so I have run a VACUUM FULL just in case and it makes no difference.

I'm assuming the remaining 167MB is related to the alignment requirements, although that does seem quite a lot at 15 bytes per row (perhaps I'm just unfortunate with the data sizes resulting in poor alignment).

I guess the answer to my question is that there is no answer to my question; pg just does use a massive (especially in relation to thin but tall tables) proportion of diskspace for its own purposes. 

Thanks again for all the responses.

Geoff

pgsql-admin by date:

Previous
From: Rosser Schwarz
Date:
Subject: Re: diskspace
Next
From: Albe Laurenz
Date:
Subject: Re: diskspace