Re: Automatic autovacuum to prevent wraparound - PG13.5

Frankly speaking, Aurora PostgreSQL's behaviour is quite unpredictable.
In our case, the autovacuum was not even getting triggered in spite of
crossing the autovacuum_freeze_max_age. Finally, the database went down
abruptly, which resolved the issue.

Thanks,
Ninad

On Wed, Jun 15, 2022 at 7:57 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

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