On Sat, 22 Jan 2005 12:13:00 +0900 (JST), Tatsuo Ishii <[EMAIL PROTECTED]> wrote: > IMO the bottle neck is not WAL but table/index bloat. Lots of updates > on large tables will produce lots of dead tuples. Problem is, There' > is no effective way to reuse these dead tuples since VACUUM on huge > tables takes longer time. 8.0 adds new vacuum delay > paramters. Unfortunately this does not help. It just make the > execution time of VACUUM longer, that means more and more dead tuples > are being made while updating. > > Probably VACUUM works well for small to medium size tables, but not > for huge ones. I'm considering about to implement "on the spot > salvaging dead tuples".
Quick thought -- would it be to possible to implement a 'partial VACUUM' per analogiam to partial indexes? It would be then posiible to do: VACUUM footable WHERE footime < current_date - 60; after a statement to DELETE all/some rows older than 60 days. The VACUUM would check visibility of columns which are mentioned in an index (in this case: footable_footime_index ;)). Of course it is not a great solution, but could be great for doing housecleaning after large update/delete in a known range. ...and should be relatively simple to implement, I guess (maybe without 'ANALYZE' part). Regards, Dawid ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match