4

Is there any way to find when an index was last created or reindexed in PostgreSQL 12-13?

The docs show a few standard views like pg_stat_user_indexes that track usage statistics for indexes, but I can't find anything that tracks creation/update statistics.

1 Answer 1

5

Creating the index, as well as re-indexing, writes a new row to the system table pg_class. Most DDL commands (including these) are transactional in Postgres. When the Postgres DB server runs with track_commit_timestamp = on, commit timestamps are recorded. See:

Then, this query produces the timestamp when the index was last created or reindexed in Postgres 9.5 or later:

SELECT pg_xact_commit_timestamp(xmin)
FROM   pg_class
WHERE  relname = 'mytbl_pkey';  -- index name

Tracking only starts after the server is restarted with track_commit_timestamp = on, there is no information for older transactions. And the information is not kept indefinitely - it's lost eventually as transaction IDs wrap around, typically after a very long time, but that depends on transactions per time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.