From: Jesper Krogh <jesper@krogh.cc>
To: pgsql-performance@postgresql.org
Subject: Re: Very long deletion time on a 200 GB database
Date: Thu, 23 Feb 2012 20:46:11 +0100
Message-ID: <4F469783.7030201@krogh.cc> (raw)
In-Reply-To: <4F45FB55.1020600@lerner.co.il>
References: <4F45FB55.1020600@lerner.co.il>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jesper@krogh.cc
Subject: Re: Very long deletion time on a 200 GB database
In-Reply-To: <4F469783.7030201@krogh.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox