pgstat: Track time of the last scan of a relation

Enterprise / PostgreSQL - Andres Freund [anarazel.de] - 14 October 2022 18:11 UTC

It can be useful to know when a relation has last been used, e.g., when evaluating whether an index is still required. It was already possible to infer the time of the last usage by tracking, e.g., pg_stat_all_indexes.idx_scan over time. But far from everybody does so.

To make it easier to detect the last time a relation has been scanned, track that time in each relation's pgstat entry. To minimize overhead a) the timestamp is updated only when the backend pending stats entry is flushed to shared stats b) the last transaction's stop timestamp is used as the timestamp.

Bumps catalog and stats format versions.

Author: Dave Page

c037471832 pgstat: Track time of the last scan of a relation
doc/src/sgml/monitoring.sgml | 30 ++++
src/backend/catalog/system_views.sql | 3 +
src/backend/utils/activity/pgstat_relation.c | 6 +
src/backend/utils/adt/pgstatfuncs.c | 13 ++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 4 +
src/include/pgstat.h | 3 +-
src/test/regress/expected/rules.out | 9 ++
src/test/regress/expected/stats.out | 202 +++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 86 ++++++++++++
10 files changed, 356 insertions(+), 2 deletions(-)

Upstream: git.postgresql.org


  • Share