Message-ID: <965a52fe849ec6e5759d349b4063b5077353f24b.camel@cybertec.at>
Subject: Re: Avoid Wraparound Failures
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Loles <lolesft@gmail.com>, pgsql-admin@postgresql.org
Date: Sat, 26 Mar 2022 05:34:14 +0100
In-Reply-To: 
 <CA+FWGK6HmRx8=-2wZkw-9a6a36f6iEGgAH=bN1rNicbFgzas5A@mail.gmail.com>
References: 
	<CA+FWGK6HmRx8=-2wZkw-9a6a36f6iEGgAH=bN1rNicbFgzas5A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-3.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/965a52fe849ec6e5759d349b4063b5077353f24b.camel%40cybertec.at>
Precedence: bulk

On Fri, 2022-03-25 at 23:16 +0100, Loles wrote:
> Suppose the databases on my instance are near to have a wraparound failure.
> 
> (I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).
> 
> What do I have to do?
> 
> vacuum freeze;
> 
> better than,
> 
> vacuum analyze;
> 
> Or both?
> 
> If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?
> 
> More I read of this topic, more confused I am.
> 
> Please, I need simple and wise advice :)

DON'T PANIC

If what you see is the age of your oldest unfrozen rows approaching 200 million,
everything is just normal.  That's when anti-wraparound autovacuum *begins*.

Normally, you have nothing to do, except perhaps make sure than autovacuum is
fast enough (autovacuum_vacuum_cost_delay = 2).

If you want to prevent that autovacuum run from happening while your system is
busy (which normally also is no problem), you can trigger a manual VACUUM at
a time of lower database activity.  But make it a plain VACUUM, not a
VACUUM (FREEZE) or (god forbid) VACUUM (FULL), and only VACUUM those tables
that are large and approaching the threshold.  And don't VACUUM them all at the
same time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com