|
|
 | | From: | Dawid Kuroczko | | Subject: | PostgreSQL clustering VS MySQL clustering | | Date: | Sat, 22 Jan 2005 14:18:27 +0100 |
|
|
 | On Sat, 22 Jan 2005 12:13:00 +0900 (JST), Tatsuo Ishii
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
|
|
|
