MIME-Version: 1.0
From: Raghav Mittal <mittalr086@gmail.com>
Date: Wed, 1 Apr 2026 07:52:49 +0530
Message-ID: 
 <CA+dbf3gvWCeF8j_DBjtaPcNmm=E9d_Z7sPdaJRuanGfAw2Rvow@mail.gmail.com>
Subject: Proposal: Track last-used timestamp for index usage
To: pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000056c995064e5cc33b"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bdbf3gvWCeF8j_DBjtaPcNmm%3DE9d_Z7sPdaJRuanGfAw2Rvow%40mail.gmail.com>
Precedence: bulk

--00000000000056c995064e5cc33b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi all,

I=E2=80=99ve been exploring index usage statistics in PostgreSQL and notice=
d that
pg_stat_user_indexes only provides cumulative counters (idx_scan), but not
recency information.

Problem:
- Counters reset on restart or pg_stat_reset()
- No way to determine when an index was last used
- Makes it hard to safely identify unused indexes

Proposal:
Introduce lightweight tracking of last-used timestamp for indexes.

Possible approach:
- Mark index usage in executor (cheap signal)
- Background worker periodically updates last_used_at
- Avoids overhead in query execution path

Questions:
1. Has this problem been explored before?
2. Are there known concerns with adding timestamp tracking to stats?
3. Would this be acceptable as an extension vs core feature?

Happy to prototype this if there=E2=80=99s interest.

Thanks!

--00000000000056c995064e5cc33b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi all,<br><br>I=E2=80=99ve been exploring index usag=
e statistics in PostgreSQL and noticed that<br>pg_stat_user_indexes only pr=
ovides cumulative counters (idx_scan), but not<br>recency information.<br><=
br>Problem:<br>- Counters reset on restart or pg_stat_reset()<br>- No way t=
o determine when an index was last used<br>- Makes it hard to safely identi=
fy unused indexes<br><br>Proposal:<br>Introduce lightweight tracking of las=
t-used timestamp for indexes.<br><br>Possible approach:<br>- Mark index usa=
ge in executor (cheap signal)<br>- Background worker periodically updates l=
ast_used_at<br>- Avoids overhead in query execution path<br><br>Questions:<=
br>1. Has this problem been explored before?<br>2. Are there known concerns=
 with adding timestamp tracking to stats?<br>3. Would this be acceptable as=
 an extension vs core feature?<br><br>Happy to prototype this if there=E2=
=80=99s interest.<br><br>Thanks!</div></div>

--00000000000056c995064e5cc33b--