Re: Automatic autovacuum to prevent wraparound - PG13.5

On Wed, 2022-06-15 at 12:13 +0100, Mauro Farracha wrote:
> Have recently upgraded from PG10 to PG13.5 and would like to understand the reason why we
> are seeing triggered autovacuum to prevent the wraparound while all the metrics are still
> far off from the multixact/freeze max ages defined. And inclusive there was one time where
> it was triggered as aggressive.
>
> Some background:
> - autovacuum_freeze_max_age: 400M
> - autovacuum_multixact_freeze_max_age: 800M
> - the activity is mostly insert intensive in one particular table (60M daily)
> - the team execute vacuum freeze verbose every day at night to keep the multixact ids down
> - we generally reach near 70M mxids before running vacuum freeze at night
> - the postgresql is Aurora
>
> The scenario:
> - Out of nowhere (during the weekend), without database activity load or batches running,
> with previous nightly run of vacuum freeze, in the middle of the day, with xids and mxids
>  below 20M we are seeing autovacuum being triggered to prevent wraparound.
>
> My question is why this is occurring, which condition might be responsible for this behaviour?

A long-running transaction or a prepared transaction.
Or an abandoned replication slot with an old "xmin".

That would be the answer for PostgreSQL. It might apply to Amazon Aurora, unless they
changed the behavior there. Perhaps ask Amazon.

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