On Wed, Jan 8, 2014 at 3:33 AM, Simon Riggs <si...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
