On Sun, Sep 8, 2013 at 12:47:35AM +0200, Andres Freund wrote: > Hi, > > On 2013-09-07 12:50:59 -0400, Bruce Momjian wrote: > > That seems very complicated. I think it would be enough to record the > > current xid at the time of the vacuum, and when testing for later > > vacuums, if that saved xid is earlier than the RecentGlobalXmin, and > > there have been no inserts/updates/deletes, we know that all of > > the pages can now be marked as allvisible. > > But that would constantly trigger vacuums, or am I missing something? Or > what are you suggesting this xid to be used for?
OK, let me give some specifices. Let's suppose we run a vacuum, and at the time the current xid counter is 200. If we later have autovacuum check if it should vacuum, and there have been no dead rows generated (no update/delete/abort), if the current RecentGlobalXmin is >200, then we know that all the transactions that prevented all-visible marking the last time we ran vacuum has completed. That leaves us with just inserts that could prevent all-visible. If there have been no inserts, we can assume that we can vacuum just the non-all-visible pages, and even if there are only 10, it just means we have to read 10 8k blocks, not the entire table, because the all-visible is set for all the rest of the pages. Now, if there have been inserts, there are a few cases. If the inserts happened in pages that were previously marked all-visible, then we now have pages that lost all-visible, and we probably don't want to vacuum those. Of course, we will not have recorded which pages changed, but any decrease in the all-visible table count perhaps should have us avoiding vacuum just to set the visibility map. We should probably update our stored vm bit-set count and current xid value so we can check again later to see if things have sabilized. If the vm-set bit count is the same as the last time autovacuum checked the table, then the inserts happened either in the vm-bit cleared pages, or in new data pages. If the table size is the same, the inserts happened in existing pages, so we probably don't want to vacuum. If the table size has increased, some inserts went into new pages, so we might want to vacuum, but I am unclear how many new pages should force a vacuum. -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
