On Sun, Dec 3, 2023 at 9:08 PM senor <frio_cerv...@hotmail.com> wrote:
> Hi All, > > Simplified Scenario: > 40+ gig table with 20+ indexes receiving log event type records in an > append only fashion. > One of the columns is a sequence ID. > PG version 11.4 > > If I start a vacuum on this table when sequence ID is 1 million, and the > table continues to accumulate new events, when vacuum completes, should I > be thinking about the state of the table and indexes as being vacuumed up > to 1 million? Or is vacuum also operating on records inserted after vacuum > started? > > Is there any reason to think about this differently when the vacuum is > manual as opposed to auto? > > I attempted to deal with the append only issue corrected in PG13 by > setting autovacuum_freeze_max_age low enough to trigger vacuum but that has > been such a wild card I abandoned it. I'm now trying to find a formula to > trigger a manual vacuum. There are stats gathering processes pulling from > the most recently added data all the time so my priority is to keep > performance high for those. Secondly, there's a ton of memory allotted so > running vacuum freeze before table pages age out of memory seems like a > good idea. > > I am having trouble with autovacuum keeping up and I suspect there's a > communication problem with the stats collector but have put off looking > into that because updating to PG15 was "right around the corner". > Meanwhile, I have had to run multiple-thread scripts to keep up vacuum. > Manual vacuums always work and updates stats correctly but autovacuums hang > and occupies workers doing nothing. > > I realize these are 2 completely different issues. I would love to have > better understanding of the initial question but it feels like I may not > have to be very aware of how that works if I knew what was causing the > latter issue. Any hints, pointers and references are always appreciated. > What do you have autovacuum_vacuum_scale_factor set to? The default 0.1 is almost certainly way too high.
