Re: Why do I have holes in my pages?

Take a look at this part of the documentation:
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

The “missing” entries belong to the tuples that you have DELETEd/UPDATEd
and that are no longer visible
to your current session, but still might be for the others, that started
some time ago. When tuples are no longer
needed, VACUUM will “release” the slots by adding them into the
FreeSpaceMap.

Still, if you have “empty” slots in the middle of your datafiles, VACUUM
cannot resize files.
This leads to the fact that while database size is being not so big, actual
disk space occupied by it
is bigger. This is called “bloat”.

Check the output of the query here:
http://wiki.postgresql.org/wiki/Show_database_bloat

Also, having such a big difference in the reported and actual size of the
database, may I ask:
- when was the last time you performed VACUUM?
- don't you have autovacuum = on (which is default) in your configuration?

2012/9/20 Aleksey Tsalolikhin <atsaloli(dot)tech(at)gmail(dot)com>

> On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
> wrote:
> > In response to Aleksey Tsalolikhin <atsaloli(dot)tech(at)gmail(dot)com>:
> >>
> >> Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB
> filesystem).
> >
> > I expect that the first thing that others are going to ask
> > is "what is telling you that your DB is 400G?"
>
>
> Right on. I got that out of my pgstatspack report.
>
> \l+ in psql tells me the same thing - 400 GB

--
Victor Y. Yegorov