patternsqlMinor
Finding timestamp when index was created or reindexed
Viewed 0 times
timestampcreatedfindingwhenwasindexreindexed
Problem
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
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.Solution
Creating the index, as well as re-indexing, writes a new row to the system table
Then, this query produces the timestamp when the index was last created or reindexed in Postgres 9.5 or later:
Tracking only starts after the server is restarted with
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 nameTracking 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.Code Snippets
SELECT pg_xact_commit_timestamp(xmin)
FROM pg_class
WHERE relname = 'mytbl_pkey'; -- index nameContext
StackExchange Database Administrators Q#295207, answer score: 5
Revisions (0)
No revisions yet.