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

Hello,

> On Jul 23, 2025, at 9:43 PM, Michael Paquier <michael(at)paquier(dot)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 when disabling and then dropping an index. As a developer/DBA or person in a similar position, I think my journey for dropping an 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 using feature 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. Which is 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 / impossible task. But it also means that both systems aren't fighting/overriding each other or making it difficult for users to 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’s not clear to me here is, how common / intuitive of a workflow will this be and if it fits easily in the “common use case” 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