Re: BUG #4730: Vacuum full verbose analyze "deadlock"

On Wed, Mar 25, 2009 at 09:15:48AM -0500, Kevin Grittner wrote:
> >>> "Wayne Conrad" <wconrad(at)yagni(dot)com> wrote:
>
> > "VACUUM FULL ANALYZE VERBOSE" on a "deadlocks"
>
> > "VACUUM VERBOSE ANALYZE" (without the "FULL") does not
>
> You do realize that FULL should not be part of normal maintenance,
> right? It is sometimes useful to recover from table bloat when normal
> maintenance fails. Although it is almost always much slower than
> CLUSTER, it has the advantage of not requiring disk space for a second
> copy of the table, but it requires a REINDEX afterward to correct the
> index bloat it causes. If you are doing a good job of normal
> maintenance, you never, ever should be running VACUUM FULL.
>
> None of the above means you haven't found a problem worth looking at
> -- I'm not trying to comment on that; but unless you are in the middle
> of recovery from abnormal bloat, you may be able to dodge the problem
> by correcting your maintenance practices.
>
> -Kevin

Kevin,

We started doing a routine VACUUM FULL ANALYZE on the advice of a
friend after the database started getting slow over time. We did not
realize that the FULL should never be done. We will change that to a
VACUUM ANALYZE, without the full, and see how it goes.

We discovered that an occasionally REINDEX was also required to keep
things fast. We did not realize it was a direct result of the VACUUM
FULL that the REINDEX was required.

Thank you for your advice. We are, obviously, not DB guys--just
programmers shoved into the role. We push the buttons and pull the
levers until things appear to work, which is a poor substitute for
actually understanding what we are doing.

Best Regards,
Wayne Conrad