Re: BUG #9757: Why reclaim index deleted pages need twice vacuum

On 03/28/2014 10:15 AM, digoal(at)126(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 9757
> Logged by: digoal.zhou
> Email address: digoal(at)126(dot)com
> PostgreSQL version: 9.3.3
> Operating system: CentOS 6.4 x64
> Description:
>
> When I'm testing a index page recycling, found that the index page must be
> two vacuum can be reused.

Yep, that's how the b-tree works [1]. A deleted page cannot be
immediately reused, because there might be concurrent scans that are
just about to visit the page. So when a page is deleted, i.e unlinked
from the tree, it is stamped with the next transaction ID, and left in
place. The next vacuum checks that the transaction ID is no longer
visible to anyone, which ensures that there are no transactions running
that might've seen a reference to that page. Only after that the page
can be reused.

That's quite pessimal; in most cases the page could be reused much
earlier, because it is a very tight window for vacuum to delete a page
just when a concurrent scan has read a link to the page and is about to
follow it. But B-tree pages are usually not deleted that often that it
would matter in practice, so there has been no effort to optimize it.

In short, it's not a bug :-).

[1]
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/README#l283

- Heikki