Re: truncate a table instead of vaccum full when count(*) is 0
От | Pomarede Nicolas |
---|---|
Тема | Re: truncate a table instead of vaccum full when count(*) is 0 |
Дата | |
Msg-id | Pine.LNX.4.64.0705081214250.22289@localhost обсуждение исходный текст |
Ответ на | Re: truncate a table instead of vaccum full when count(*) is 0 (Heikki Linnakangas <heikki@enterprisedb.com>) |
Ответы |
Re: truncate a table instead of vaccum full when count(*)
is 0
Re: truncate a table instead of vaccum full when count(*) is 0 |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: