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

> In that type of environment, the GUC-only method enables you to
> control changes at very precise levels, so you can do things like:
> - run it ad-hoc at the session level to confirm that the explain plans
> you get in production match your expectations.
> - you can stay ad-hoc at the session level and run explain analyze and
> confirm acceptable performance within your workload, and see what kind
> of buffer impact you are going to have (typically overlooked, but a
> potential landmine for outages, but I'll come back to this)
> - because we are operating at the session level, we can then add this
> on a per query basis at the application level, and in really high
> traffic scenarios, you can use canary releases and/or feature flags to
> ramp up those new queries into the live system.
> - depending on how much risk you are concerned about, you can use this
> session level method across queries individually, or at some point
> roll it up to a user/application level. And again, we can roll it out
> to different users at different times if you want.
> - at some point when you feel confident that you have covered enough
> angles, you set the GUC globally and let that marinate for a few more
> weeks as needed.

Do we need this level of granular control in core, or should this be
delegated to other tools in the ecosystem, like pg_hint_plan? The de
facto tool for influencing planning.
There is probably some work that must happen in that extension to make
the use-cases above work, but it is something to consider.

With that said, I am not really opposed to a multi-value GUC that takes
in a list of index names, but I do have several concerns with that
approach being available in core:

1. The list of indexes getting too long, and the potential performance
impact of having to translate the index name to a relid to find which
index to make "invisible". I don't think a list of index relids will
be good from a usability perspective either.

2. A foot-gun such as adding an index name to my list, dropping the
index, recreating it with the same name, and now my new index is not
being used.

3. not sync'd up with the replica, so manual work is required there. That
could be seen as a positive aspect of this approach as well.

4. The above points speak on the level of maintenance required for this.

> You mentioned that one of the things you liked about the ALTER/guc method
> is that it replicates the changes across all systems which makes it
> easy to revert, however I believe that thinking is flawed. For
> starters, any change that has to occur across the WAL stream is not
> something that can be relied on to happen quickly; there are too many
> other items that traverse that space that could end up blocking a
> rollback from being applied in a timely fashion.

This is not going to be unique to this feature though. Other critical
DDLs will be blocked, so this is a different problem, IMO.

> but it also crystalized my
> feeling that an Oracle-style implementation would be a red herring
> that can keep us from a better solution.

Going back to this point, I still think that the ALTER option is useful
after the user's confidence is near 100% and they are ready to drop
the index for good, and which also gets replicated.

The GUC is useful for experimentation or for users that want to do a
slow rollout of dropping an index. We can discuss whether this should
be a multi-value setting or a boolean in core, or if it should be
delegated to an extension.

Essentially, I don't think we need to choose one or the other, but
perhaps we can improve upon the GUC.

--
Sami