Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vVuZV-009eO1-1J for pgsql-general@arkaria.postgresql.org; Wed, 17 Dec 2025 16:40:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vVuZT-00EawS-2f for pgsql-general@arkaria.postgresql.org; Wed, 17 Dec 2025 16:40:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vVuZT-00EawK-1b for pgsql-general@lists.postgresql.org; Wed, 17 Dec 2025 16:40:16 +0000 Received: from fout-b6-smtp.messagingengine.com ([202.12.124.149]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vVuZR-001GJv-2s for pgsql-general@lists.postgresql.org; Wed, 17 Dec 2025 16:40:15 +0000 Received: from phl-compute-02.internal (phl-compute-02.internal [10.202.2.42]) by mailfout.stl.internal (Postfix) with ESMTP id 2C3551D00160; Wed, 17 Dec 2025 11:40:12 -0500 (EST) Received: from phl-frontend-04 ([10.202.2.163]) by phl-compute-02.internal (MEProxy); Wed, 17 Dec 2025 11:40:12 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1765989612; x=1766076012; bh=sjYBEFZHrTX7fxKE1/Biwy/shxQ12TV6kuwz01GBYeE=; b= gXGXgIqQVcqksY/O2KNFoNCvfMpREmdUK6ecV48GbWiwf4cMysFzZgD3nacVSlJ+ tByEb7pQ+bynMkQzk2tYBU01zEqKHhwvG4JeODgrsmK0pJ2yqZwFB4H/C9sFxpax VRzqUmDChIHjcboIlAigucH38ifBwdgWDjlYkf6fkVIKo1LJLKFzbu9cs4up9iCc J9d5Jn1iyrawHYozlRLvLp+pflrAmo08v/s5Nrpm9j8mO5uJde51l8199703RLhl DNcub+I5XF3DPRIc8wgWhDVjxijcR7glPDLNE4DOoNyriWZAlMcGM1L1nK15KuBW /MiAKb2nbMgIpW4Suk/SCQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1765989612; x=1766076012; bh=s jYBEFZHrTX7fxKE1/Biwy/shxQ12TV6kuwz01GBYeE=; b=O6JYcDo9ln7Vpfo4C S7dpqvIsNLNshHoaqz2pq96ylN8wmoZkQZr5sZCYmzag+bFlpZsOTExSWAWqNYjF 6ozO7RYGO6/d68cRm4Q/RYrHoGt0jNOpyOH91e7Z/+vArasmu2L7F0ZcJzWf/vv3 XYxkTAsXLjCVSC+ikifapIJpJNc/6hvu4LVpe3gnWqazSRcQWCoVqsITk1NqH6rI cIzKyhma3qIVsvmRwMjFcA/WrI6M/rfAegB1QxjpldmhvBC67M8E2d8bHyhfEHD6 95xiW5rXK9fvo5QwCYO5Nso8rTX4cEDdWCYovpOTYP7nZr5+vo2pP1SjLrLU1oE7 Xm1wA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgdegfedtiecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpuffrtefokffrpgfnqfghnecuuegr ihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenucfjug hrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughrihgrnhcu mfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomheqne cuggftrfgrthhtvghrnhepffelgeeifefgveduhedthfekuedtffejveegffegjeevtdeh gfduieetfeehjeehnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilh hfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomhdpnhgspghr tghpthhtohepvddpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtohepmhgrthhthhhirg hssehlvghishhirdhnvghtpdhrtghpthhtohepphhgshhqlhdqghgvnhgvrhgrlheslhhi shhtshdrphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 17 Dec 2025 11:40:11 -0500 (EST) Message-ID: Date: Wed, 17 Dec 2025 08:40:10 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Record last SELECT on a row? To: Matthias Leisi , "pgsql-generallists.postgresql.org" References: <287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net> Content-Language: en-US From: Adrian Klaver In-Reply-To: <287E4DF6-35A2-4062-AEBA-32DB1DE35C5D@leisi.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/16/25 23:40, Matthias Leisi wrote: > An application (which we can’t 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 „age out“ rows which have not been accessed in a certain period of time. Why? Given the small size of the table, what is the gain expected? Also is it assured that the reading of a row equals importance of a row? I would expect any solution would impose more overhead then simply leaving the rows alone. > > 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 „last read“ column. But since we don’t control the application, that’s not an option. On the other hand, we have full control over the database, so we could put some other „object“ in lieu of the direct table. > > Any other ways this could be achieved? > > Thanks, > Matthias > > > -- Adrian Klaver adrian.klaver@aklaver.com