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
