From: Matthias Leisi <matthias@leisi.net>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.200.81.1.6\))
Subject: Record last SELECT on a row?
Message-Id: <287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net>
Date: Wed, 17 Dec 2025 08:40:39 +0100
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/287E4DF6-35A2-4062-AEBA-32DB1DE35C5D%40leisi.net>
Precedence: bulk

An application (which we can=E2=80=99t change) is accessing some =
Postgres table, and we would like to record when the rows in that table =
were last read (meaning: appeared in a SELECT result). The ultimate goal =
would be that we can =E2=80=9Eage out=E2=80=9C rows which have not been =
accessed in a certain period of time.

The table contains some ten thousand rows, five columns, and we already =
record created / last updated using triggers. Almost all accesses will =
result in zero, one or very few records returned. Given the modest size =
of the table, performance considerations are not top priority.

If we had full control over the application, we could eg use a function =
to select the records and then update some =E2=80=9Elast read=E2=80=9C =
column. But since we don=E2=80=99t control the application, that=E2=80=99s=
 not an option. On the other hand, we have full control over the =
database, so we could put some other =E2=80=9Eobject=E2=80=9C in lieu of =
the direct table.

Any other ways this could be achieved?

Thanks,
Matthias