Hi Shayon, On Sat, Jun 21, 2025 at 9:38 PM 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. > > 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 >
Based on your analysis, I think the patch could be split into two parts: one focusing on the DDL approach and the other on the additional GUC control. >From reading the discussions, it seems that the GUC control depends on the DDL approach (eg. creating an index as initially invisible and making it visible later). Therefore, maybe the DDL approach can be committed first and extend the GUC control later as needed? I read the v18 patch, I think the following changes should not be included: diff --git a/src/interfaces/ecpg/test/regression.diffs b/src/interfaces/ecpg/test/regression.diffs new file mode 100644 index 0000000000..e69de29bb2 diff --git a/src/interfaces/ecpg/test/regression.out b/src/interfaces/ecpg/test/regression.out new file mode 100644 index 0000000000..cb633f4d71 --- /dev/null +++ b/src/interfaces/ecpg/test/regression.out @@ -0,0 +1,55 @@ +# initializing database system by copying initdb template +# using temp instance on port 65312 with PID 30031 +ok 1 - compat_informix/dec_test 563 ms +ok 2 - compat_informix/charfuncs 255 ms +ok 3 - compat_informix/rfmtdate 355 ms -- Regards Junwang Zhao
