Hi all! I recently started using pg_autovacuum instead of scheduling vacuum analyzes in a system that needs to be taken to production soon.
However, I have noticed something funny that happens while using this.
sometimes, some database operations take an unreasonably long time to finish. I have not been able to pin the problem down to any specific cause and the problem is also not reproducible( atleast I don't know how to make sure it happens again ) but what happens is that some queries take an unreasonably long time to finish.
pg_stat_actiivity showed some selects running on a table with ~90k rows at one such time of weird behavior(they ran for a long long time).
Is it possible that pg_autovacuum is auto vacuuming that same table at that time and there is some unnecessary waiting involved because the table is locked?
A simple vacuum shouldn't lock tables, although a vacuum full will. It could be that on a busy system, the vacuum is pushing disk activity to the limit. You can monitor overall activity with "vmstat 1" from the command-line in Linux.
One solution might be to vacuum more often. Although a little counter-intuitive, this means each run does less work.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
