Re: [PATCH] Proposal to Enable/Disable Index using ALTER INDEX

Hello,

> On Jul 23, 2025, at 9:43 PM, Michael Paquier <michael@paquier.xyz> wrote:
>
> On Tue, Jul 22, 2025 at 01:15:16PM -0500, Sami Imseih wrote:
>> The GUC serves multiple purposes. For example,I can create an index as invisible
>> and use it in a controlled way, which is helpful for experimenting
>> with a new index.
>
> An in-core GUC to control the list of indexes that should be allowed
> or disallowed is I think asking for trouble, adding schema-related
> knowledge directly into the GUC machinery.  This does not scale well,
> even if you force all the entries to be specified down to the database
> and the schema.  And it makes harder to control what a "good" behavior
> should be at query-level.
>
> My 2c.

+1

I wonder if there's a path to simplify things further here while still providing a way to gradually build confidence
whendisabling and then dropping an index. As a developer/DBA or person in a similar position, I think my journey for
droppingan index in this new world would look something like this: 

1. Research phase: Use `pg_stat_user_indexes`, query analysis to understand index usage
2. Experimentation phase: Use `pg_hint_plan` (or GUC?) for session-level testing and slower rollout from applications
usingfeature flags 
  - Up until a while ago, this step won't exist because once I had enough confidence from step 1, I'd go to step 3.
Whichis a huge improvement from jumping to Step 4 below. But the new discussions have made me think that this step is
important.
3. Validation phase: Use `ALTER INDEX INVISIBLE` for final system-wide confidence building
4. Cleanup phase: `DROP INDEX` when certain

Per this plan, this would mean that pg_hint_plan would need to support index-level hints, and it’s not a massive /
impossibletask. But it also means that both systems aren't fighting/overriding each other or making it difficult for
usersto understand when exactly an index is being used or not. Ultimately, this would also mean that `ALTER INDEX
INVISIBLE`is a one-way door, and there is only one way to control index visibility in core, which makes sense to me. 

I think any pitfalls and guarantees can be communicated well through documentation both in core and in `pg_hint_plan`.
What’snot clear to me here is, how common / intuitive of a workflow will this be and if it fits easily in the “common
usecase” path? 

There are some aspects of the GUC approach that I'd miss, also because as a developer I've used DDLs and GUCs more than
pg_hint_plan,but it's probably just a tooling exposure thing perhaps.  

Curious what folks think.

P.S. Still very happy to help with patches whenever that is.

Thanks,
Shayon