Thread

Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

Robert Treat <rob@xzilla.net> — 2025-07-21T20:29:56Z

On Mon, Jul 21, 2025 at 1:17 PM Sami Imseih <samimseih@gmail.com> wrote:
>
> > it will still be extremely risky in
> > heavy production workloads. In short, we're both walking a bull
> > through the china shop, but it would seem mine is much more
> > temperamental than yours.
>
> Robert, Could you describe the GUC you would like to see?
>
> Also, I'd like to ask. what would be the argument against offering both options,
> ALTER and a GUC to override the catalog, as currently proposed in the patch?
>
> This conversation has been mainly GUC is better than ALTER, or vice versa.
>
> It is clear, at least to me, there are merits in both approaches, so
> what would be
> the argument against making both options available ( maybe with a GUC that
> could be more useful than a simple boolean )?
>

Just to reiterate, I am not against having both a GUC and ALTER
option, if implemented correctly. Like David, I don't have good
feelings about the ALTER / use_invisible_index GUC overwriting
behavior that is explicitly written in the catalog, and I see no
reason to settle for a technically awkward solution when I think it
also delivers a poor user interface that will be hard to reason about
and/or debug in production.

So I think the "right" interface looks something like a GUC that would
be something like "ignore_index_planning" which takes a csv list of
index names that the planner would ignore. On its own, this provides
as much flexibility as we can offer when attempting to change index
visibility, since it would be set global/local/etc, and could be set
on some, none, or some combo thereof within replica tree environments.
You can make that convoluted, but it is operating like other GUCs.

This also seems compatible with the implementation approach discussed
by PeterE, Tom, and Haas earlier upthread (1)(2) with regard to
providing a list of names and filtering them out. (There could be
other ways of implementing it, but this certainly seems to cover a lot
of the ground we'd want covered).

I know one concern of this method is that this could introduce some
parsing overhead if people choose to use large lists of indexes, but I
think that's generally ok as long as it is documented. Our typical use
case is expected to be one or maybe a few at most, indexes, but if
people feel strongly they need to run with dozens and dozens of
indexes listed, there will be a trade off, similar to other GUCs/tools
(think track_activity_query_size or adding pg_stat_statements, or even
wildly long search_paths).

This also covers some of the more esoteric use cases, such as wanting
to "turn off" indexes for mixed workload replica trees, and covers the
often mentioned use case of allowing an index to be created
"invisible" by default (just add the proposed index name to the list
before creation).

And I'll also mention that this seems like the method least likely to
conflict with an ALTER INDEX implementation if we want to add one down
the line (I think there is an argument for it), since I imagine that
you could create such a thing with a boolean catalog flag that mimics
the gucs behavior, so that the GUC or catalog aren't trying to
override each other. Of course I'm tempted to say you could maybe
implement this like an index storage parameter, but that might be a
bridge too far... still if we make the GUC first, that would certainly
be an interesting idea to explore.


1) https://www.postgresql.org/message-id/15238d97-f667-48df-8319-ab73b37d4511%40eisentraut.org
2) https://www.postgresql.org/message-id/3465209.1727202064%40sss.pgh.pa.us


Robert Treat
https://xzilla.net