Is there a way for checking an empty table has inserts and deleted for a while?

Question

I want to drop unused tables. I listed empty tables to make sure that tables not getting inserts and deletes (using such as temporary table) within 1 week.

Is there way for checking if a table is not getting any transactional operations?

I need a simple query to check this for a table.

Answer 1

The pg_stat_all_tables and other system views track traffic on all your tables from the moment they get created. Based on those, you might be able to figure out when and how extensively they were used:

select schemaname
      ,relname
      ,last_seq_scan
      ,last_idx_scan
      ,last_vacuum
      ,last_autovacuum
      ,last_analyze
      ,last_autoanalyze 
from pg_stat_all_tables 
where schemaname='public'
  and relname in ('your_empty_table','another_suspected_tmp_table');

All these somewhat translate to most recent table access. You can also see pg_statio_all_tables to determine how much stuff was written, read or removed.

Files that physically hold those also have filesystem-level metadata you can check, or you could track them down in statement logs if you have that enabled, or you could even try to take a look at the WAL entries for those tables. All these are discussed here.

For those that aren't empty you could guess based xmin/xmax.

If you have the logs, tracking triggers or pgaudit, it's easiest to check there. If not, tracking down and inspecting individual files on the filesystem or in the WAL is a bit more inconvenient and harder to do in bulk but it's the most reliable.