Hi Kato-san, Thank you for working on the patch.
There may be room for further discussion regarding the design, but from a testing perspective, I did not find any issues. I tested the patch against the current master on Rocky Linux 9. However, it did not apply cleanly due to conflicts in src/test/regress/sql/stats.sql and src/test/regress/expected/stats.out. After resolving the conflicts locally, I tested the patch and did not find any issues. I confirmed the following: - TAP tests - Passed successfully. - pg_stat_all_tables - Confirmed that the TOAST-related columns were added successfully. - Confirmed that the values are displayed correctly. - Verified cases where dead tuples were generated and subsequently removed by autovacuum. - pg_stat_user_tables - Confirmed that the TOAST-related columns were added successfully. - Confirmed that the values are displayed correctly. - Verified cases where dead tuples were generated and subsequently removed by autovacuum. - pg_stat_sys_tables - Confirmed that the TOAST-related columns were added successfully. - Confirmed that the values are displayed correctly. - However, I was not able to create a test case in which dead tuples were generated for a system table's TOAST relation. It may be worth considering whether an additional test case is needed for this scenario. For reference, I have attached the rebased patch that I used for testing. Regards, Toshiki Toda 2026年8月5日(水) 22:28 Shinya Kato <[email protected]>: > 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
