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

On Thu, Jun 5, 2025 at 7:32 PM Robert Treat <rob(at)xzilla(dot)net> wrote:

> I'm not saying there isn't any possible use case that could be solved with
> the above (although mind my example of people running with all indexes and
> the guc always enabled; I don't think thats a sceanrio that anyone thinks
> should be recommended, but it will be a far more common use case given this
> design; and btw it wont work well with pg_hint_plan because the GUC/ALTER
> combo doesn't play well with multiple indexes), but more importantly, if we
> only solve the simple cases at the expense of the hard problem, we're doing
> our users a disservice.
>
>
So, as proposed:

Replicate-able DDL: Enables a holistic picture but is a foot-gun for the
DBA in the "revert" case.

Boolean GUC: Enables some experimentation; can be used to quickly re-enable
invisible indexes that are waiting for the DDL to make them visible again.
Insufficiently granular for quickly exploring various options.

The granularity issue seems overcome-able:

Multi-Valued GUC: Specify explicitly which invisible indexes to make
visible, eliminating the granularity problem of the boolean option. Can
provide a "pg_catalog.pg_all_indexes" magic value impossible to exist in
reality that would enable the "true" boolean option - false would just be
an empty setting.

The foot-gun seems safe enough to offer given the benefit the feature
provides.

Even without the GUC the proposed feature seems an improvement over the
status quo. The boolean I'd probably leave on the table; while a bit ugly
in usage for the expected experimentation the multi-valued text GUC seems
reasonable (and will effectively prohibit relying on invisible indexes
generally).

Are there other alternative designs this last bit of discussion is meant to
promote or are people actively voting for the status quo over the addition
of the index visibility attribute? Or, maybe more properly, is index
replication the dividing line here and any new feature has to make that
aspect optional?

If we are going to bite on the multi-valued text GUC it could just define
which indexes to ignore when planning and we'd have the local-only feature
done. Which leads then to just implementing this feature (with
multi-valued GUC) as the option by which the DBA can choose to apply their
local GUC changes across their secondaries without having to (but can if
they wish) apply the GUC change to all those machines as well.

David J.