MIME-Version: 1.0
From: Merlin Moncure <mmoncure@gmail.com>
Date: Tue, 21 Apr 2026 11:22:45 -0600
Message-ID: 
 <CAHyXU0yedXJ9EwXRyKMw2ddfSf1cGWJNAnTZn_SDz73SLvK4sw@mail.gmail.com>
Subject: feature request: index supported REINDEX for partial indexes on
 needle/haystack tables
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b81188064ffbaad3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHyXU0yedXJ9EwXRyKMw2ddfSf1cGWJNAnTZn_SDz73SLvK4sw%40mail.gmail.com>
Precedence: bulk

--000000000000b81188064ffbaad3
Content-Type: text/plain; charset="UTF-8"

Suppose you have an index:

CREATE INDEX ON huge_table WHERE something_transient;

...where something_transient represents a small amount records (the needle)
at any point in time, but touches many records up to the next REINDEX.
This is a common pattern in queue type processing tables.

The basic issue here is index bloat, which is a well understood problem.
 However, there's an opportunity to have the index support its own rebuild,
since it can limit the number of records that need to be scanned.
If "something_transient" represents zero or near zero records relative to
the table's size, the REINDEX ought to be quite fast...almost a "TRUNCATE
INDEX" if the something_transient set is empty.

merlin

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

<div dir=3D"ltr">Suppose you have an index:<div><br></div><div>CREATE INDEX=
 ON huge_table WHERE something_transient;</div><div><br></div><div>...where=
 something_transient represents a small amount records (the needle) at any =
point in time, but touches many records up to the next REINDEX.=C2=A0 This =
is a common pattern in queue type processing tables.</div><div><br></div><d=
iv>The basic issue here is index bloat, which is a well understood problem.=
=C2=A0 =C2=A0However, there&#39;s an opportunity to have the index support =
its own rebuild, since it can limit the number=C2=A0of records=C2=A0that ne=
ed to be scanned. If=C2=A0&quot;something_transient&quot; represents zero o=
r near zero records relative to the table&#39;s size, the REINDEX ought to =
be quite fast...almost a &quot;TRUNCATE INDEX&quot; if the something_transi=
ent set is empty.=C2=A0</div><div><br></div><div>merlin</div><div><br></div=
></div>

--000000000000b81188064ffbaad3--