On Sun, Jan 22, 2017 at 3:27 AM, Stephen Frost <sfr...@snowman.net> wrote: > All, > > * Simon Riggs (si...@2ndquadrant.com) wrote: >> On 12 August 2016 at 01:01, Tom Lane <t...@sss.pgh.pa.us> wrote: >> > Michael Paquier <michael.paqu...@gmail.com> writes: >> >> In short, autovacuum will need to scan by itself the VM of each >> >> relation and decide based on that. >> > >> > That seems like a worthwhile approach to pursue. The VM is supposed to be >> > small, and if you're worried it isn't, you could sample a few pages of it. >> > I do not think any of the ideas proposed so far for tracking the >> > visibility percentage on-the-fly are very tenable. >> >> Sounds good, but we can't scan the VM for every table, every minute. >> We need to record something that will tell us how many VM bits have >> been cleared, which will then allow autovac to do a simple SELECT to >> decide what needs vacuuming. >> >> Vik's proposal to keep track of the rows inserted seems like the best >> approach to this issue. > > I tend to agree with Simon on this. I'm also worried that an approach > which was based off of a metric like "% of table not all-visible" might > result in VACUUM running over and over on a table because it isn't able > to actually make any progress towards improving that percentage. We'd > have to have some kind of "cool-off" period or something. > > Tracking INSERTs and then kicking off a VACUUM based on them seems to > address that in a natural way and also seems like something that users > would generally understand as it's very similar to what we do for > UPDATEs and DELETEs. > > Tracking the INSERTs as a reason to VACUUM is also very natural when you > consider the need to update BRIN indexes. >
Another possible advantage of tracking INSERTs is for hash indexes where after split we need to remove tuples from buckets that underwent split recently. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
