From: Pomarede Nicolas <npomarede@corp.free.fr>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
Date: Tue, 8 May 2007 12:23:56 +0200 (CEST)
Message-ID: <Pine.LNX.4.64.0705081214250.22289@localhost> (raw)
In-Reply-To: <46404BEA.5060009@enterprisedb.com>
References: <Pine.LNX.4.64.0705081125140.20675@localhost>
<46404BEA.5060009@enterprisedb.com>
On Tue, 8 May 2007, Heikki Linnakangas wrote:
> 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.
Yes, I already do this on another spool table ; I run a vacuum after
processing it, but I wondered if there was another way to keep the disk
size low for this table.
As for autovacuum, the threshold values to analyze/vacuum are not adapted
to my situation, because I have some big tables that I prefer to keep
vacuumed frequently to prevent growing in disk size, even if the number of
insert/update is not big enough and in my case autovacuum would not run
often enough. Instead of configuring autovacuum on a per table basis, I
prefer running a vacuum on the database every day.
>
>> 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?
Shouldn't locking the table prevent this ? I mean, if I try to get an
exclusive lock on the table, shouldn't I get one only when there's no
older transaction, and in that case I can truncate the table safely,
knowing that no one is accessing it due to the lock ?
the pg version is 8.1.2 (not the latest I know, but migrating this base is
quite complicated since it needs to be up 24/24 a day)
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, heikki@enterprisedb.com
Subject: Re: truncate a table instead of vaccum full when count(*) is 0
In-Reply-To: <Pine.LNX.4.64.0705081214250.22289@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