From: Pomarede Nicolas <npomarede@corp.free.fr>
To: pgsql-performance@postgresql.org
Subject: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 8 May 2007 11:43:14 +0200 (CEST)
Message-ID: <Pine.LNX.4.64.0705081125140.20675@localhost> (raw)
Hello to all,
I have a table that is used as a spool for various events. Some processes
write data into it, and another process reads the resulting rows, do some
work, and delete the rows that were just processed.
As you can see, with hundreds of thousands events a day, this table will
need being vaccumed regularly to avoid taking too much space (data and
index).
Note that processing rows is quite fast in fact, so at any time a
count(*) on this table rarely exceeds 10-20 rows.
For the indexes, a good way to bring them to a size corresponding to the
actual count(*) is to run 'reindex'.
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.
I see in the 8.3 list of coming changes that the FSM will try to re-use
pages in a better way to help truncating empty pages. Is this correct ?
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 ?
Thanks
Nicolas
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: npomarede@corp.free.fr
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <Pine.LNX.4.64.0705081125140.20675@localhost>
* 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