Re: Add TOAST statistics columns to pg_stat_all_tables

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 <shinya11(dot)kato(at)gmail(dot)com>:

> 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
>