Re: Vacuum problems with 9.1

On Tue, Aug 28, 2012 at 10:03 AM, Nimesh Satam <nimesh(dot)zedo(at)gmail(dot)com>wrote:

> Hi,
>
> We have been using the current version of postgres i.e. 9.1.4 with
> streaming replication on. While vacuuming we noticed that certain dead rows
> are not getting removed and following debug information is printed:
>
> "DETAIL: 12560 dead row versions cannot be removed yet."
>
> As per suggestion, we made sure that no long running transactions are
> active. Also all the applications were stopped during this time.
>
> Can anybody highlight the possible reason for the dead rows not been
> cleaned?
>
>
Are you absolutely sure that there are no other client connections open
which are actively deleting/updating records ? The above message would
usually come when certain rows which are otherwise DEAD (meaning, deleting
or updating transaction has already committed) but can't be removed just
yet because there is at least one old transaction that may still see the
tuple as visible. If there are no open transactions, then I can only think
about a concurrent auto-analyze running that can prevent some tuples from
being vacuumed.

What happens if you run the command again ? Do you get the exact same
number again ?

Also note that any concurrent transaction can cause this, even if the
transaction does not access the table under vacuum operation.

> FYI: We used the command VACUUM FULL ANALYZE VERBOSE table_name; command.
>
>
I hope you are aware that VACUUM FULL is a costly operation because it
rewrites the entire table again. You need VACUUM FULL only in cases of
severe bloat. Otherwise a plain VACUUM (or auto-vacuum) should be enough to
handle regular bloat.

Thanks,
Pavan