Re: Why do I have holes in my pages?

On Thu, Sep 20, 2012 at 1:46 PM, Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:
> 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.

My understanding is (which is not very deep on this topic) is that it
is a lot more complicated than that.

To start with, it can be as you say where the ctid and its tuple are
interesting to someone, but not to you. But eventually the tuple is
not interesting to anyone, and its space can be reused. But the ctid
is still needed (to inform stragglers that it's corresponding tuple is
not interesting, and in fact no longer exists, so move on, nothing to
see). Then eventually even the ctid itself is not needed anymore even
for that purpose.

At that point the ctid can be re-used, but only if someone actually
wants a "new" ctid on that page. An ordinary vacuum will not close up
the gaps on un-used ctids. Only a vaccum full will do that.

The space used by these ctid gaps is not large, and as the OP
discovered, his wasted space was in fact happening outside of the
database itself.

Cheers,

Jeff