Re: autovacuum blocks the operations of other manual vacuum

Thanks for your information. I am using postgresql 8.4 and this
version should have already supported HOT. The frequently updated
columns are not indexed columns. So, the frequent updates should not
create many dead records. I also did a small test. If I don't execute
vacuum, the number of pages of the small table does not increase.

However, analyzing the big table still bothers me. According current
results, if the analyze operation is triggered, vacuum or HOT would
not function as I expect.

On Sat, Nov 20, 2010 at 12:43 PM, <tv(at)fuzzy(dot)cz> wrote:
>> Excerpts from kuopo's message of jue nov 18 04:10:36 -0300 2010:
>>> However, when I analyze the table A, the autovacuum or vacuum on the
>>> table B cannot find any removable row version (the number of
>>> nonremoveable row versions and pages keeps increasing). After the
>>> analysis finishes, the search operations on the table B is still
>>> inefficient. If I call full vacuum right now, then I can have quick
>>> response time of the search operations on the table B again.
>
> Hi, I don't know how to fix the long VACUUM/ANALYZE, but have you tried to
> minimize the growth using HOT?
>
> HOT means that if you update only columns that are not indexed, and if the
> update can fit into the same page (into an update chain), this would not
> create a dead row.
>
> Are there any indexes on the small table? How large is it? You've
> mentioned there are about 2049 rows - that might be just a few pages so
> the indexes would not be very efficient anyway.
>
> Try to remove the indexes, and maybe create the table with a smaller
> fillfactor (so that there is more space for the updates).
>
> That should be much more efficient and the table should not grow.
>
> You can see if HOT works through pg_stat_all_tables view (columns
> n_tup_upd and n_tup_hot_upd).
>
> regards
> Tomas
>
>