On Tue, Feb 5, 2013 at 3:33 PM, Geoff Winkless <pgsqlad...@geoff.dj> wrote:
> Thanks for all the responses.... > > On 5 February 2013 19:36, Rosser Schwarz <rosser.schw...@gmail.com> wrote: > >> On Tue, Feb 5, 2013 at 11:00 AM, Scott Mead <sco...@openscg.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 >
