MIME-Version: 1.0
References: <177089689173.797.9982569831633965239@wrigleys.postgresql.org>
In-Reply-To: <177089689173.797.9982569831633965239@wrigleys.postgresql.org>
From: Lukas Fittl <lukas@fittl.com>
Date: Thu, 12 Feb 2026 12:21:45 -0800
Message-ID: 
 <CAP53PkzAKvL=mmDnTVpHWYzSQLRz_fQS6R28m_VYL7zG0fxKfg@mail.gmail.com>
Subject: Re: "Disadvantages of increasing autovacuum_freeze_max_age" paragraph
 to be modified
To: divyamodernite@gmail.com, pgsql-docs@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Heikki Linnakangas <hlinnaka@iki.fi>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAP53PkzAKvL%3DmmDnTVpHWYzSQLRz_fQS6R28m_VYL7zG0fxKfg%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 12, 2026 at 8:52=E2=80=AFAM PG Doc comments form
<noreply@postgresql.org> wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/18/routine-vacuuming.html
> Description:
>
> Hello
>
> The current doc says (Routine Vacuuming for all available versions) :
>
> "The sole disadvantage of increasing autovacuum_freeze_max_age (and
> vacuum_freeze_table_age along with it) is that the pg_xact and pg_commit_=
ts
> subdirectories of the database cluster will take more space, because it m=
ust
> store the commit status and (if track_commit_timestamp is enabled) timest=
amp
> of all transactions back to the autovacuum_freeze_max_age horizon. The
> commit status uses two bits per transaction, so if autovacuum_freeze_max_=
age
> is set to its maximum allowed value of two billion, pg_xact can be expect=
ed
> to grow to about half a gigabyte and pg_commit_ts to about 20GB. If this =
is
> trivial compared to your total database size, setting
> autovacuum_freeze_max_age to its maximum allowed value is recommended.
> Otherwise, set it depending on what you are willing to allow for pg_xact =
and
> pg_commit_ts storage. (The default, 200 million transactions, translates =
to
> about 50MB of pg_xact storage and about 2GB of pg_commit_ts storage.)"
>
> Based on my experience, the growing size of pg_xact and pg_commit_ts
> subdirectories is not the "sole" disadvantage of increasing
> autovacuum_freeze_max_age. The higher the value is, the later autovacuum
> will be triggered to prevent wraparound. Autovacuum launches workers to d=
o
> the work primarily for 2 reasons : 1. If the no. for dead tuples is great=
er
> than the calculated threshold, and 2. If the no. of txids consumed by the
> database cluster have reached/surpassed autovacuum_freeze_max_age. If we
> increase the autovacuum_freeze_max_age to 2 billion (as recommended above=
),
> to trigger on the second condition autovacuum might be too late already.
> This is a more important point in my opinion than the one in the docs.

I would agree with this understanding. I don't understand why this is
worded the way it is today, it seems like bad guidance.

To capture as I understand it: One should not set
autovacuum_freeze_max_age to two billion because that would
effectively make one dependent on the (now luckily existing) failsafe
vacuums, instead of doing anti-wraparound vacuums.

Looking back at the history, I think this was first worded that way in
48188e1621bb, almost 20 years ago, and whilst the text has changed
over the years, the intent seems to come from back then.

Adding Tom and Heikki since they were involved in that commit back
then, and may recall why the wording was changed that way?

> My suggestion would be :
> 1. To not recommend setting  autovacuum_freeze_max_age to 2 billion, as i=
t
> contradicts with the paragraph already mentioned in the docs :
> "The maximum time that a table can go unvacuumed is two billion transacti=
ons
> minus the vacuum_freeze_min_age value at the time of the last aggressive
> vacuum. If it were to go unvacuumed for longer than that, data loss could
> result. To ensure that this does not happen, autovacuum is invoked on any
> table that might contain unfrozen rows with XIDs older than the age
> specified by the configuration parameter autovacuum_freeze_max_age. (This
> will happen even if autovacuum is disabled.)"
>
> 2. Remove the word "sole", as its not true (already explained above why).

+1 to both points.

Thanks,
Lukas

--
Lukas Fittl