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
