From: Michael Banck <mbanck@gmx.net>
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Auto Vacuum Question
Date: Mon, 16 Oct 2023 23:40:12 +0200
Message-ID: <652dadbd.170a0220.1d68.00f9@mx.google.com> (raw)
In-Reply-To: <DM8PR09MB667789C7CCCA7508D74FE6EEB8D7A@DM8PR09MB6677.namprd09.prod.outlook.com>
References: <DM8PR09MB667789C7CCCA7508D74FE6EEB8D7A@DM8PR09MB6677.namprd09.prod.outlook.com>
Hi,
On Mon, Oct 16, 2023 at 08:46:48PM +0000, Murthy Nunna wrote:
> It looks like the max value of autovacuum_freeze_max_age is 2 billion.
> I am wondering why anybody wants to change this setting to a lower number?
At a transaction age a bit above 2 billion, Postgres will stop accepting
connections and one needs to vacuum manually, usually resulting in a
long downtime.
So one needs some (or rather a lot of) daylight between the time
autovacuum decides to freeze a table and Postgres shutting down - it
could be that autovacuum is held back by long-running transactions or
unused replication slots or something.
I would consider autovacuum_freeze_max_age up to 1 billion mostly safe
(depending on the workload) and values up to 1.6 billion acceptable if
good monitoring is in place and everybody knows what is going on.
The other reason why want might not want to set
autovacuum_freeze_max_age so high is that the pg_xact and pg_commit_ts
sizes are proportional to autovacuum_freeze_max_age and take up more
space if autovacuum_freeze_max_age is higher, which might be problematic
if there is not a lot of storage present.
Michael
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mbanck@gmx.net, mnunna@fnal.gov
Subject: Re: Auto Vacuum Question
In-Reply-To: <652dadbd.170a0220.1d68.00f9@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox