Re: Turning off HOT/Cleanup sometimes

On Wed, Apr 22, 2015 at 5:17 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Apr 22, 2015 at 06:07:00PM -0300, Alvaro Herrera wrote:
>> > Good point, but doesn't vacuum remove the need for pruning as it removes
>> > all the old rows?
>>
>> Sure. The point, I think, is to make autovacuum runs of some sort that
>> don't actually vacuum but only do HOT-pruning. Maybe this is a
>> reasonable solution to the problem that queries don't prune anymore
>> after Simon's patch. If we made autovac HOT-prune periodically, we
>> could have read-only queries prune only already-dirty pages. Of course,
>> that would need further adjustments to default number of autovac
>> workers, I/O allocation, etc.
>
> Do we really want to make vacuum more complex for this? vacuum does
> have the delay settings we would need though.

I think it's abundantly clear that, as wonderful as autovacuum is
compared with what we had before autovacuum, it's not good enough.
This is one area where I think improvement is definitely needed, and
I've suggested it before. Discussion began here:

http://www.postgresql.org/message-id/AANLkTimd3ieGCm9pXV39ci6-owy3rX0mzz_N1tL=0ZLm@mail.gmail.com

Some of the things I suggested then seem dumb in hindsight, but I
think the basic concept is still valid: if we scan the heap and find
only a few dead tuples, the expense of scanning all of the indexes may
not be justified. Also, the fact that a relation can currently only
be vacuumed by one process at a time is coming to seem like a major
limitation. Some users are partitioning tables just so that each
partition can be autovac'd separately. That really shouldn't be
required.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company