Question 391996 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    Make a simple trigger function activity_watch() that monitors table activity into a log table activity, add after insert, after delete and after update triggers to your target tables and later inspect the log table. Here is an example for a target table called demo.

    create table activity (operation text, tablename text, ts timestamptz);
create or replace function activity_watch() returns trigger as 
$$
begin 
    insert into activity (operation, tablename, ts) 
        values (TG_OP, TG_TABLE_NAME, current_timestamp);
    return null;
end;
$$ language plpgsql;

-- For every target table
create trigger demo_monitor_insdelup 
  after insert or delete or update on demo
  for each row execute function activity_watch();

-- ... and later inspect what happened during the last 10 days for example
select tablename from activity
where ts > (current_timestamp - interval '10 days')
group by tablename;

    Drop the monitoring triggers after you no longer need them.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search