On Mon, 4 Dec 2023 at 15:08, senor <frio_cervesa@hotmail.com> 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,
whenvacuum completes, should I be thinking about the state of the table and indexes as being vacuumed up to 1 million?
Oris 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
triggervacuum 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
keepperformance high for those. Secondly, there's a ton of memory allotted so running vacuum freeze before table pages
ageout 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
buthave put off looking into that because updating to PG15 was "right around the corner". Meanwhile, I have had to run
multiple-threadscripts to keep up vacuum. Manual vacuums always work and updates stats correctly but autovacuums hang
andoccupies 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