Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
