Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

Dimitri wrote:
> I understand well that it's respecting the standard and so on, but the
> background problem that you may see your table bloated just because
> there is a long running transaction appeared in another database, and
> if it's maintained/used/etc by another team - the problem very quickly
> may become human rather technical :-))
>

The way VACUUM and autovacuum work by default, it's OK to expect just
over 20% of the database rows to be bloat from dead rows. On some
systems that much overhead is still too much, but on others the system
continues to operate just fine with that quantity of bloat. It's not
unreasonable, and is recoverable once the long running transaction finishes.

If your application has a component to it that allows a transaction to
run for so long that more than 20% of a table can be dead before it
completes, you have a technical problem. The technical solution may not
be simple or obvious, but you need to find one--not say "the person
shouldn't have done that". Users should never have gotten an API
exposed to them where it's possible for them to screw things up that
badly. The usual first round of refactoring here is to figuring out how
to break transactions into smaller chunks usefully, which tends to
improve other performance issues too, and then they don't run for so
long either.

> So, why simply don't add a FORCE option to VACUUM?.. - In this case if
> one executes "VACUUM FORCE TABLE" will be just aware about what he's
> doing and be sure no one of the active transactions will be ever
> access this table.
>

See above. If you've gotten into this situation, you do not need a
better hammer to smack the part of the server that is stuck. One would
be almost impossible to build, and have all sorts of side effects it's
complicated to explain. It's far simpler to just avoid to known and
common design patterns that lead to this class of problem in the first
place. This is a database application coding problem, not really a
database internals one.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us