Re: One vacuum full is not enough.

mendola(at)bigfoot(dot)com (Gaetano Mendola) wrote:
> Hi all,
> running a 7.4.5 it happen to me with another table
> where a single vacuum full was not freeing enough pages,
> here the verbose vacuum full, as you can see only at
> the end: truncated 8504 to 621 pages.
>
> I use pg_autovacuum and it's not enough. I'll schedule
> again a nightly vacuum full.

That doesn't follow as a legitimate inference.

It is fairly well certain that what you are "suffering" from are some
long running transactions that prevent dead tuples from being vacuumed
out.

That indicates that your focus on VACUUM FULL is a focus on a red
herring.

You can see that pretty easily; you're seeing VACUUM FULL requests not
"doing the trick" because the old transaction prevents _ANY_ kind of
vacuum from clearing out tuples that were 'killed' after that
transaction started.

The problem isn't particularly with your vacuum policy; it is with the
transaction handling behaviour in your application. No vacuum policy
will ever really be "enough" until you can get the long running
transactions under control.
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://linuxdatabases.info/info/lsf.html
"I've run DOOM more in the last few days than I have the last few
months. I just love debugging ;-)" -- Linus Torvalds