Hi hackers, I am proposing a patch that adds four columns to pg_stat_all_tables (toast_relid, toast_n_dead_tup, toast_last_autovacuum, and toast_autovacuum_count), so that the state of a table's TOAST table can be observed from the owning table's row.
Motivation: Statistics for TOAST tables already exist, but they are hard to reach when troubleshooting. They appear only as separate rows in the pg_toast schema, pg_stat_user_tables filters that schema out entirely, and autovacuum processes a TOAST table independently of its main table. As a result, a table can look perfectly healthy (n_dead_tup = 0, recently autovacuumed) while most of the dead space is in its TOAST table, and nothing in the standard views hints that one should join pg_class.reltoastrelid by hand. The typical symptom is that pg_table_size() keeps growing while n_dead_tup stays near zero. Design: pg_statio_all_tables already exposes TOAST activity on the owning table's row (toast_blks_read and toast_blks_hit) by joining pg_class on reltoastrelid, so this patch applies the same pattern to pg_stat_all_tables. The new columns call the existing statistics functions on the TOAST relation's OID, so no new counters or C functions are introduced, and tables without a TOAST table show NULLs. The TOAST values are deliberately not folded into the table's own counters, because TOAST tuples are chunks of long values and mixing the units would make the numbers meaningless. The patch is attached. Thoughts? -- Best regards, Shinya Kato NTT OSS Center
v1-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patch
Description: Binary data
