Re: VACUUM/ANALYZE Strategy for Low-Activity PostgreSQL 15 Instance

On Tue, Jan 13, 2026 at 6:39 AM Paul Smith* <paul(at)pscs(dot)co(dot)uk> wrote:

> On 13/01/2026 11:09, Gabriel Guillem Barceló 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 with
> 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 = 0 OR n_dead_tup = 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.
>

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