On 1/9/14, 12:54 PM, Tom Lane 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.
There is a reasonable argument for forcing UPDATE queries to do it anyway,
to improve the odds they can do same-page updates (whether HOT or
otherwise). And probably an INSERT should do it on a page that it's
selected as an insertion target. But I think the argument that the
original do-maintenance-in-background-whenever-possible design was wrong
is a lot harder to sustain for SELECT or even DELETE queries. As I said
upthread, I think the current behavior was *not* chosen for performance
reasons but just to limit the scope of what we had to change for HOT.
Instead of looking at how to avoid this work in SELECTs maybe it'd be more
useful to look at how we can get it done more quickly in the background. The
VSM is already a step in the right direction, but it seems the big use case
here is when some bulk operation comes through and touches a sizeable number of
blocks (but perhaps not enough to hit autovac thresholds).
ISTM it wouldn't be too difficult for a backend to track how many blocks in a
relation it's dirtied (keep in mind that count doesn't have to be perfect). If
we tracked that info, it could be put into a maintenance queue (LISTEN/NOTIFY?)
along with our XID. That gives us a list of relations to vacuum and exactly
when to vacuum them. Thanks to the VSM we wouldn't need to track individual
pages (though it might be useful to track the minimum and maximum block IDs we
hit, per relation).
--
Jim C. Nasby, Data Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
