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

Hi Shayon,

On Sat, Jun 21, 2025 at 9:38 PM 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? 
>
> 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