Message-ID: <4F469783.7030201@krogh.cc>
Date: Thu, 23 Feb 2012 20:46:11 +0100
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
References: <4F45FB55.1020600@lerner.co.il>
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 23/02/12 09:39, Reuven M. Lerner wrote:
> Hi, everyone.  I'm maintaining an application that exists as a "black 
> box" in manufacturing plants.  The system is based on Windows, .NET, 
> and PostgreSQL 8.3.  I'm a Unix kind of guy myself, but the 
> application layer and system administration are being handled by other 
> people; I'm just the PostgreSQL guy.

Just thinking loud. It looks like (just guessing)
that the application needs store data worth 1 month back and
it was put into production under the assumption that it would
never fill up or deletion easily could be done under maintaince
windows. And that now turns out not to be the case.

I would stuff in a  trigger function on the table that automatically
does the cleanup.. It could be a BEFORE INSERT OR UPDATE
TRIGGER that just tries to prune 2-3 rows of the table if they
have exceeded the keep-back time. Just installing that in the
maintance window would allow the system to self-heal over time.

If the maintaince window allows for more cleanup, then manually
do some deletions. Now the black-box is self-healing.

-- 
Jesper