Re: vacuum visibility relevance - Mailing list pgsql-general

On Mon, 2023-12-04 at 02:08 +0000, senor wrote:
> Simplified Scenario:
> 40+ gig table with 20+ indexes receiving log event type records in an append only fashion.

Log tables typically receive tons of INSERTs.  Over twenty indexes are too many.

> 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?

VACUUM processes the whole table, but it determines an "xmin horizon" when it starts,
that is, it determines which tuples are safe to remove.  Rows added after VACUUM started
won't qualify, so they won't be removed.

> Is there any reason to think about this differently when the vacuum is manual as opposed to auto?

No, the behavior is the same.

> 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.

You can monitor the "n_tup_ins" column in "pg_stat_user_tables" and run VACUUM whebever
it has increased enough.

But the simple solution is to upgrade.  Any version older than v13 is due for an upgrade soon.

> 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.

Autovacuum deliberately runs slower than a manual VACUUM.
If you are running a version older than v12, the default value for
"autovacuum_vacuum_cost_delay" is 20ms, which makes autovacuum really slow.  You should
change that parameter to 2ms, and if that is still too slow, use ALTER TABLE to set it
to 0 on the affected table.  Then autovacuum will run as fast as manual VACUUM.

In addition, see that "maintenance_work_mem" is set high to make autovacuum fast.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com