Finding timestamp when index was created or reindexed

Question

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.

Answer 1

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:

• How do I write a Postgres SQL command based on metadata of the tables themselves?

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.