last_analyze/last_vacuum services seem to wronly consider partitioned tables

[TheDromundKaas]

I had some hosts where after adding both last_analyze and last_vacuum services I regularly had to do manual vacuuming, as some of our servers are so low traffic/churn that they do not hit the autovacuum water mark/parameter triggers.

After a while I discovered that for some servers and databases even a full

vacuumdb --all --analyze --freeze

did not seem to change the critical state from check_pgactivity. And while I am not an experienced perl writer, as far as I can guess why that is I think it's because of partitioned tables.

I checked first how check_pgactivity determined the state, and found a select from pg_stat_user_tables, using the fields last_vacuum, last_autovacuum, last_analyze and last_autoanalyze. I selected the object (schemaname, relname) and these fields, and found that for some tables these fields stay empty, even after the manual execution of vacuumdb.

Using

vacuumdb --all --analyze --freeze -e

I checked first which statement vacuumdb passed to PostgreSQL and found that for the tables that had a NULL value for the mentioned fields no vacuum statement was passed to PostgreSQL. I checked out these tables and found all of them to be partitioned tables.

My best guess at the moment would be that partitioned tables are not vacuumed, only its partitions.

To make sure that's not only a problem of the vacuumdb tool I tried to force a vacuum on such a partitioned table in psql:

vacuum (analyze, freeze) <tablename>

but that does not write any dates into the last_vacuum/last_autovacuum fields of the pg_stat_user_tables view for that table either.

PostgreSQL version: 15.14

[gleu]

AFAICT, 13670d5 should have fixed your issue. Can you check on your server with the code on the master branch? Thanks.

[TheDromundKaas]

My apologies, I was incommunicado for a week.

I can see the code that should prevent the problem in the current check_pgactivity script for the analyze part. After a little more digging I think the problem is the vacuum part.

I executed a vacuum from the command line again:

vacuumdb --all --analyze --freeze

However, I find the last_vacuum field in pg_stat_user_tables to be current for none of the partitioned tables:

entwicklung_controller_rzf=# select relkind, a.relname, last_vacuum, a.last_autovacuum , last_analyze, a.last_autoanalyze  from pg_stat_user_tables a
JOIN pg_class b on a.relid = b.oid
where last_vacuum is null;
relkind |          relname          | last_vacuum | last_autovacuum |         last_analyze          | last_autoanalyze
---------+---------------------------+-------------+-----------------+-------------------------------+------------------
p       | main_jobevent             |             |                 | 2025-10-24 11:35:14.69159+02  |
p       | main_inventoryupdateevent |             |                 | 2025-09-01 12:27:33.999624+02 |
p       | main_projectupdateevent   |             |                 | 2025-09-01 12:27:37.141163+02 |
p       | main_adhoccommandevent    |             |                 | 2025-09-01 12:27:38.693006+02 |
p       | main_systemjobevent       |             |                 | 2025-09-01 12:27:38.702111+02 |

Although it looks like analyze and vacuum are handled differently, it might be necessary to exclude the vacuum part in the same vein as the analyze part.

Edit 1:

I also tried

vacuumdb --all --freeze -e

to see if it is some side effect from the analyze part, but there is no vacuum command passed for the partitioned table(s).