2012/9/21 Jeff Janes
<jeff.janes@gmail.com>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.
In the “Routine Vacuuming” section of the documentation I read that:
«The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse.»
and
«In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space.»
What I wanted to say in the previous post was exactly this: vacuum will mark the space (or gap) as free, while no space will be “returned” to the OS, except if free pages are at the very end of the data file. I haven't mentioned vacuum full at all.
It seems that this also matches your explanation, correct me if I'm wrong.
