Message-ID: <652dadbd.170a0220.1d68.00f9@mx.google.com>
Sender: Michael Banck <mbanck@gmail.com>
Date: Mon, 16 Oct 2023 23:40:12 +0200
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
References: 
 <DM8PR09MB667789C7CCCA7508D74FE6EEB8D7A@DM8PR09MB6677.namprd09.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <DM8PR09MB667789C7CCCA7508D74FE6EEB8D7A@DM8PR09MB6677.namprd09.prod.outlook.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/652dadbd.170a0220.1d68.00f9%40mx.google.com>
Precedence: bulk

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