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.
