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 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.
The GUC component opens the door for additional workflows, such as creating an index as initially invisible (like Sami points out) and testing its performance before making it visible. I originally wasn't thinking it this way, but this demonstrates 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 DDL approach provides the right balance of simplicity and system-wide consistency that most users need, while the GUC still enables 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. Is there something we can do here in the essence of "start small, think big", perhaps?
Thanks
Shayon
