Thanks for the reply! I tried the analysis on our much shorter staging table and it did change the plan. I haven’t tried it on the production ones because my understanding is that the autovacuum process is gentler with resource consumption and I didn’t want to gum things up in the meantime. But that may be false or avoidable.
Unfortunately the insertions are not batched—they arrive in a fairly steady stream. Given that, and the fact that we’re talking about the behavior of the daemon, I’m not sure how to provide a reproduction (though would be game if you had pointers). I could in theory make a job analyze these tables on a regular cadence, though I guess that feels like I’d be stepping on autovacuum’s toes.
Would be happy to read over similar reports if you could steer me toward them.
Thanks again for your help.
Lincoln Swaine-Moore
On Sat, Feb 22, 2025 at 00:37 Andrei Lepikhov <lepihov@xxxxxxxxx> wrote:
On 22/2/2025 00:46, Lincoln Swaine-Moore wrote:
> So, obviously there's a statistics problem, which led me to realize that
> actually these tables have *never* been autovacuumed/analyzed according
> to pg_stat_user_tables.
> I'm using a managed database which makes it a little tricky to debug,
> but all my settings
> (autovacuum/autovacuum_vacuum_threshold/autovacuum_analyze_threshold/
> autovacuum_vacuum_insert_threshold) are default,
> and I can see that other tables have been vacuumed recently.
I know a couple of reports related to this kind of behaviour and
different solutions to resolve it. But first, if you execute the ANALYZE
command on these problematic tables, does it fix your issue? May you
live with manual vacuum analysis each time after batch insertion?
If not, may you provide a synthetic reproduction of the case?
--
regards, Andrei Lepikhov
