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

On Wed, 16 Jul 2025 at 05:59, Robert Treat <rob@xzilla.net> wrote:
> operational risk within the system. Similarly, the nature of the DDL
> change also requires that all sessions be impacted everywhere at once;
> there is no way to slowly roll the change to some segment of the
> database or some specific workload within the system.

IMO, sounds like your method for safety here is to slowly walk your
bull into the china shop. Wouldn't it be much better to learn where or
confirm the index isn't being used before you go turning it off for
various queries? I'm stumped at why your method for removing the index
amounts to closing your eyes and doing your best to narrow the blast
radius of the trial and error method.

> regards to roll out specifically, the ALTER method is no safer than
> drop index because both use DDL which means they are both open to
> blocking or being blocked by conflicting queries, which increase

Aside from not having to recreate the index, I agree with this part.
It's a genuine concern. If some query switches to a Seq Scan and the
queries to that table start taking a week to execute, then it'll be a
long wait before you can get an AccessExclusiveLock on the table
again. I think our mental models for this differ, however. In my
imagination, I've checked that the index is unused before I disable
it. It seems like in your model, you're going to disable it and
measure the yield of the resulting explosion.

The latest patch seems to be using a ShareUpdateExclusiveLock, so it
looks like those concurrent seq scans won't block making the index
visible again.

My concern with the GUC approach is that:

1. It'll be yet another crappy way to hint what you want the planner
to do. (The other way being enable_* GUCs)
2. There's no plan cache invalidation when changing the GUC.
3. Standby servers may get forgotten about
4. It encourages trial and error methodology for removing indexes
5. All the committers who showed any hints at liking this method have
disappeared from the thread.

My concern with #1 is that when we one day do get query hints, we'll
be left with a bunch of redundant ways to influence planner behaviour.

Maybe you could get the behaviour you want by some additions to
pg_hint_plan. Looking at [1], if query_id could be NULL to apply to
all queries and there was some way of doing "No IndexScan(*
index_name)", would that get you what you want?

David

[1] https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_table.md