On Tue, Feb 5, 2013 at 3:33 PM, Geoff Winkless <pgsqladmin@xxxxxxxx> wrote:
Thanks for all the responses....On 5 February 2013 19:36, Rosser Schwarz <rosser.schwarz@xxxxxxxxx> wrote:On Tue, Feb 5, 2013 at 11:00 AM, Scott Mead <scottm@xxxxxxxxxxx> 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) = 483MB11M rows (ish).
That's nearly 48 bytes extra per row.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
