Bug #29663
open
Bug #29154: nodelastcompliance table can get *really* big
Autovacuuming of nodelastcompliance could be improved
Description
The table has the same number of rows as nodes in Rudder, but the size and bloat it occupies could be huge: the compliance jsonb data is stored in postgres toast table.
It appears the toast table is not vacuumed, contrary to the main table.
The SQL query for the main table:
> SELECT relname, n_live_tup, n_dead_tup, autovacuum_count, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'node_analysis';
+---------------+------------+------------+------------------+-------------------------------+
| relname | n_live_tup | n_dead_tup | autovacuum_count | last_autovacuum |
|---------------+------------+------------+------------------+-------------------------------|
| node_analysis | 1100 | 0 | 137 | 2026-08-27 14:53:46.174298+02 |
+---------------+------------+------------+------------------+-------------------------------+
But the SQL query for the associated TOAST table:
> SELECT relid::regclass, n_live_tup, n_dead_tup, n_ins_since_vacuum,
last_autovacuum, autovacuum_count,
pg_size_pretty(pg_relation_size(relid)) AS heap,
pg_size_pretty(pg_indexes_size(relid)) AS idx
FROM pg_stat_all_tables
WHERE relid = (SELECT reltoastrelid FROM pg_class WHERE oid='nodelastcompliance'::regclass)
+--------------------------+------------+------------+--------------------+-------------------------------+------------------+---------+-------+
| relid | n_live_tup | n_dead_tup | n_ins_since_vacuum | last_autovacuum | autovacuum_count | heap | idx |
|--------------------------+------------+------------+--------------------+-------------------------------+------------------+---------+-------|
| pg_toast.pg_toast_736499 | 4024 | 246000 | 8000 | 2026-08-27 16:29:08.438807+02 | 143 | 2066 MB | 37 MB |
+--------------------------+------------+------------+--------------------+-------------------------------+------------------+---------+-------+
The above toast table counts will not reset with the default autovacuum options of postgres, and will not update the last date of autovacuum nor increase the autovacuum count.
But when changing the toast.autovacuum_x options of the table to lower than defaults, the autovacuum happens and reduces the heap size, resets dead tuples.
We should change the toast.autovacuum_x specifically for this table, which tends to have growing TOAST, especially with huge directives as TOAST data as stored as chunks for the single details jsonb column in nodelastcompliance
Updated by Clark ANDRIANASOLO 11 days ago
- Status changed from New to In progress
Updated by Clark ANDRIANASOLO 11 days ago
- Status changed from In progress to Pending technical review
- Assignee changed from Clark ANDRIANASOLO to François ARMAND
- Pull Request set to https://github.com/Normation/rudder/pull/7447