Skip site navigation (1) Skip section navigation (2)

Odd VACUUM behavior when it is expected to truncate last empty pages

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Odd VACUUM behavior when it is expected to truncate last empty pages
Date: 2011-08-03 10:35:43
Message-ID: CAL_0b1sQytEusUSnY3Wv=Wh4MePof15TTA0KDKyuNaX_9YRmAA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
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?).

The case is stable.

Could anybody explain why it could happen, please?

Here is the VACUUMs verbose http://pastebin.com/RjuDmabV

-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2011-08-03 10:40:42
Subject: Re: Further news on Clang - spurious warnings
Previous:From: Peter GeogheganDate: 2011-08-03 10:05:52
Subject: Re: Further news on Clang - spurious warnings

pgsql-general by date

Next:From: Andy ColsonDate: 2011-08-03 14:03:05
Subject: Re: Vacuum as "easily obtained" locks
Previous:From: Michael GrahamDate: 2011-08-03 09:47:24
Subject: Vacuum as "easily obtained" locks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group