Re: how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> Or what I usually do in a case like this is clone the database to a
>>> test/QA server then run pg_upgrade to get that running on 9.5, then hope
>>> what I learn transfers back to production.
>>
>>
I'll save this great idea.

> But the symptoms you describe are exactly what I expect from these clean
> up problems, so I would just assume that that is the problem.
>
> The easiest solution is to turn of fastupdate for that index. Each update
> will then be individually slower, but you won't have the periodic lock up
> you currently do.
>

That would be fine and we will try this.

> Vacuum is overkill (and can be extremely slow to run a large gin index),
> you just need to get it to autoanalyze by changing the per-table setting of
> "autovacuum_vacuum_scale_factor" to zero and instead using
>

Did you mean autovacuum_analyze_scale_factor or does it not matter?

I'm trying to force an autovacuum/autoanalyze this way but unfortunately
for me I have autovacuum_max_workers at the default of 3 and there are
apparently many tables in line for autovacuuming in front of the table I
want :-(. I'm playing whack-a-mole killing them and hoping the table I want
will come up.

Note that a manual ANALYZE will *not* clear the pending list, it has to be
> an autoanalyze.
>

This is a brain bender, I didn't know there were differences, and this eats
away a little bit at my confidence in understand things, but I'll just
accept it for now.

> (Manual VACUUM will clear the pending list, but you might have trouble
> getting manual VACUUM to complete fast enough)
>

You are exactly right the manual VACUUM is taking forever.

>