Message-ID: <a21fd458be8ac8edc342f9a8e145739a1f435884.camel@posteo.de>
Subject: last_analyze/last_vacuum in pg_stat_all_tables (PG 17.10)
From: Thomas Michael Engelke <thomas.engelke@posteo.de>
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: Fri, 03 Jul 2026 10:20:57 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a21fd458be8ac8edc342f9a8e145739a1f435884.camel%40posteo.de>
Precedence: bulk

Good morning,

for monitoring purposes we use check_pgactivity
(https://github.com/OPMDG/check_pgactivity) to check a number of
different metrics. One of those checks the last analyze timestamp of
either kind, another the last vacuum timestamp of either kind, and
alerts when either of those becomes older than a certain age that can
be specified as a parameter to these checks.

This is the SQL that runs to find these values:

$PG_VERSION_91 =3D> qq{
            SELECT
                pg_is_in_recovery()::int AS is_in_recovery,
                current_database(),
                schemaname,
                a.relname,
                CASE WHEN NOT pg_is_in_recovery() THEN
                  coalesce(max(
                       coalesce(extract(epoch FROM
                         current_timestamp -
                                 greatest(last_${type},
last_auto${type})),
                        'infinity'::float)),
                   'NaN'::float)
                  ELSE 'NaN'::float END,
                CASE WHEN NOT pg_is_in_recovery() THEN
                  coalesce(sum(${type}_count), 0)
                  ELSE 0 END
                  AS ${type}_count,
                CASE WHEN NOT pg_is_in_recovery() THEN
                  coalesce(sum(auto${type}_count), 0)
                  ELSE 0 END
                  AS auto${type}_count,
                CASE WHEN NOT pg_is_in_recovery() THEN
                  (
                    SELECT
md5(tup_inserted::text||tup_updated::text||tup_deleted::text||'$c_limit
$w_limit'
                    )
                    FROM pg_catalog.pg_stat_database
                    WHERE datname =3D current_database()
                  )
                  ELSE '' END
            FROM pg_stat_all_tables a
            JOIN pg_class b on a.relid =3D b.oid
            WHERE schemaname NOT LIKE 'pg_temp_%'
              AND schemaname NOT LIKE 'pg_toast_temp_%'
              AND pg_relation_size(relid) >=3D $table_min_size
              AND schemaname NOT LIKE 'information_schema'
              AND (('${type}' =3D 'analyze' AND schemaname NOT LIKE
'pg_toast') -- TOAST tables aren't ANALYZEd
                OR  ('${type}' =3D 'vacuum'))
              AND (('${type}' =3D 'analyze' AND a.relname NOT LIKE
'pg_statistic') -- pg_statistic never gets ANALYZEd
                OR  ('${type}' =3D 'vacuum'))
              AND relkind <> 'p' -- partitioned table do not have
last_* information
            GROUP BY schemaname, a.relname
        }

{type} gets replaced by either analyze or vacuum.

What happens *sometimes* on servers is that the age of the oldest
maintenance increases to infinity, as the fields of last_analyze and
last_vacuum are emptied (NULL value).

However, looking into the monitoring system I can see that there must
have been values in these fields before. I can see the values coming
ouf of that check 2 hours ago, the values were not infinity.

Are there any circumstances under which these fields get emptied? From
reading the documentation any value should never be replaced by a NULL
value:

last_vacuum timestamp with time zone: Last time at which this table was
manually vacuumed (not counting VACUUM FULL)

last_autovacuum timestamp with time zone: Last time at which this table
was vacuumed by the autovacuum daemon

last_analyze timestamp with time zone: Last time at which this table
was manually analyzed

last_autoanalyze timestamp with time zone: Last time at which this
table was analyzed by the autovacuum daemon

I can probably work around that by using either vacuum against the
database or vacuumdb on the system, but I'd like to understand if there
are other factors that influence these fields, and maybe the monitoring
agent needs to change in that regard.