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.a...@cybertec.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 > > >
