O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Knowing whether anybody is using a specific table

This recipe helps you when you are in doubt whether some obscure table is used any more or it is left over from old times and just takes 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…

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;

Then wait a little, 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) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required