Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX
| От | Robert Treat |
|---|---|
| Тема | Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX |
| Дата | |
| Msg-id | CAJSLCQ18YZAQAt0SzNVf2eudj-Pur4vRiX4dpLD-OouV_4AMuQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX (Sami Imseih <samimseih@gmail.com>) |
| Ответы |
Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX
Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX |
| Список | pgsql-hackers |
On Thu, Jun 5, 2025 at 8:16 PM Sami Imseih <samimseih@gmail.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
В списке pgsql-hackers по дате отправления: