Q:

PostgreSQL: Last access time to table

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.

A:

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.

A:

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

