On Sun, Jan 22, 2017 at 4:45 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Amit, > > * Amit Kapila (amit.kapil...@gmail.com) wrote: > > On Sun, Jan 22, 2017 at 3:27 AM, Stephen Frost <sfr...@snowman.net> > wrote: > > > * 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. > > That's a good point also, and for clearing GIN pending lists too, now > that I think about it. > As much as I want my patch to go in, this is not an argument for it. The GIN pending lists will be handled by autoanalyze. > We really need to get this fixed for PG10. > I'm not sure what more I'm supposed to be doing, if anything. -- Vik Fearing +33 6 46 75 15 36http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
