Knowing when a table was last used
Once you know that a table is not currently being used, the next question is, When was it last used?
Getting ready
You need to use a user with enough monitoring privileges to be able to select from the following views: pg_stat_user_tables, administration functions pg_relation_filenode(), pg_stat_file(), pg_ls_dir(), and catalogs pg_class and pg_namespace.
How to do it…
As we already know, the pg_stat_user_tables view shows current table usage statistics. Since PostgreSQL 16, these include the last time each table was scanned, either in full or using an index (sequential or index scan). These are found respectively in the columns last_seq_scan and last_idx_scan.
To see when a table was last read from, you can run the following query:
SELECT last_seq_scan, last_idx_scan
FROM pg_stat_user_tables
WHERE relname=<table name>;
And here is the type of output that you can expect:
postgres=# SELECT date_trunc(...