Message-ID: <9a196c5db21caadb873493439011e4959d387ffe.camel@cybertec.at>
Subject: Re: autovacuum default run with  vacuum_index_cleanup and
 vacuum_truncate and related lock?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "James Pang (chaolpan)" <chaolpan@cisco.com>,
	"pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Mon, 07 Feb 2022 17:09:20 +0100
In-Reply-To: 
 <PH0PR11MB51917B6D0E1C97DA6EAAEC3FD62C9@PH0PR11MB5191.namprd11.prod.outlook.com>
References: 
	<PH0PR11MB51917B6D0E1C97DA6EAAEC3FD62C9@PH0PR11MB5191.namprd11.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.40.4 (3.40.4-2.fc34) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/9a196c5db21caadb873493439011e4959d387ffe.camel%40cybertec.at>
Precedence: bulk

On Mon, 2022-02-07 at 08:43 +0000, James Pang (chaolpan) wrote:
> It’s Postgres 13 version, we plan to migrate from Oracle to Postgres 13 on RHEL8.
> One maintain job is about autovacuum, with default config,  autovacuum will do on
> tables with  “vacuum_index_cleanup” and “vacuum_truncate” option?
> With both options, autovacuum woker will hold “access exclusive” lock when truncate
> empty blocks and cleanup index ?  if that’s true, how to handle autovacuum to reduce
> the lock blocking issue?

Cleaning up the index will not take any locks that block data modifications.

The ACCESS EXCLUSIVE lock for truncation is only held for a very short time
and won't bother you (unless perhaps, you could get replication conflicts on
a standby server).

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