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

On Thu, Jun 5, 2025 at 8:16 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:

> > Thanks for weighing in.
> +1
>
> > In my mind, this feature is for "I'm almost 100% certain this index
> > isn't needed, I want to make sure I'm 100% right in a way that I can
> > quickly fix the ensuing chaos if I'm wrong".
>
> This is the primary use-case. A user performs an ALTER INDEX...
> INVISIBLE, and they monitor the workload and pg_stat_all_indexes
> ( on primary and hot standbys ) until they feel confident enough
> to fully commit to dropping the index. This is the case that many
> users out there want.
>

To be blunt, the users who think they want this either aren't trying to
solve the actual hard problem, or they haven't thought about how this
operation needs to happen that deeply. Don't get me wrong, it would be an
improvement to have some type of mechanism that can move you from almost
100% to 100%, but the real problem is how do you SAFELY get to almost 100%
in the first place? You need to be able to build that confidence through
smaller incremental changes to your production workload, and ALTER INDEX
won't help you with that. In production, you aren't watching to see what
happen with pg_stat_all_indexes, because you will first be watching
pg_stat_activity to see if the plans have flipped in some way that leads to
an overloaded server (extra latency, poor caching effects, extra buffers
usage, etc). And the replicated bit? Sadly someone launched some big DML
operation so you're waiting for that to finish so the "quick rollback" can
actually get to those other servers.

> > I'm also skeptical of the idea that
> > users need a way to add invisible indexes they can then test to see if
> > they are useful because 1) this is basically how indexes already work,
> > meaning if you add an index and it isn't useful, it doesn't get used,
>
> The GUC will be useful for experimentation or for the safer rollout of
> new indexes. For example, an index can be created as INVISIBLE initially,
> and with use_invisible_index, one can observe how the index may impact
> various queries before fully committing to enabling it. Also, if we allow
> an
> index to be INVISIBLE initially, we need to provide the user with this
> GUC; otherwise, I can’t see why a user would want to make an
> index INVISIBLE initially.
>
>
Again, I can squint enough to see the use case, but the risk with indexes
is FAR greater in their removal rather than in adding new ones; and to
whatever degree you think slow rolling out the generally not dangerous
addition of new indexes is, it's an argument that should really speak to
how much more important the ability to slow roll index removal is.

> > and 2) we have an extension (hypopg) which arguably provides this
> > functionality without causing a bunch of i/o, and there isn't nearly
> > the clamor to add this functionality in to core as there is for having
> > a way to "soft drop" indexes.
>
> I have not worked much with HypoPG, but from what I understand,
> it works only at the EXPLAIN level. It is purely an experimentation tool.
> However, the proposed GUC can also be used in more places,
> including, pg_hint_plan ( at least with the SET hint without any changes
> to pg_hint_plan).
>
>
To be clear, the reason I bring up hypopg is that if slow rolling the
addition of indexes was a significant customer problem, we'd have people
clamoring for better tools to do it, and by and large we don't, and I posit
that by and large that's because adding new indexes is not really that
dangerous.

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.

Robert Treat
https://xzilla.net