public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: VACUUM/ANALYZE Strategy for Low-Activity PostgreSQL 15 Instance
Date: Tue, 13 Jan 2026 09:52:05 -0500
Message-ID: <CANzqJaAzJAw3QcAdSu_6MUXwbBWQ5XEYb=2uNuSk9bcOn-PbGg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <DU0PR08MB79216D7A1EBC77E66899D508A68EA@DU0PR08MB7921.eurprd08.prod.outlook.com>
<[email protected]>
<DU0PR08MB7921A4C96214BA4517FD1E39A68EA@DU0PR08MB7921.eurprd08.prod.outlook.com>
<[email protected]>
On Tue, Jan 13, 2026 at 6:39 AM Paul Smith* <[email protected]> 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!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: VACUUM/ANALYZE Strategy for Low-Activity PostgreSQL 15 Instance
In-Reply-To: <CANzqJaAzJAw3QcAdSu_6MUXwbBWQ5XEYb=2uNuSk9bcOn-PbGg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox