Database Reference
In-Depth Information
For this purpose, you can collect the usage data in a regular manner daily or even more often
using either cron or a PostgreSQL-specific scheduler like
pg_agent
.
The following query adds a timestamped snapshot of current usage statistics to the table
created earlier:
insert into backup_stat_user_tables
select current_timestamp as snaptime, *
from pg_stat_user_tables;
When did anybody last use it?
Once you find out that a table is not used currently, the next question is "when was it last used?"
Getting ready
Get access to the database as a superuser or to the database host computer as user postgres.
How to do it...
PostgreSQL does not have any built-in last-used information about tables, so you have to use
other means to figure it out.
If you have set up a cronjob to collect usage statistics, as described in the previous chapter,
then it is relatively easy to find out the last change date using an SQL query.
Else, you have basically two possibilities, neither of which gives you absolutely reliable answers.
You can either look at actual timestamps of the files in which the data is stored, or you can
use the
xmin
and
xmax
system columns to find out the latest transaction ID that has changed
the table data.
Looking at file dates
To find out the file name(s) in which the table data is stored, you have to do the following:
Here is a sample PL/PythonU function that lists main file statistics for files used to store a
table. You need to have PL/PythonU installed in your database for this to work. If you don't
have it, use the following:
CREATE LANGUAGE plpythonu;
to install the language in the database. This assumes you have the support for PL/PythonU
available on the database host.
