Re: Berserk Autovacuum (let's save next Mandrill)

On Tue, Jul 23, 2019 at 1:53 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
>
> To invoke autovacuum even on insert-only tables we would need check
> the number of inserted tuples since last vacuum. I think we can keep
> track of the number of inserted tuples since last vacuum to the stats
> collector and add the threshold to invoke vacuum with INDEX_CLEANUP =
> false. If an autovacuum worker confirms that the number of inserted
> tuples exceeds the threshold it invokes vacuum with INDEX_CLEANUP =
> false. However if the number of dead tuples also exceeds the
> autovacuum thresholds (autovacuum_vacuum_threshold and
> autovacuum_vacuum_scale_factor) it should invoke vacuum with
> INDEX_CLEANUP = true. Therefore new threshold makes sense only when
> it's lower than the autovacuum thresholds.
>
> I guess we can have one new GUC parameter to control scale factor.
> Since only relatively large tables will require this feature we might
> not need the threshold based the number of tuples.
>

Generally speaking, having more guc's for autovacuum and that too
which are in some way dependent on existing guc's sounds bit scary,
but OTOH whatever you wrote makes sense and can help the scenarios
which this thread is trying to deal with. Have you given any thought
to what Alvaro mentioned up-thread "certain tables would have some
sort of partial scan that sets the visibility map. There's no reason
to invoke the whole vacuuming machinery. I don't think this is
limited to append-only tables, but
rather those are just the ones that are affected the most."?

This thread seems to be stalled for the reason that we don't have a
clear consensus on what is the right solution for the problem being
discussed. Alvaro, anyone has any thoughts on how we can move forward
with this work?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com