Knowing whether anybody is using a specific table

This recipe will help you when you are in doubt about whether an obscure table is being used anymore, or if it has been left over from past use and is just taking up space.

Getting ready

Make sure that you are a superuser, or at least have full rights to the table in question.

How to do it…

Perform the following steps:

1. To see whether a table is currently in active use (that is, whether anyone is using it while you are watching it), run the following query on the database you plan to inspect:

   CREATE TEMPORARY TABLE tmp_stat_user_tables AS
          SELECT * FROM pg_stat_user_tables;
   

2. Then, wait for a while and see what has changed:

   SELECT * FROM pg_stat_user_tables n
     JOIN tmp_stat_user_tables t
       ON n.relid=t.relid
      AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del)
       <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);
   

...