Database Reference
In-Depth Information
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...
The table
pg_stat_user_tables
is a view that shows current statistics for table usage.
To see if a table is used, you check for changes in its usage counts.
The previous query selects all tables where any of the usage counts for selector data
manipulation have changed.
There's more...
The quick and dirty way
If you are sure that you have no use for the cumulative statistics gathered by PostgreSQL, you
can just reset all table statistics by doing
select pg_stat_reset()
This sets all statistics to 0, and you can detect table use by just looking for tables where any
usage count is not 0
Of course, you can make a backup copy of statistics table first, as follows:
create table backup_stat_user_tables as
select current_timestamp as snaptime, *
from pg_stat_user_tables;
Collecting daily usage statistics
It is often useful to have historical usage statistics of tables available when trying to solve
performance problems or just understanding the usage patterns.
