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

From: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
To: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: use AV worker items infrastructure for GIN pending list's cleanup
Date: 2021-09-08 14:07:11
Message-ID: 20210908140711.GA4599@ahch-to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2021-09-08 14:15:24 Re: Postgres perl module namespace
Previous Message Pavel Stehule 2021-09-08 14:02:36 Re: On login trigger: take three