On Mon, 4 Dec 2023 at 15:08, senor <frio_cervesa@xxxxxxxxxxx> wrote: > PG version 11.4 You're missing out on over 3 years of bug fixes running that version. I see you're planning an upgrade to v15. You should update to 11.22 in the meantime. That's the latest and final version of v11. > 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? The transaction ID thresholds are decided at the start of vacuum, so tuples that become dead after vacuum starts won't be touched. > Is there any reason to think about this differently when the vacuum is manual as opposed to auto? Not in terms of what will be removed, but there are performance considerations. vacuum_cost_delay is 0 but default but autovacuum_vacuum_cost_delay isn't. I believe it's 20ms for v11. That means the default settings allow manual VACUUMs to run quickly, but autovacuums are throttled to be quite slow. > 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. Perhaps you could set autovacuum_analyze_scale_factor to something higher for the tables in question use pg_stat_all_tables.n_mod_since_analyze and do a manual VACUUM ANALYZE. If autovacuum were ever to ANALYZE the tables in question then this would cause issues. > 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. If you find all the autovacuum workers busy most of the time, then you'll likely want to reduce autovacuum_vacuum_cost_delay to increase the speed of the auto-vacuum workers. David
