Monitoring table activity is a vital task for database administrators looking to optimize performance or clean up “bloat” from unused objects. PostgreSQL provides a built-in cumulative statistics system that tracks every scan, insert, and maintenance task performed on your relations. By querying these system views, you can pinpoint exactly when a table was last read from or modified without the need for external auditing tools.
Understanding these timestamps helps in identifying cold data that can be moved to cheaper storage or determining if a table is safe to drop. This guide explores the different methods to retrieve last-use statistics using standard system catalogs like pg_stat_all_tables.
Table of Contents
Key Takeaways for Monitoring Table Usage
- pg_stat_all_tables → The primary system view for tracking table-level activity, including read scans and row modifications.
- last_seq_scan → A timestamp recorded in
pg_stat_all_tablesthat shows the last time a sequential scan was initiated on a table. - last_idx_scan → The timestamp indicating the last time any index associated with the table was used for a query.
- Maintenance Timestamps → PostgreSQL also tracks the last time a table was processed by VACUUM or ANALYZE operations.
- Statistics Resets → It is important to remember that these counters are reset during an unclean shutdown or a server crash.
Method 1: Checking Read Activity via pg_stat_user_tables
The most effective way to see when a table was last queried is to check the last_seq_scan and last_idx_scan columns. This covers both full table scans and targeted searches using indexes.
SQL Command:
SELECT relname, last_seq_scan, last_idx_scan
FROM pg_stat_user_tables
WHERE relname = 'your_table_name';
The output will return two timestamps. If you need to find all tables that haven’t been used in a long time, you can sort the results by these fields. For more on viewing data about your relations, see Finding your tables in PostgreSQL.
Method 2: Monitoring Modification and Maintenance Activity
Usage isn’t just about reading data; it’s also about when data was last inserted or when the system last performed “housekeeping” tasks like autovacuum.
Command to Check Maintenance:
SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;
This query shows the last time the table was manually or automatically cleaned. Regular vacuuming is essential to prevent performance issues and data loss due to transaction ID wraparound. To ensure you have the rights to run these queries, refer to the PostgreSQL GRANT Statement: A Complete Guide.
Method 3: Checking Index-Specific Usage
Sometimes a table might be accessed, but certain indexes remain untouched. You can check usage at the index level using pg_stat_user_indexes.
SQL Command:
See also: Mastering the Linux Command Line — Your Complete Free Training Guide
SELECT indexrelname, last_idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'your_table_name';
This is useful for identifying redundant indexes that add overhead to INSERT and UPDATE operations but never benefit SELECT queries. To audit which roles can use these objects, see How to view access privileges in PostgreSQL.
Step-by-Step Process to Audit Table Usage
- Enable Statistics Tracking: Ensure
track_countsis set toonin your configuration; otherwise, these views will be empty. - Identify the Schema: Determine which schema your table belongs to, as this affects which view (
all,user, orsys) you should query. - Run the Activity Query: Execute a SELECT against
pg_stat_user_tablesto view thelast_seq_scanandlast_idx_scantimestamps. - Compare against Uptime: Check when the server was last started to ensure the statistics have had enough time to accumulate.
- Interpret NULL Values: A NULL timestamp usually means the table has not been scanned since the statistics were last reset.
Table Usage Statistics Summary
| Column Name | Activity Type | Purpose |
|---|---|---|
last_seq_scan | Read (Full) | Last time a full table scan occurred. |
last_idx_scan | Read (Index) | Last time an index was used for access. |
last_vacuum | Maintenance | Last manual cleanup of dead rows. |
last_analyze | Maintenance | Last time query planner stats were updated. |
n_tup_ins | Write | Total number of rows inserted. |
FAQs
Why is my last_idx_scan always NULL? This can happen if your queries are not using indexes or if the table is so small that the planner prefers a sequential scan.
Do these statistics include uncommitted transactions? Standard cumulative views lag slightly behind actual activity and typically reflect transactions that have completed.
How do I reset these counters manually? You can use the pg_stat_reset() function to clear all statistics for the current database and start fresh monitoring.
