Feedback-ID: i76984098:Fastmail
Message-ID: <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com>
Date: Tue, 3 Dec 2024 08:57:26 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Autovacuum and visibility maps
To: "Tefft, Michael J" <Michael.J.Tefft@snapon.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <BN8PR04MB6289F7099F7B38E5B08D85B7D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <BN8PR04MB6289F7099F7B38E5B08D85B7D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/341dd184-9096-4d1f-a74c-7db02cd1004c%40aklaver.com>
Precedence: bulk

On 12/3/24 08:32, Tefft, Michael J wrote:
> We have some batch queries that had occasionally having degraded 
> runtimes: from 2 hours degrading to 16 hours, etc.
> 
> Comparing plans from good and bad runs, we saw that the good plans used 
> index-only scans on table “x”, while the bad plans used index scans.
> 
> Using the pg_visibility utility, we found that all of the 83 partitions 
> of table “x” were showing zero blocks where all tuples were visible. We 
> ran a VACUUM on the table; the visibility maps are now clean and the 
> good plans came back.
> 
> Our question is: why did autovacuum not spare us from this?
> 
> We are using default autovacuum parameters for all except 
> log_autovacuum_min_duration=5000. These partitions are populated by 
> processes that do a truncate + a single insert-select.
> 
> We see autovacuum failure (failed to get lock) messages, followed by a 
> success message, in the log for one of these partitions (the biggest 
> one) but even that partition showed zero blocks with all tuples visible.
> 
> Are we wrong to expect autovacuum to clean up the visibility map?

I have to believe it is due to this:

https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY

"If you have a table whose entire contents are deleted on a periodic 
basis, consider doing it with TRUNCATE rather than using DELETE followed 
by VACUUM. TRUNCATE removes the entire content of the table immediately, 
without requiring a subsequent VACUUM or VACUUM FULL to reclaim the 
now-unused disk space. The disadvantage is that strict MVCC semantics 
are violated."

Combined with this:

https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD

"autovacuum_vacuum_threshold

Specifies the minimum number of updated or deleted tuples needed to 
trigger a VACUUM in any one table. ...

"

I'm going to say the TRUNCATE itself does not trigger an autovacuum. I 
would suggest throwing a manual VACUUM in the table population script.

> 
> postgres=# select version();
> 
>                                                   version
> 
> ----------------------------------------------------------------------------------------------------------
> 
> PostgreSQL 14.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 
> 20210514 (Red Hat 8.5.0-22), 64-bit
> 
> Thank you,
> 
> Mike Tefft
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com