From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Pomarede Nicolas <npomarede@corp.free.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 08 May 2007 11:07:38 +0100
Message-ID: <46404BEA.5060009@enterprisedb.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0705081125140.20675@localhost>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
Pomarede Nicolas wrote:
> But for the data (dead rows), even running a vacuum analyze every day is
> not enough, and doesn't truncate some empty pages at the end, so the
> data size remains in the order of 200-300 MB, when only a few effective
> rows are there.
For a table like that you should run VACUUM much more often than once a
day. Turn on autovacuum, or set up a cron script etc. to run it every 15
minutes or so.
> Running a vacuum full is a solution for now, but it locks the table for
> too long (10 minutes or so), which is not acceptable in that case, since
> events should be processed in less that 10 seconds.
>
> So, I would like to truncate the table when the number of rows reaches 0
> (just after the table was processed, and just before some new rows are
> added).
>
> Is there an easy way to do this under psql ? For example, lock the
> table, do a count(*), if result is 0 row then truncate the table, unlock
> the table (a kind of atomic 'truncate table if count(*) == 0').
>
> Would this work and what would be the steps ?
It should work, just like you describe it, with the caveat that TRUNCATE
will remove any old row versions that might still be visible to an older
transaction running in serializable mode. It sounds like it's not a
problem in your scenario, but it's hard to say for sure without seeing
the application. Running vacuum more often is probably a simpler and
better solution, anyway.
Which version of PostgreSQL is this?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, npomarede@corp.free.fr
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <46404BEA.5060009@enterprisedb.com>
* 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