Re: Turning off HOT/Cleanup sometimes

On Thu, Jan 9, 2014 at 1:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> That said, I'm not entirely convinced that traversing these dead tuples
>> is all *that* painful during SELECT. If there's that many levels then
>> hopefully it's not long til an UPDATE comes along and cleans them up.
>
> There's always VACUUM ;-)
>
> If you take about ten steps back, what's happening here is that
> maintenance work that we'd originally delegated to VACUUM, precisely so
> that it wouldn't have to be done by foreground queries, is now being done
> by foreground queries. And oddly enough, people don't like that.

People *think* they don't like that, because that's the way it works
right now. If it worked some other way, there's a good chance people
would be complaining about that behavior, too. I submitted a patch a
few years back to limit the setting of hint bits by foreground
processes to approximately 5% of the buffers they touched in a large
scan, so that no single scan would incur all the cost of setting the
hint bits; instead, the cost would be amortized over the first 20 or
so scans. However, nobody was very enthusiastic about that patch,
because while it greatly softened the blow for the first scan,
subsequent scans were slower, because now they had to carry part of
the burden, too. And you know what? People didn't like *that*
either.

The problem with saying that we should let VACUUM do this work is the
same as the problem with saying that if you're late for your Concorde
flight, you should go running across the tarmac and try to catch it.
The cost of dead tuples is related in a linear fashion to the rate at
which pages are accessed. Not coincidentally, the number of
opportunities for HOT pruning is *also* related in a linear fashion to
the rate at which pages are accessed. This is why it works so well.
The rate at which vacuuming happens does not ramp up in the same way;
it's limited by autovacuum cost settings (which people tend not have
set correctly, and don't adjust themselves on the fly) or by their
hardware capabilities. If autovacuum can't keep up, foreground
activity doesn't slow down to compensate; instead, the system just
bloats out of control. While people may not like having this
maintenance activity in the foreground, they like not having it at all
even less.

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