There is a way with Postgres 9.5 or later. Turn on
postgresql.conf and restart the DB cluster. Starts logging commit timestamps.
Then you can get the timestamp of the latest modification (latest commit) for a given table using the function
SELECT pg_xact_commit_timestamp(t.xmin) AS modified_ts FROM my_table t ORDER BY modified_ts DESC NULLS LAST LIMIT 1;
NULLS LAST is necessary while there still may be rows without recorded commit timestamp.
- PostgreSQL sort by datetime asc, null first?
- Row with latest timestamp
- Is there way to get transaction commit timestamp in Postgres?
For Postgres 9.4 or older, see:
Improve this answerFollow
answered Apr 19, 2019 at 0:55
156k1919 gold badges384384 silver badges527