Subject: Proposal to Enable/Disable Index using ALTER INDEX

From: Shayon Mukherjee <[email-masked]>
Date: Mon, 9 Sep 2024 17:38:35 -0400

Hello hackers,

This is my first time posting here, and I’d like to propose a new feature
related to PostgreSQL indexes. If this idea resonates, I’d be happy to
follow up with a patch as well.

*Problem*:
Adding and removing indexes is a common operation in PostgreSQL. On larger
databases, however, these operations can be resource-intensive. When
evaluating the performance impact of one or more indexes, dropping them
might not be ideal since as a user you may want a quicker way to test their
effects without fully committing to removing & adding them back again.
Which can be a time taking operation on larger tables.

*Proposal*:
I propose adding an ALTER INDEX command that allows for enabling or
disabling an index globally. This could look something like:

ALTER INDEX index_name ENABLE;
ALTER INDEX index_name DISABLE;

A disabled index would still receive updates and enforce constraints as
usual but would not be used for queries. This allows users to assess
whether an index impacts query performance before deciding to drop it
entirely.

*Implementation*:
To keep this simple, I suggest toggling the indisvalid flag in pg_index
during the enable/disable operation.

*Additional Considerations*:
- Keeping the index up-to-date while it’s disabled seems preferable, as it
avoids the need to rebuild the index if it’s re-enabled later. The
alternative would be dropping and rebuilding the index upon re-enabling,
which I believe would introduce additional overhead in terms of application
logic & complexity.
- I am also proposing to reuse the existing indisvalid flag to avoid adding
new state and the maintenance that comes with it, but I’m open to feedback
if this approach has potential downsides.
- To keep the scope minimal for now, I propose that we only allow enabling
and disabling indexes globally, and not locally, by supporting it
exclusively in ALTER INDEX. I would love to know if this would break any
SQL grammar promises that I might be unaware of.

I would love to learn if this sounds like a good idea and how it can be
improved further. Accordingly, as a next step I would be very happy to
propose a patch as well.

Best regards,
Shayon Mukherjee

---

From: David Rowley <[email-masked]>
Date: Tue, 10 Sep 2024 10:16:34 +1200

On Tue, 10 Sept 2024 at 09:39, Shayon Mukherjee <[email-masked]> wrote:
> Adding and removing indexes is a common operation in PostgreSQL. On larger databases, however, these operations can be resource-intensive. When evaluating the performance impact of one or more indexes, dropping them might not be ideal since as a user you may want a quicker way to test their effects without fully committing to removing & adding them back again. Which can be a time taking operation on larger tables.
>
> Proposal:
> I propose adding an ALTER INDEX command that allows for enabling or disabling an index globally. This could look something like:
>
> A disabled index would still receive updates and enforce constraints as usual but would not be used for queries. This allows users to assess whether an index impacts query performance before deciding to drop it entirely.

I personally think having some way to alter an index to stop it from
being used in query plans would be very useful for the reasons you
mentioned.  I don't have any arguments against the syntax you've
proposed.  We'd certainly have to clearly document that constraints
are still enforced. Perhaps there is some other syntax which would
self-document slightly better. I just can't think of it right now.

> Implementation:
> To keep this simple, I suggest toggling the indisvalid flag in pg_index during the enable/disable operation.

That's not a good idea as it would allow ALTER INDEX ... ENABLE; to be
used to make valid a failed concurrently created index.  I think this
would need a new flag and everywhere in the planner would need to be
adjusted to ignore indexes when that flag is false.

> Additional Considerations:
> - Keeping the index up-to-date while it’s disabled seems preferable, as it avoids the need to rebuild the index if it’s re-enabled later. The alternative would be dropping and rebuilding the index upon re-enabling, which I believe would introduce additional overhead in terms of application logic & complexity.

I think the primary use case here is to assist in dropping useless
indexes in a way that can very quickly be undone if the index is more
useful than thought. If you didn't keep the index up-to-date then that
would make the feature useless for that purpose.

If we get the skip scan feature for PG18, then there's likely going to
be lots of people with indexes that they might want to consider
removing after upgrading. Maybe this is a good time to consider this
feature as it possibly won't ever be more useful than it will be after
we get skip scans.

David

---

From: Nathan Bossart <[email-masked]>
Date: Tue, 10 Sep 2024 10:12:22 -0500

> feature as it possibly won't ever be more useful than it will be after

+1, this is something I've wanted for some time.  There was some past
discussion, too [0].

[0] https://postgr.es/m/flat/ed8c9ed7-bb5d-aaec-065b-ad4893645deb%402ndQuadrant.com

-- 
nathan

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 23 Sep 2024 17:14:15 +0200

On 09.09.24 23:38, Shayon Mukherjee wrote:
> *Problem*:
> Adding and removing indexes is a common operation in PostgreSQL. On 
> larger databases, however, these operations can be resource-intensive. 
> When evaluating the performance impact of one or more indexes, dropping 
> them might not be ideal since as a user you may want a quicker way to 
> test their effects without fully committing to removing & adding them 
> back again. Which can be a time taking operation on larger tables.
> 
> *Proposal*:

I think a better approach would be to make the list of disabled indexes 
a GUC setting, which would then internally have an effect similar to 
enable_indexscan, meaning it would make the listed indexes unattractive 
to the planner.

This seems better than the proposed DDL command, because you'd be able 
to use this per-session, instead of forcing a global state, and even 
unprivileged users could use it.

(I think we have had proposals like this before, but I can't find the 
discussion I'm thinking of right now.)