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

On Mon, Apr 5, 2021 at 3:31 PM Jaime Casanova
<jcasanov@systemguards.com.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.

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/