3

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)
1
  • Please always lead with the version of Postgres in use. Nov 22, 2018 at 16:45

1 Answer 1

5

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.

3
  • 1
    I removed my answer instead and added the manual reference to yours - if you don't mind. Nov 22, 2018 at 16:59
  • Thanks Erwin...but the TOAST table is not available in pg_stat_sys_tables as well. postgres=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'messages1'; reltoastrelid ------------------------- pg_toast.pg_toast_16402 (1 row) postgres=# select relname, vacuum_count, n_tup_del, n_live_tup, n_dead_tup postgres-# from pg_stat_sys_tables postgres-# where relname='pg_toast.pg_toast_16402'; relname | vacuum_count | n_tup_del | n_live_tup | n_dead_tup ---------+--------------+-----------+------------+------------ (0 rows)
    – Gunjan
    Jan 22, 2019 at 7:14
  • @Gunjan your query is looking at pg_class not pg_stat_sys_tables which does show toast tables
    – Gurmokh
    Nov 14, 2023 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.