Add TOAST statistics columns to pg_stat_all_tables

Started by Shinya Kato4 months ago6 messageshackers
Jump to latest
#1Shinya Kato
shinya11.kato@gmail.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

Attachments:

t253324_1
v1-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchapplication/octet-stream; name=v1-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchDownload+135-4
#2shihao zhong
zhong950419@gmail.com
In reply to: Shinya Kato (#1)
Re: Add TOAST statistics columns to pg_stat_all_tables

Hi Shinya,

Thoughts?

I tested v1 on current master. The problem is real. pg_stat_user_tables
hides the pg_toast schema, so TOAST bloat is invisible there. The
regression suite passes with your new test.

There is one thing I would like you to consider change.

Monitoring tools read pg_stat_all_tables all the time, so I measured
what the patch costs. With 10,000 tables, which gives 15,107 rows:

before the patch 19.4 ms
v1 28.9 ms

Almost all of that is the new LEFT JOIN pg_class T, not the three
function calls. Those are strict, so a table with no TOAST table costs
nothing.

The join is also not needed. T.oid holds the same value as
C.reltoastrelid, which you already use via NULLIF(C.reltoastrelid, 0)
for the toast_relid column. Pass that same expression to the three
functions, drop the join and the T.oid in GROUP BY, and the time goes
back to 20.7 ms with the same output. I checked all four columns over
15,120 rows, including materialized views and partitioned tables.

One note on the test. Temporary tables are never autovacuumed, so
toast_last_autovacuum is always NULL there and toast_autovacuum_count
is always 0. Checking the new columns against the TOAST table's own row
would catch a wrong OID:

SELECT s.toast_n_dead_tup = t.n_dead_tup
FROM pg_stat_all_tables s
JOIN pg_stat_all_tables t ON t.relid = s.toast_relid
WHERE s.relname = 'stats_toast_test';

v1 also needs a rebase, because stats.sql and stats.out conflict.

Happy to test a v2. If it is easier, I can send the join change and the
rebase as a patch for you to take or leave.

Thanks,
Shihao

#3Toshiki Toda
toshiki.toda.pgtech@gmail.com
In reply to: Shinya Kato (#1)
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.kato@gmail.com>:

Show quoted text

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

Attachments:

t253324_3
v1-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchapplication/octet-stream; name=v1-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchDownload+135-4
#4Shinya Kato
shinya11.kato@gmail.com
In reply to: shihao zhong (#2)
Re: Add TOAST statistics columns to pg_stat_all_tables

On Tue, Sep 15, 2026 at 6:48 AM shihao zhong <zhong950419@gmail.com> wrote:

The join is also not needed. T.oid holds the same value as
C.reltoastrelid, which you already use via NULLIF(C.reltoastrelid, 0)
for the toast_relid column. Pass that same expression to the three
functions, drop the join and the T.oid in GROUP BY, and the time goes
back to 20.7 ms with the same output. I checked all four columns over
15,120 rows, including materialized views and partitioned tables.

Thanks for testing and measuring this. You are right, and v2 does it
that way. Removing the join also leaves the FROM clause and GROUP BY
list untouched, so the patch now only adds columns.

One note on the test. Temporary tables are never autovacuumed, so
toast_last_autovacuum is always NULL there and toast_autovacuum_count
is always 0. Checking the new columns against the TOAST table's own row
would catch a wrong OID:

Adopted. v2 compares all four columns against the TOAST table's own
row in pg_stat_all_tables. I also changed the lookup from relname to
'stats_toast_test'::regclass, so the test cannot match a same-named
table from another session under installcheck.

--
Shinya Kato
NTT OSS Center

Attachments:

t253324_4
v2-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchapplication/octet-stream; name=v2-0001-Add-TOAST-statistics-columns-to-pg_stat_all_table.patchDownload+144-1
#5shihao zhong
zhong950419@gmail.com
In reply to: Shinya Kato (#4)
Re: Add TOAST statistics columns to pg_stat_all_tables

Hi Shinya,

Thanks for addressing all of my comments.

LGTM! Move to `Ready For Committer`

Regards,
Shihao

#6solai v
solai.cdac@gmail.com
In reply to: Shinya Kato (#4)
Re: Add TOAST statistics columns to pg_stat_all_tables

Hi all,

On Thu, Oct 15, 2026 at 2:53 PM Shinya Kato <shinya11.kato@gmail.com> wrote:

On Tue, Sep 15, 2026 at 6:48 AM shihao zhong <zhong950419@gmail.com> wrote:

The join is also not needed. T.oid holds the same value as
C.reltoastrelid, which you already use via NULLIF(C.reltoastrelid, 0)
for the toast_relid column. Pass that same expression to the three
functions, drop the join and the T.oid in GROUP BY, and the time goes
back to 20.7 ms with the same output. I checked all four columns over
15,120 rows, including materialized views and partitioned tables.

Thanks for testing and measuring this. You are right, and v2 does it
that way. Removing the join also leaves the FROM clause and GROUP BY
list untouched, so the patch now only adds columns.

One note on the test. Temporary tables are never autovacuumed, so
toast_last_autovacuum is always NULL there and toast_autovacuum_count
is always 0. Checking the new columns against the TOAST table's own row
would catch a wrong OID:

Adopted. v2 compares all four columns against the TOAST table's own
row in pg_stat_all_tables. I also changed the lookup from relname to
'stats_toast_test'::regclass, so the test cannot match a same-named
table from another session under installcheck.

Thank you for the patch. I tested the patch on the current development
branch. The patched source builds successfully, and all 239 regression
tests passed. I also performed a manual test using a table containing
large text values. I verified that the four new columns in
pg_stat_all_tables report the expected TOAST statistics. The reported
TOAST relation OID matched the actual relation, and the dead-tuple
count matched the TOAST relation's own statistics. I also verified the
behavior for a table without a TOAST relation. Additionally, I
reviewed the regression-test changes and the view definition. The
tests cover the new statistics, including the case where a table has
no TOAST relation. The implementation uses the owning table's existing
reltoastrelid value and does not reintroduce the additional pg_class
join that caused overhead. The patch looks good to me based on my
testing.

Regards
Solai