On Thu, Jan 9, 2014 at 1:54 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Stephen Frost <sfr...@snowman.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers