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

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

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Date: 2010-08-22 17:53:04
Message-ID: 4C716400.5030900@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Greg SmithDate: 2010-08-22 18:00:49
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows
Previous:From: DimitriDate: 2010-08-22 16:17:23
Subject: Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

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