Subject: RE: Autovacuum and visibility maps

From: "Tefft, Michael J" <[email-masked]>
Date: Tue, 3 Dec 2024 18:11:21 +0000

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 <[email-masked]>
Sent: Tuesday, December 3, 2024 11:57 AM
To: Tefft, Michael J <[email-masked]>; [email-masked]
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

[email-masked]<mailto:[email-masked]>

---

From: Ron Johnson <[email-masked]>
Date: Tue, 3 Dec 2024 13:18:55 -0500

When in doubt, "manually" vacuum and/or analyze.

Maybe even disable autovacuum on that table before the TRUNCATE + INSERT,
do the "manual" vacuum-analyze and then re-enable autovacuum.  Bonus points
for programmatically determining which partitions you're going to insert
into, so that you only manually maintain those partitions.

On Tue, Dec 3, 2024 at 1:11 PM Tefft, Michael J <[email-masked]>
wrote:

> *From:* Adrian Klaver <[email-masked]>
> *Sent:* Tuesday, December 3, 2024 11:57 AM
> *To:* Tefft, Michael J <[email-masked]>;
> [email-masked]
> *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
>
> 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$>
>
>
>
> 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$>
>
>
>
> [email-masked]
>
>
>
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!