The timestamp that dates an unused index

A counter cannot tell you when

“Is anything using this index” is the question that stands between a team and a dropped index, and until PostgreSQL 16 the server could only answer it with a running total. idx_scan says how many times the index has been reached since the counters were last cleared, and a running total on its own carries no information at all: an index that was scanned nine million times before the application was rewritten and never since looks exactly like an index that is scanned constantly. The number is large either way.

The standard workaround was a baseline. Record idx_scan for every index into a table of your own, come back in a month, subtract, and treat anything whose difference is zero as a candidate for removal. It works, and it costs a scheduled job, a table, a retention policy and a month of waiting before the first answer arrives. It also has a failure mode nobody notices until it bites: a counter reset between the two samples makes the difference negative or small, and both readings look like an index nobody uses.

PostgreSQL 16 records the time instead. Per-table statistics gained a column holding when the table was last read by a sequential scan and another holding when it was last reached through any index, and the per-index view gained the second of those per index. The question stops needing a baseline, because a date is already a comparison.

This is recorded under Monitoring in the PostgreSQL 16 release notes as one line about recording statistics on the last sequential and index scans. What the line does not say is what moment the timestamp records, which turns out to be the thing you have to know before you act on it.

The fixture, and the version that has no answer

A table with thirty thousand rows and a secondary index, so there is something to scan two different ways.

CREATE TABLE ledger_entry (id integer PRIMARY KEY, account text, cents bigint);
INSERT INTO ledger_entry SELECT g, 'acct-' || (g % 97), g * 13 FROM generate_series(1, 30000) AS g;
CREATE INDEX ledger_entry_account_idx ON ledger_entry (account);
VACUUM ANALYZE ledger_entry;

CREATE TABLE
INSERT 0 30000
CREATE INDEX
VACUUM

On 15, asking for the timestamp names a column that is not there, and the server says so:

SELECT relname, seq_scan, last_seq_scan, idx_scan, last_idx_scan
FROM pg_stat_user_tables
WHERE relname = 'ledger_entry';

ERROR:  column "last_seq_scan" does not exist
LINE 1: SELECT relname, seq_scan, last_seq_scan, idx_scan, last_idx_...
                                  ^

That is the whole of the 15 experience: two counters, no dates, and a job somewhere that writes them down.

SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE relname = 'ledger_entry';

   relname    | seq_scan | idx_scan 
--------------+----------+----------
 ledger_entry |        2 |        0
(1 row)

On 16, a null that means something specific

Start from a clean slate, because the first useful property of these columns is what they hold before anything has happened.

SELECT pg_sleep(2);
SELECT pg_stat_reset_single_table_counters('ledger_entry'::regclass);
SELECT pg_sleep(2);
SELECT relname, seq_scan, last_seq_scan, idx_scan, last_idx_scan
FROM pg_stat_user_tables
WHERE relname = 'ledger_entry';

 pg_sleep 
----------
 
(1 row)

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

   relname    | seq_scan | last_seq_scan | idx_scan | last_idx_scan 
--------------+----------+---------------+----------+---------------
 ledger_entry |        0 |               |        0 | 
(1 row)

Both timestamps are null, and null here means “not since the counters were cleared”, which is not the same claim as “never”. That distinction is the first thing to get right in a collection layer. An exporter that maps null to zero publishes a Unix epoch, and a panel sorting tables by least-recently-scanned puts every untouched table at the top of the list dated 1970, which looks like a discovery and is an artefact. An exporter that drops the null publishes nothing, which is at least honest, but a dashboard that renders a missing series as a gap will show the same gap for a table nobody has scanned and for a table whose collector stopped running.

Now read the table two different ways, one statement each, and look again.

SELECT count(*) FROM ledger_entry;
SELECT count(*) FROM ledger_entry WHERE account = 'acct-5';
SELECT pg_sleep(2);
SELECT relname, seq_scan, idx_scan,
       last_seq_scan = last_idx_scan AS same_instant,
       to_char(last_idx_scan, 'HH24:MI:SS.US') AS last_idx_scan
FROM pg_stat_user_tables
WHERE relname = 'ledger_entry';

 count 
-------
 30000
(1 row)

 count 
-------
   310
(1 row)

 pg_sleep 
----------
 
(1 row)

   relname    | seq_scan | idx_scan | same_instant |  last_idx_scan  
--------------+----------+----------+--------------+-----------------
 ledger_entry |        1 |        1 | t            | 18:04:15.477971
(1 row)

Two statements, separated in wall-clock time by however long the first one took, and both scans carry the identical timestamp down to the microsecond. That is the part the release note does not mention and the part that decides how you may use the column.

What the timestamp measures

The value is not the moment the scan ran. It is the moment the backend flushed its accumulated statistics to shared memory, and every counter in that flush is stamped with the same instant. A session does not report after each statement; it reports at the end of a transaction, and not more often than roughly once a second. So a session that runs a sequential scan and then an index scan inside the same second contributes one report, and the server records that one report’s time against both.

Three consequences follow, and they are the difference between using this column well and being misled by it.

• The resolution is the reporting interval, not the statement. Two scans a fraction of a second apart are indistinguishable, and there is no ordering to recover between them.
• A scan inside a long transaction is dated at the end of that transaction. A batch job that opens a transaction at midnight, reads a reference table immediately, and commits at four in the morning leaves a timestamp of four in the morning against a read that happened four hours earlier.
• A session that is killed before it reports contributes nothing. The scan happened, the table was used, and no timestamp records it.

None of that makes the column unusable. It makes it a column about days and weeks rather than about seconds, which is exactly the scale at which anybody asks whether an index is still earning its place. Treating it as an audit trail of individual accesses is the misuse to avoid.

The per-index view is the one that matters

The table-level column tells you the table was reached through some index. The per-index view tells you which, and that is the surface a drop decision actually rests on.

SELECT indexrelname, idx_scan, last_idx_scan IS NULL AS never_since_reset
FROM pg_stat_user_indexes
WHERE relname = 'ledger_entry'
ORDER BY indexrelname;

       indexrelname       | idx_scan | never_since_reset 
--------------------------+----------+-------------------
 ledger_entry_account_idx |        1 | f
 ledger_entry_pkey        |        0 | t
(2 rows)

The primary key index shows a null, because the query above reached the table through the secondary index and nothing has looked a row up by identifier. This is the shape of the real answer on a real cluster: most indexes on a busy table carry a recent date, and the interesting rows are the handful that do not.

There is no per-index equivalent of the sequential-scan timestamp, and there could not be, because a sequential scan does not touch an index. If you want to know whether a table is being read at all rather than which path is used, the table-level pair is the one to read, and a table whose sequential timestamp is recent while every index timestamp is old is usually a table whose statistics are stale or whose row count has fallen below the point where the planner bothers.

Before you drop anything

The column removes the need for a baseline; it does not remove the need for judgement. Three checks are worth making on every candidate, and they are the ones that catch the expensive mistakes.

• Counters are per cluster. A read replica keeps its own statistics, and an index used only by reporting queries that run on a standby shows as untouched on the primary. Read the view on every node that serves traffic before concluding anything.
• A counter reset clears the timestamp along with the count, and so does a restore from a base backup or a crash. Check the table’s stats_reset and treat a recent reset as an absence of evidence rather than as evidence of absence.
• Quarterly and annual work is invisible in a monthly window. An index that exists for a year-end reconciliation will have a stale timestamp for eleven months out of twelve.

A constraint-backing index is a separate case and the timestamp says nothing useful about it. A unique index is doing its job every time it prevents a duplicate, and that work is not a scan. The same applies to an index a foreign key relies on, which the planner may never choose for a query and which the server still consults when a referenced row is deleted.

Getting it out of the database without inventing a number

The column is a timestamp and most metric systems only carry numbers, so something has to convert it, and the conversion is where the meaning usually gets lost.

The safe form is an age in seconds computed in the database, not in the exporter, with nulls left out of the result rather than mapped to anything. A query that returns one row per index with its age, skipping the indexes that have never been scanned, gives a series whose absence means “no scan on record” and whose value means what it says. Anything that fills the gap produces a number that looks like a measurement and is not one.

The second decision is which indexes to include at all. A sweep across every index in every database on a large fleet produces a lot of series that will never be looked at, and the interesting set is small: secondary indexes, not backing a constraint, larger than some size worth caring about, on tables that are not tiny. Filtering in the query rather than in the dashboard keeps the cardinality down and keeps the answer readable.

Partitioned tables need a decision of their own. Each partition has its own index and its own counters, so a partitioned table with a year of monthly partitions produces a spread of timestamps rather than one. Old partitions will legitimately show old timestamps because nothing queries last March any more, and rolling the whole set up to the parent hides the one partition that is being scanned constantly. Keeping the partition in the series and reading the newest few is usually what you want.

Finally, take the reading from every node rather than from the writer. The counters are per cluster, and on a fleet with reporting replicas the primary’s answer is systematically wrong in the direction that gets an index dropped. That is the single most expensive mistake this column makes easier to avoid and easier to make.

What it costs to read

Nothing beyond what the server already maintains. The timestamp is written into the same statistics report as the counter beside it, so there is no extra work per scan, no setting to enable and nothing to restart. The view is assembled on request from shared memory.

The one operational cost is the same one every statistics-derived decision carries: you are reading counters whose lifetime is the cluster’s, not the table’s. If your fleet resets statistics on a schedule, these columns reset with them, and the answer to “when was this last used” becomes “not since Sunday” on every table in the database. That is a reason to think carefully about scheduled resets, which a later release made easier to get wrong rather than harder, and a reason to record the reset time alongside whatever you conclude.