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.
