Re: use AV worker items infrastructure for GIN pending list's cleanup

On Mon, May 17, 2021 at 01:46:37PM +0900, Masahiko Sawada wrote:
> On Mon, Apr 5, 2021 at 3:31 PM Jaime Casanova
> <jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> >
> > Hi,
> >
> > When AV worker items where introduced 4 years ago, i was suggested that
> > it could be used for other things like cleaning the pending list of GIN
> > index when it reaches gin_pending_list_limit instead of making user
> > visible operation pay the price.
> >
> > That never happened though. So, here is a little patch for that.
>
> Thank you for working on this.
>
> I like the idea of cleaning the GIN pending list using by autovacuum
> work item. But with the patch, we request and skip the pending list
> cleanup if the pending list size exceeds gin_pending_list_limit during
> insertion. But autovacuum work items are executed after an autovacuum
> runs. So if many insertions happen before executing the autovacuum
> work item, we will end up greatly exceeding the threshold
> (gin_pending_list_limit) and registering the same work item again and
> again. Maybe we need something like a soft limit and a hard limit?
> That is, if the pending list size exceeds the soft limit, we request
> the work item. OTOH, if it exceeds the hard limit
> (gin_pending_list_limit) we cleanup the pending list before insertion.
> We might also need to have autovacuum work items ignore the work item
> if the same work item with the same arguments is already registered.
> In addition to that, I think we should avoid the work item for
> cleaning the pending list from being executed if an autovacuum runs on
> the gin index before executing the work item.
>

Thanks for your comments on this. I have been working on a rebased
version, but ENOTIME right now.

Will mark this one as "Returned with feedback" and resubmit for
november.

--
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL