feature request: index supported REINDEX for partial indexes on needle/haystack tables

Merlin Moncure <mmoncure@gmail.com>

From: Merlin Moncure <mmoncure@gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: 2026-04-21T17:22:45Z
Lists: pgsql-performance
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