public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Merlin Moncure <mmoncure@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: feature request: index supported REINDEX for partial indexes on needle/haystack tables
Date: Tue, 21 Apr 2026 11:22:45 -0600
Message-ID: <CAHyXU0yedXJ9EwXRyKMw2ddfSf1cGWJNAnTZn_SDz73SLvK4sw@mail.gmail.com> (raw)
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mmoncure@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: feature request: index supported REINDEX for partial indexes on needle/haystack tables
In-Reply-To: <CAHyXU0yedXJ9EwXRyKMw2ddfSf1cGWJNAnTZn_SDz73SLvK4sw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox