MIME-Version: 1.0
From: Shayon Mukherjee <shayonj@gmail.com>
Date: Mon, 9 Sep 2024 17:38:35 -0400
Message-ID: <CANqtF-oXKe0M=0QOih6H+sZRjE2BWAbkW_1+9nMEAMLxUJg5jA@mail.gmail.com>
Subject: Proposal to Enable/Disable Index using ALTER INDEX
To: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f1375e0621b694f4"
Archived-At: <https://www.postgresql.org/message-id/CANqtF-oXKe0M%3D0QOih6H%2BsZRjE2BWAbkW_1%2B9nMEAMLxUJg5jA%40mail.gmail.com>
Precedence: bulk

--000000000000f1375e0621b694f4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello hackers,

This is my first time posting here, and I=E2=80=99d like to propose a new f=
eature
related to PostgreSQL indexes. If this idea resonates, I=E2=80=99d 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=E2=80=99s disabled seems preferable=
, as it
avoids the need to rebuild the index if it=E2=80=99s 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=E2=80=99m open to f=
eedback
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

--000000000000f1375e0621b694f4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello hackers,<br><br>This is my first time posting here, =
and I=E2=80=99d like to propose a new feature related to PostgreSQL indexes=
. If this idea resonates, I=E2=80=99d be happy to follow up with a patch as=
 well.<br><br><b>Problem</b>:<br>Adding and removing indexes is a common op=
eration in PostgreSQL. On larger databases, however, these operations can b=
e 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 q=
uicker way to test their effects without fully committing to removing &amp;=
 adding them back again. Which can be a time taking operation on larger tab=
les.<br><br><b>Proposal</b>:<br>I propose adding an ALTER INDEX command tha=
t allows for enabling or disabling an index globally. This could look somet=
hing like:<br><br>ALTER INDEX index_name ENABLE;<br>ALTER INDEX index_name =
DISABLE;<br><br>A disabled index would still receive updates and enforce co=
nstraints as usual but would not be used for queries. This allows users to =
assess whether an index impacts query performance before deciding to drop i=
t entirely.<br><br><b>Implementation</b>:<br>To keep this simple, I suggest=
 toggling the indisvalid flag in pg_index during the enable/disable operati=
on.<br><br><b>Additional Considerations</b>:<br>- Keeping the index up-to-d=
ate while it=E2=80=99s disabled seems preferable, as it avoids the need to =
rebuild the index if it=E2=80=99s re-enabled later. The alternative would b=
e dropping and rebuilding the index upon re-enabling, which I believe would=
 introduce additional overhead in terms of application logic &amp; complexi=
ty.<br>- I am also proposing to reuse the existing indisvalid flag to avoid=
 adding new state and the maintenance that comes with it, but I=E2=80=99m o=
pen to feedback if this approach has potential downsides.<br>- 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 IN=
DEX. I would love to know if this would break any SQL grammar promises that=
 I might be unaware of.<br><br>I would love to learn if this sounds like a =
good=C2=A0idea and how it can be improved further. Accordingly, as a next s=
tep I would be very happy to propose a patch as well. <br><br>Best regards,=
 =C2=A0<br>Shayon Mukherjee<br></div>

--000000000000f1375e0621b694f4--