Re: Is it possible to keep track of SELECTs?
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/
Re: Is it possible to keep track of SELECTs?
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
Re: Is it possible to keep track of SELECTs?
On Tue, Mar 12, 2024 at 3:30 PM Christophe Pettus wrote: > > On Mar 12, 2024, at 07:15, Dominique Devienne > 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?
Re: Is it possible to keep track of SELECTs?
> On Mar 12, 2024, at 07:15, Dominique Devienne 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.
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] 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
