MIME-Version: 1.0
References: <BN8PR04MB6289F7099F7B38E5B08D85B7D0362@BN8PR04MB6289.namprd04.prod.outlook.com>
 <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com>
In-Reply-To: <341dd184-9096-4d1f-a74c-7db02cd1004c@aklaver.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 3 Dec 2024 12:57:28 -0500
Message-ID: <CANzqJaC03VrGRJxU_c29YdX48byH1n71dGNRP7y21_dDjMJ5Ww@mail.gmail.com>
Subject: Re: Autovacuum and visibility maps
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ad5ad806286166aa"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaC03VrGRJxU_c29YdX48byH1n71dGNRP7y21_dDjMJ5Ww%40mail.gmail.com>
Precedence: bulk

--000000000000ad5ad806286166aa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Dec 3, 2024 at 11:57=E2=80=AFAM Adrian Klaver <adrian.klaver@aklave=
r.com>
wrote:
[snip]

>
> 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#GU=
C-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.
>

Shouldn't autovacuum_vacuum_insert_threshold kick off an autovacuum if
you're doing a lot of inserts?

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

--000000000000ad5ad806286166aa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 3, 2024 at 11:57=E2=80=AFAM A=
drian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver=
@aklaver.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><div>[snip]=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
I have to believe it is due to this:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/routine-vacuuming.html#V=
ACUUM-FOR-SPACE-RECOVERY" rel=3D"noreferrer" target=3D"_blank">https://www.=
postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVER=
Y</a><br>
<br>
&quot;If you have a table whose entire contents are deleted on a periodic <=
br>
basis, consider doing it with TRUNCATE rather than using DELETE followed <b=
r>
by VACUUM. TRUNCATE removes the entire content of the table immediately, <b=
r>
without requiring a subsequent VACUUM or VACUUM FULL to reclaim the <br>
now-unused disk space. The disadvantage is that strict MVCC semantics <br>
are violated.&quot;<br>
<br>
Combined with this:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/runtime-config-autovacuu=
m.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD" rel=3D"noreferrer" target=3D=
"_blank">https://www.postgresql.org/docs/current/runtime-config-autovacuum.=
html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD</a><br>
<br>
&quot;autovacuum_vacuum_threshold<br>
<br>
Specifies the minimum number of updated or deleted tuples needed to <br>
trigger a VACUUM in any one table. ...<br>
<br>
&quot;<br>
<br>
I&#39;m going to say the TRUNCATE itself does not trigger an autovacuum. I =
<br>
would suggest throwing a manual VACUUM in the table population script.<br><=
/blockquote><div><br></div><div>Shouldn&#39;t=C2=A0<span style=3D"backgroun=
d-color:rgb(248,249,250);color:rgb(0,0,0);font-family:monospace,monospace;f=
ont-size:14.4px">autovacuum_vacuum_insert_threshold</span>=C2=A0kick off an=
 autovacuum if you&#39;re doing a lot of inserts?</div><div><br></div></div=
><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" clas=
s=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butte=
r sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redac=
ted&gt; lobster!</div></div></div></div></div></div>

--000000000000ad5ad806286166aa--