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

On Sat, Jun 21, 2025 at 8:38 AM Shayon Mukherjee <shayonj(at)gmail(dot)com> wrote:

>
>
> On Jun 11, 2025, at 9:00 AM, Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
>
> IMO, having this GUC to force the use of invisible indexes is quite
>> strange. In my view, it detracts from the guarantees that you're meant
>> to get from disabling indexes. What if some connection has
>> use_invisible_index set to true? The DBA might assume all is well
>> after having seen nobody complain and then drop the index. The user
>> might then complain.
>>
>
> Sure, this may occur. I can also imagine cases where an index is made
> visible only for certain workloads, intentionally. But such efforts should
> be coordinated by application teams and DBAs. Someone would need to modify
> this GUC at the connection level, alter the database, or change the session
> via application code. An ad-hoc connection enabling this GUC is unlikely to
> be an issue.
>
> I don't see how we could provide the INVISIBLE index DDL without also
> providing this boolean GUC. If a user creates an index that is initially
> INVISIBLE, they need a GUC to try it out before deciding to make it
> visible.
>
> It was also pointed out in the thread above that this GUC can serve as a
> backstop for replicas if the DDL to make an index visible is delayed.
>
>
> Hello,
>
> Thank you everyone for all the discussions and also to Robert Treat for
> feedback and the operational considerations.
>
> It seems like there are multiple ways to solve this problem, which is
> encouraging. From the discussion, there appears to be consensus on few
> things as well, including the DDL approach, which I personally am a
> proponent for as well.
>
> I believe this is a valuable feature for DBAs and engineers working with
> large databases. Esp since it provides the confidence to "turn off" an
> index to observe the impact through their observability tools and make an
> informed decision about whether to drop it. If they're wrong, they can
> quickly rollback by making the index visible again, rather than waiting for
> a full index rebuild that can take 30 minutes to hours.
>
> The primary use case I have in mind is for helping engineers (ones not so
> seasoned like DBAs) decide whether to drop *existing* indexes. For new
> indexes, I expect most users would create them in visible mode (the
> default). Or so has been my experience so far.
>

What I would be using this for is when the server is choosing the wrong
index, often in multi column index scenarios. The server can be obtuse in
those situations. So I see this as a query optimization aid rather than a
'should I drop this?' Given that there are several ways to do that
already. I can see scenarios where I'd want the index backed constraint
to never be used for some/all queries.

ALTER driving this seems ok. It seems more of a planner directive to me
but having potential permanent configuration (vs mostly temporary needs)
tips the scale IMO.

ENABLE | DISABLE seems off. I would take it further to, ENABLE | DISABLE
OPTIMIZATION for clarify and to leave room for syntax expansion.

Nice stuff. Did not review patch

merlin