Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in charge of a large PostgreSQL database, with few dozen tables. I suspect that many of these tables are never accessed.

What's the best way to check when was the last time a certain table was accessed? I thought of adding a trigger on DELETE, INSERT and UPDATE, but I hope there is a more efficient way.

share|improve this question
    
You can't create a trigger on select. Have you considered logging? –  Jack Douglas Aug 29 '11 at 8:28
    
Thanks, Corrected. Logging might be the solution, but the DB is heavily used and logs will probably take lots of disk space. –  Adam Matan Aug 29 '11 at 13:14
add comment

2 Answers

up vote 5 down vote accepted

pg_catalog.pg_statio_all_tables is your friend. All you should need to do is periodically poll pg_statio_all_tables for the tables in question. Changing stats ~ active table, non-changing stats ~ potentially unused table. Just be careful that no one does a select pg_stat_reset () ; in the middle of your monitoring.

For example:

test_1=# create table test_stats (col1 integer);
CREATE TABLE

test_1=# select * from pg_catalog.pg_statio_all_tables 
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |              0 |             0 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Inserts:

test_1=# insert into test_stats (col1) select generate_series( 1, 10000000);
INSERT 0 10000000

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          44260 |      10088481 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Selects:

test_1=# select count (*) from test_stats where col1 between 10000 and 50000;
 count 
-------
 40001
(1 row)

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |          85560 |      10091429 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

Deletes:

test_1=# delete from test_stats where col1 between 10000 and 50000;
DELETE 40001

test_1=# select * from pg_catalog.pg_statio_all_tables
         where schemaname not in ('pg_catalog', 'information_schema') 
         and relname = 'test_stats';
 relid | schemaname |  relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
-------+------------+------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
 22957 | public     | test_stats |         155075 |      10136163 |        [null] |       [null] |          [null] |         [null] |         [null] |        [null]
(1 row)

update-- 2011-09-01

Further testing indicates that vacuum appears to increase the values in pg_statio_all_tables somewhat, which is unfortunate for your desired use. While vacuum doesn't make use of pg_statio_all_tables useless, it does make interpreting the results a bit fuzzier.

Perhaps a better place to monitor is pg_catalog.pg_stat_all_tables (at least with newer versions of Pg). I'm looking at version 8.4 and that has counts for tuples inserted, read, updated, and deleted-- ISTR 8.2 doesn't have all that and I don't know about 8.3 so YMMV depending on the version of Pg that you are using.

A third option (for insert, update, and delete activity) is to watch the file timestamps in the $PGDATA/base/$datid directory. The filename should map to the oid of the table, so you could use this to identify tables that aren't getting inserts, updates, or deletes. Unfortunately, this doesn't address tables that are still getting selected from, and using tablespaces will cause additional complications (as those files will not be under $PGDATA/base/$datid). The timestamps won't update until any pending changes are flushed, but if the file hasn't changed in months then the odds of a currently pending change are probably small.

share|improve this answer
    
This rocks. Thanks! –  Adam Matan Sep 1 '11 at 4:49
add comment

You can get some information about the last change to a table with xmin, eg:

select max(xmin::text::bigint) from t;

But, you need to be aware of the modulo and wraparound and frozen xids. There isn't any way of converting this into a "time", but if you capture the value for your tables now, then compare at a later date, you can get a list of tables that have changed

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.