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:
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.tech@xxxxxxxxx>
On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:Right on. I got that out of my pgstatspack report.
> In response to Aleksey Tsalolikhin <atsaloli.tech@xxxxxxxxx>:
>>
>> 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?"
\l+ in psql tells me the same thing - 400 GB
Victor Y. Yegorov
