Date: Thu, 26 Dec 2024 12:21:08 -0800
From: Jeremy Schneider <schneider@ardentperf.com>
To: Will Storey <will@summercat.com>, pgsql-general@lists.postgresql.org
Subject: Re: Disabling vacuum truncate for autovacuum
Message-ID: <20241226122108.33e906a3@jeremy-ThinkPad-T430s>
In-Reply-To: <Z2DE4lDX4tHqNGZt@dev.null>
References: <Z2DE4lDX4tHqNGZt@dev.null>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20241226122108.33e906a3%40jeremy-ThinkPad-T430s>
Precedence: bulk

On Mon, 16 Dec 2024 16:25:06 -0800
Will Storey <will@summercat.com> wrote:

> I would like to disable vacuum's truncate behaviour for autovacuum.
> Previously I had an outage due to its access exclusive lock when it
> was replicated to a hot standby.
> 
> When that outage happened it was from a VACUUM call in a cronjob
> rather than autovacuum. I now run such VACUUMs with TRUNCATE false
> which avoids the issue for these. However I've realized that
> autovacuum could cause this as well. This is of interest to me
> because I'm looking at tuning autovacuum and getting rid of the
> cronjob, but I've realized relying on autovacuum could be dangerous
> because of the truncates.

Can you tell us a little bit more about the outage? Autovacuum is
designed to quickly relinquish this lock if there is any contention, and
the dangers of disabling autovacuum are significant, so your statement
about autovac being "dangerous" will raise a lot of eyebrows.

Did your outage involve hot standbys serving read-only traffic, or did
it only involve a read-write database?

What was the exact nature of the outage and how did you narrow down the
cause to the exclusive lock held specifically during an autovacuum
truncation?

-Jeremy