On Sat, Jun 21, 2025 at 8:38 AM Shayon Mukherjee <shay...@gmail.com> wrote:
> > > On Jun 11, 2025, at 9:00 AM, Sami Imseih <samims...@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. > 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
