Is it possible to keep track of SELECTs?

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Is it possible to keep track of SELECTs?
Date: 2024-03-12 14:15:35
Message-ID: CAFCRh--Ar40hz6rA5HdyS2dmTaMeOPPHm9M-AJDHpuYsYz+YvQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christophe Pettus 2024-03-12 14:29:51 Re: Is it possible to keep track of SELECTs?
Previous Message Nick Renders 2024-03-12 09:57:19 Re: could not open file "global/pg_filenode.map": Operation not permitted