O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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

Is anybody using a specific table?

This one helps you when you are in doubt if some obscure table is used any more, or is it just leftover from old times that just takes up space.

Getting ready

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

How to do it...

To see if a table is currently in active use, that is, if anyone is using it while you watch, run the following:

create temp table tmp_stat_user_tables as select * from pg_stat_user_tables;

Then wait a little, and see what is 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);

How it works... ...

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