Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX - Mailing list pgsql-hackers

On Sat, Jun 21, 2025 at 7:37 AM Shayon Mukherjee <shayonj@gmail.com> wrote:
> On Jun 11, 2025, at 9:00 AM, Sami Imseih <samimseih@gmail.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
tobe 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
confidenceto "turn off" an index to observe the impact through their observability tools and make an informed decision
aboutwhether to drop it. If they're wrong, they can quickly rollback by making the index visible again, rather than
waitingfor 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
myexperience so far. 
>
> The GUC component opens the door for additional workflows, such as creating an index as initially invisible (like
Samipoints out) and testing its performance before making it visible. I originally wasn't thinking it this way, but
thisdemonstrates the flexibility of the feature and accommodates different development approaches. 
>
> As Robert noted, both approaches have trade-offs around operational safety and granular control. However, I think the
DDLapproach provides the right balance of simplicity and system-wide consistency that most users need, while the GUC
stillenables experimentation for those who want it. 
>
> I'm very much committed to iterating on this patch to address any remaining feedback and help make progress on this.
Isthere something we can do here in the essence of "start small, think big", perhaps? 
>

Glad to hear you are still interested, slightly disheartened by the
general lack of concern around operational safety in this thread. I
actually think what you have done covers a lot of the ground for
multiple implementations, so I'm optimistic we can get something for
19.

I was thinking about this some more over the weekend and it does seem
like you can't get away from doing something with DDL; even though it
is the wrong mental model... like when your AC is running but you
don't think it is cool enough, so you turn it down farther, as if it
would blow colder air... but that isn't how AC actually work... it
seems you can't eliminate the desire for this mental model entirely.
Which to be clear, I am not against, it's just a bad tool for the hard
cases, but not in every case. Anyway, if I were picking this up, I
would separate out the two ideas; as I laid out in my email to David,
the GUC solution can stand on it's own without the DDL implementation,
and I would do that first, and then add a simplified DDL
implementation after the fact. Of course it could be done the other
way around, but I think you're more likely to land on the correct GUC
implementation if it isn't mixed up with DDL, and the best way to
assure that is by not having the DDL for the initial patch. Just my
.02, but happy to help spec it out further.


Robert Treat
https://xzilla.net