MIME-Version: 1.0
References: 
 <DU0PR08MB79216D7A1EBC77E66899D508A68EA@DU0PR08MB7921.eurprd08.prod.outlook.com>
 <81c12173-6da4-43e9-866a-00321a00415a@pscs.co.uk>
 <DU0PR08MB7921A4C96214BA4517FD1E39A68EA@DU0PR08MB7921.eurprd08.prod.outlook.com>
 <05e54ad2-0d6b-49b0-834a-4107fee68c51@pscs.co.uk>
In-Reply-To: <05e54ad2-0d6b-49b0-834a-4107fee68c51@pscs.co.uk>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 13 Jan 2026 09:52:05 -0500
Message-ID: 
 <CANzqJaAzJAw3QcAdSu_6MUXwbBWQ5XEYb=2uNuSk9bcOn-PbGg@mail.gmail.com>
Subject: Re: VACUUM/ANALYZE Strategy for Low-Activity PostgreSQL 15 Instance
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003f353d06484623e9"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAzJAw3QcAdSu_6MUXwbBWQ5XEYb%3D2uNuSk9bcOn-PbGg%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jan 13, 2026 at 6:39=E2=80=AFAM Paul Smith* <paul@pscs.co.uk> wrote=
:

> On 13/01/2026 11:09, Gabriel Guillem Barcel=C3=B3 Soteras wrote:
>
> CheckMK, as Anton case, monitors several metrics with a PostgreSQL
> integration <https://checkmk.com/integrations> . In this case is last
> vacuum and analyse <https://checkmk.com/integrations/postgres_stats>. It
> generates a monitoring item with pre-populated thresholds.
>
> You are not wrong at all. The* lazy admin problem *is that adjusting
> monitoring system on per-table basis is very time consuming, compared wit=
h
>  a weekly manual vacuum + analyze that makes 'no harm' out of business
> hours. I think i will go the weekly vacumdb route, or I will have to
> deactivate VACUUM and ANALYSE monitoring items.
>
> OK
>
> As you say, it does no harm, so you could do that, but I'd argue that it'=
s
> also unnecessary. Personally, I'd disable those vacuum & analyze monitors=
.
> 'last autovacuum' and 'last autoanalyze' are a bit more useful monitoring
> stats than last manual vacuum/analyze, unless you have a company policy
> mandating otherwise.
>

I created a view that joins pg_stat_user_tables to pg_class and then does:
select sut.table_name
     , GREATEST(sut.last_vacuum, sut.last_autovacuum)::timestamp(3) without
time zone AS latest_vacuum
     , GREATEST(sut.last_analyze, sut.last_autoanalyze)::timestamp(3)
without time zone AS latest_analyze
     , sut.n_mod_since_analyze as mod_ana
     , sut.n_dead_tup as dead_rows
     , cl.reltuples::bigint as row_count
     , case
           when cl.reltuples > 0 then
(100.0*sut.n_mod_since_analyze/cl.reltuples)::decimal(6,2)
           else null
       end as mod_pct
     , case
           when cl.reltuples > 0 then
(100.0*sut.n_dead_tup/cl.reltuples)::decimal(6,2)
           else null
       end as dead_pct

That shows percentages as well as counts.  Very helpful.

Even then, I've just looked at one of our databases that's been running
> since 2019, and the 'last vacuum' AND 'last autovacuum' times are still
> null for a lot of the tables. It's simply unnecessary to vacuum those
> tables, or even analyze them, as they just hold a few rows of data that
> rarely change.
>

And yet sometimes the course of least resistance is to make the metricians
happy.  Fortunately, my metricians are reasonable enough to let me exclude
rows where (n_mod_since_analyze =3D 0 OR n_dead_tup =3D 0).

If OP's metricians are not reasonable, or there's too much hassle in
modifying the filter to exclude unmodified tables, then just "vacuumdb
--analyze -t foo -t bar -t ..." all tables that haven't been vacuumed or
analyzed in the last, for example, 28 days.

A bash+psql command can easily generate a "-t t1 -t t2 -t t3 ..." string
that you pass to vacuumdb.

> I'd say you're better monitoring other metrics, eg "n_mod_since_analyze" =
&
> "n_dead_tup", to see if vacuum/analyze is necessary for that table. That
> will show if autovacuum/analyze isn't keeping up with the job.
>

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jan 13, 2026 at 6:39=E2=80=AFAM P=
aul Smith* &lt;<a href=3D"mailto:paul@pscs.co.uk">paul@pscs.co.uk</a>&gt; w=
rote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div bgcolor=3D"#FFFFFF">
    <div>On 13/01/2026 11:09, Gabriel Guillem
      Barcel=C3=B3 Soteras wrote:<br>
    </div>
    <blockquote type=3D"cite">
      <p><span style=3D"color:rgb(0,0,0)">CheckMK, as Anton case, monitors
          several metrics with a
        </span><span style=3D"font-family:Aptos,Arial,Helvetica,sans-serif;=
font-size:12pt;color:rgb(0,0,0)"><a href=3D"https://checkmk.com/integration=
s" style=3D"margin-top:0px;margin-bottom:0px" target=3D"_blank">PostgreSQL =
integration</a></span><span style=3D"color:rgb(0,0,0)">=C2=A0. In this case=
 is <a href=3D"https://checkmk.com/integrations/postgres_stats" style=3D"ma=
rgin-top:0px;margin-bottom:0px" target=3D"_blank">
            last vacuum and analyse</a>. It generates a monitoring item
          with pre-populated thresholds.</span></p>
      <p><span style=3D"font-family:Aptos,Arial,Helvetica,sans-serif;font-s=
ize:12pt;color:rgb(0,0,0)">You
          are not wrong at all. The<i>=C2=A0lazy admin=C2=A0problem
          </i>is that adjusting monitoring system on per-table basis is
          very time consuming, compared with =C2=A0a weekly manual vacuum +
          analyze that makes &#39;no harm&#39; out of business hours. I thi=
nk i
          will go the weekly vacumdb route, or I will have to deactivate
          VACUUM and ANALYSE monitoring items.</span></p>
    </blockquote>
    <p>OK</p>
    <p>As you say, it does no harm, so you could do that, but I&#39;d argue
      that it&#39;s also unnecessary. Personally, I&#39;d disable those vac=
uum
      &amp; analyze monitors. &#39;last autovacuum&#39; and &#39;last autoa=
nalyze&#39;
      are a bit more useful monitoring stats than last manual
      vacuum/analyze, unless you have a company policy mandating
      otherwise.</p></div></blockquote><div><br></div><div>I created a view=
 that joins pg_stat_user_tables to pg_class and then does:</div><div><font =
face=3D"monospace">select sut.table_name<br>=C2=A0 =C2=A0 =C2=A0, GREATEST(=
sut.last_vacuum, sut.last_autovacuum)::timestamp(3) without time zone AS la=
test_vacuum<br>=C2=A0 =C2=A0 =C2=A0, GREATEST(sut.last_analyze, sut.last_au=
toanalyze)::timestamp(3) without time zone AS latest_analyze <br>=C2=A0 =C2=
=A0 =C2=A0, sut.n_mod_since_analyze as mod_ana<br>=C2=A0 =C2=A0 =C2=A0, sut=
.n_dead_tup as dead_rows<br>=C2=A0 =C2=A0 =C2=A0, cl.reltuples::bigint as r=
ow_count<br>=C2=A0 =C2=A0 =C2=A0, case<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0when cl.reltuples &gt; 0 then (100.0*sut.n_mod_since_analyze/cl.r=
eltuples)::decimal(6,2)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0else nu=
ll<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0end as mod_pct<br>=C2=A0 =C2=A0 =C2=A0, ca=
se<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0when cl.reltuples &gt; 0 the=
n (100.0*sut.n_dead_tup/cl.reltuples)::decimal(6,2)<br>=C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0else null<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0end as dead_pc=
t<br></font></div><div><br></div><div>That shows percentages as well as cou=
nts.=C2=A0 Very helpful.</div><div><br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div bgcolor=3D"#FFFFFF">
    <p>Even then, I&#39;ve just looked at one of our databases that&#39;s b=
een
      running since 2019, and the &#39;last vacuum&#39; AND &#39;last autov=
acuum&#39;
      times are still null for a lot of the tables. It&#39;s simply
      unnecessary to vacuum those tables, or even analyze them, as they
      just hold a few rows of data that rarely change.</p></div></blockquot=
e><div><br></div><div>And yet sometimes the course of least resistance is t=
o make the metricians happy.=C2=A0 Fortunately, my metricians=C2=A0are reas=
onable enough=C2=A0to let me exclude rows where (n_mod_since_analyze =3D 0 =
OR=C2=A0n_dead_tup =3D 0).</div><div><br></div><div>If OP&#39;s metricians =
are not reasonable, or there&#39;s too much hassle in modifying the filter =
to exclude unmodified tables, then just &quot;vacuumdb --analyze -t foo -t =
bar -t ...&quot; all tables that haven&#39;t been vacuumed or analyzed in t=
he last, for example, 28 days.</div><div><br></div><div>A bash+psql command=
 can easily generate a &quot;-t t1 -t t2 -t t3 ...&quot; string that you pa=
ss to vacuumdb.</div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div=
 bgcolor=3D"#FFFFFF">
    <p>I&#39;d say you&#39;re better monitoring other metrics, eg
      &quot;n_mod_since_analyze&quot; &amp; &quot;n_dead_tup&quot;, to see =
if vacuum/analyze
      is necessary for that table. That will show if autovacuum/analyze
      isn&#39;t keeping up with the job.</p></div></blockquote><div><br></d=
iv></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"l=
tr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, a=
nd butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&=
lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000003f353d06484623e9--