Re: [GENERAL] Odd VACUUM behavior when it is expected to truncate last empty pages

(moving this to hackers since I suspect we got an issue to fix here)

On Wed, Aug 3, 2011 at 6:35 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:
> Hi all,
>
> I have PostgreSQL 9.0.3 installed on my Gentoo Linux box. The
> configuration is default. There is no any activity in the database but
> the described below.
>
> What I am trying to achieve is the effect described in this article
> http://blog.endpoint.com/2010/09/reducing-bloat-without-locking.html.
> In short I am updating last pages of a table to move the tuples to the
> earlier pages to make VACUUM able to truncate the empty tail. However
> I faced a strange VACUUM behavior. So the situation is:
>
> 1. do some UPDATEs on the table so it has several last pages free,
> 2. perform VACUUM of this table the 1st time, no tail pages will be
> truncated (why?),
> 3. perform VACUUM the 2nd time straight after the 1st one and it will
> truncate the tail pages (why this time?).
>

There is a check to truncate only if minimum 1000 or relpages/16 pages
(compile time constants) can be truncated and attempt truncation only
if thats true, otherwise we assume that the cost of attempting
truncation is not worth the cost of pages salvaged. With that logic,
we should have never truncated just 3 pages in a relation with 3000+
pages. But I can see that happening because of visibility maps. So if
we stop scanning beyond a certain percentage of pages, we might be
fooled into believing that the rest of the pages can possibly be
truncated and then do a hard check to find that out.

The first vacuum probably scans even the last few pages because you
just updated the tuples of those pages and then updates the visibility
map for those pages. The second vacuum then stops much before because
the visibility map tells us that all remaining pages are visible (and
thus set nonempty_pages to a lower number) and so the check I
mentioned earlier succeeds and we attempt the truncation. Now, this is
just a theory and a reproducible case will help to confirm this.

The only problem, other than a surprising behavior that you noted,
that I see with this approach is that we might repeatedly try to
truncate a relation which in fact does not have anything to truncate.
The worst thing is we might unnecessarily take an exclusive lock on
the table.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB     http://www.enterprisedb.com