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. Mike From: Adrian Klaver <adrian.kla...@aklaver.com> Sent: Tuesday, December 3, 2024 11:57 AM To: Tefft, Michael J <michael.j.te...@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.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>
