Re: Turning off HOT/Cleanup sometimes

On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> VACUUM cleans up blocks, which is nice because it happens offline in a
> lazy manner.
>
> We also make SELECT clean up blocks as it goes. That is useful in OLTP
> workloads, but it means that large SQL queries and pg_dump effectively
> do much the same work as VACUUM, generating huge amounts of I/O and
> WAL on the master, the cost and annoyance of which is experienced
> directly by the user. That is avoided on standbys.

On a pgbench workload, though, essentially all page cleanup happens as
a result of HOT cleanups, like >99.9%. It might be OK to have that
happen for write operations, but it would be a performance disaster if
updates didn't try to HOT-prune. Our usual argument for doing HOT
pruning even on SELECT cleanups is that not doing so pessimizes
repeated scans, but there are clearly cases that end up worse off as a
result of that decision.

I'm not entirely wild about adding a parameter in this area because it
seems that we're increasingly choosing to further expose what arguably
ought to be internal implementation details. The recent wal_log_hints
parameter is another recent example of this that I'm not thrilled
with, but in that case, as in this one, I can see the value of it.
Still, I think it'd be loads better to restrict what you're talking
about here to the SELECT-only case; I have a strong feeling that this
will be a disaster on write workloads.

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