Vacuum on TOAST table

Question

Is it possible to get the number of dead/live tuples for TOAST table as we do for normal table?

CREATE TABLE messages1 (message text);

INSERT INTO messages1
SELECT (SELECT 
        string_agg(chr(floor(random() * 26)::int + 65), '')
        FROM generate_series(1,10000)) 
FROM generate_series(1,10);

SELECT reltoastrelid::regclass 
 FROM pg_class 
 WHERE relname = 'messages1';

 DELETE FROM messages1
WHERE ctid IN (
    SELECT ctid
    FROM messages1
    ORDER BY 1
    LIMIT 6
);

postgres=# SELECT reltoastrelid::regclass 
postgres-#  FROM pg_class 
postgres-#  WHERE relname = 'messages1';
      reltoastrelid       
--------------------------
 pg_toast.pg_toast_147777
(1 row)


postgres=# select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup from pg_stat_user_tables where relname='pg_toast.pg_toast_147777';
 relname | vacuum_count | n_tup_del | n_live_tup | n_dead_tup 
---------+--------------+-----------+------------+------------
(0 rows)

Answer 1

TOAST table is treated as system table , so you need to query pg_stat_sys_tables, not pg_stat_user_tables:

select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup 
from pg_stat_sys_tables 
where relname='pg_toast_147777'

Or you can put it all together,

select  cl.relname ,vacuum_count, n_tup_del, n_live_tup, n_dead_tup  
 FROM pg_class cl 
 inner join pg_stat_sys_tables st on st.relid = cl.reltoastrelid
 WHERE cl.relname = 'messages1';

Quoting the manual:

The pg_stat_all_tables view will contain one row for each table in the current database (including TOAST tables), showing statistics about accesses to that specific table. The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.