Feedback-ID: i76984098:Fastmail
Message-ID: <80f60c4c-aeec-46f0-9b8e-81a5aace1ffa@aklaver.com>
Date: Tue, 3 Dec 2024 11:23:22 -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>
 <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com>
 <BN8PR04MB62890EE42B888F3A0B455F76D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <BN8PR04MB62890EE42B888F3A0B455F76D0362@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/80f60c4c-aeec-46f0-9b8e-81a5aace1ffa%40aklaver.com>
Precedence: bulk


On 12/3/24 10:11 AM, Tefft, Michael J wrote:
> Thanks for the point about truncates versus deletes.
> 
> But most of these partitions have over 100k rows, all inserted at once. 
> We have the default setting:
> 
> #autovacuum_vacuum_insert_threshold = 1000      # min number of row inserts
> 
> So I thought we should be triggering by inserts.

 From your OP I took the following literally:

"... a single insert-select".

Take a look at the stat table below:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW

pg_stat_all_tables

For given table and see what the *autovacuum* fields return.

You can use the function below to see if there are per table settings 
that are overriding the postgresql.conf settings.

https://www.postgresql.org/docs/current/functions-info.html

pg_options_to_table()

Something like:

select pg_options_to_table(reloptions) from pg_class where relname = 
'some_table';

> 
> Mike
> 
> *From:*Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Tuesday, December 3, 2024 11:57 AM
> *To:* Tefft, Michael J <Michael.J.Tefft@snapon.com>; 
> pgsql-general@lists.postgresql.org
> *Subject:* Re: Autovacuum and visibility maps
> 
> 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
> 
> 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://urldefense.com/v3/__https://www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZSxzvbw$ <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/routine-vacuuming.html*VACUUM-FOR-SPACE-RECOVERY__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOracZSxzvbw$>
> 
> "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://urldefense.com/v3/__https://www.postgresql.org/docs/current/runtime-config-autovacuum.html*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$ <https://urldefense.com/v3/__https:/www.postgresql.org/docs/current/runtime-config-autovacuum.html*GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD__;Iw!!Lf_9VycLqA!mGufXaOdGX6PdXSpHcIUnIF1pe8evFpE7r-l4vJVUcoY--jp8LtF-jWv8YicvFWegi1-_jyxJnNx3YBvbxQOraeerEd0yw$>
> 
> "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 <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com