Is it possible to keep track of SELECTs?
Hi,
Our legacy 3-tier backend is adding a feature requested by users,
to keep track of the last-access-time of projects. The primary purpose
is to archive projects which haven't been used (read from) in a while
(offline),
or perhaps move them to a cheaper / slower storage tier (still online, but
slow access).
In our new 2-tier PostgreSQL-based "backend", project = schema, there's no
mid-tier
services we control anymore, only whatever PostgreSQL records about SELECTs.
(I'm assuming other kinds of accesses, like (AUTO or not) VACUUM, are
tracked differently).
The docs at [1]https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-SETUP mentions:
The parameter track_counts controls whether cumulative statistics
are collected about table and index ***accesses***. (emphasis mine)
So is it possible to track the last time a SELECT was performed on some
TABLE?
And how would one go about setting that up, if not ON by default?
Thanks, --DD
[1]: https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-SETUP
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-SETUP
On Mar 12, 2024, at 07:15, Dominique Devienne <ddevienne@gmail.com> wrote:
So is it possible to track the last time a SELECT was performed on some TABLE?
Directly, no. You could periodically sample the various table-level statistics, and conclude that tables that have had some type of scan since the last scan have had a SELECT run against them. It might not be 100% accurate if (for example) you reset the statistics or lose them for some other reason, but it might be sufficient for the application.
On Tue, Mar 12, 2024 at 3:30 PM Christophe Pettus <xof@thebuild.com> wrote:
On Mar 12, 2024, at 07:15, Dominique Devienne <ddevienne@gmail.com>
wrote:
So is it possible to track the last time a SELECT was performed on some
TABLE?
Directly, no. You could periodically sample the various table-level
statistics, and conclude that tables that have had some type of scan since
the last scan have had a SELECT run against them. It might not be 100%
accurate if (for example) you reset the statistics or lose them for some
other reason, but it might be sufficient for the application.
Bummer :(
PostgreSQL tables and indexes are ultimately files.
And there are ways to map them to file names, I've seen in the past.
So isn't it possible, provided the filesystem tracks last access time, to
infer when a table was accessed the last time?
On Tue, 2024-03-12 at 16:34 +0100, Dominique Devienne wrote:
PostgreSQL tables and indexes are ultimately files.
And there are ways to map them to file names, I've seen in the past.
So isn't it possible, provided the filesystem tracks last access time, to infer when a table was accessed the last time?
Sure, but that won't necessarily tell you when the table was last used.
It could be autovacuum or autoanalyze that last accessed your table.
Also, if the data happen to be in shared buffers, the file won't be read.
Using the PostgreSQL statistics is a much better method.
Yours,
Laurenz Albe
On 2024-Mar-12, Dominique Devienne wrote:
So is it possible to track the last time a SELECT was performed on some
TABLE?
Perhaps you could use the pgAudit module for this purpose.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/