MIME-Version: 1.0
References: 
 <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
In-Reply-To: 
 <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 8 Oct 2025 07:59:47 -0400
Message-ID: 
 <CANzqJaBTqzVH=hPTPGfp2LsJZtQ6otc+Th1PFA=kuALbmfTn_A@mail.gmail.com>
Subject: Re: Dropping index from large, partitioned table
To: pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006b816b0640a46c4a"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaBTqzVH%3DhPTPGfp2LsJZtQ6otc%2BTh1PFA%3DkuALbmfTn_A%40mail.gmail.com>
Precedence: bulk

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

On Tue, Oct 7, 2025 at 5:01=E2=80=AFPM Matthew Planchard <msplanchard@gmail=
.com>
wrote:

> I have a very active table with around 1,000 partitions. I would like
> to drop a GIN index on one of its columns.
>
> Unfortunately, this is a top-level index, and so it seems impossible
> to drop the indexes on each partition individually, which means a
> 'drop index' requires holding an access exclusive lock on the entire
> table until the process completes for all children. That process turns
> out to be much too slow, since it requires locking one of our most
>

Too bad there's no ONLY. clause in DROP INDEX.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Oct 7, 2025 at 5:01=E2=80=AFPM Ma=
tthew Planchard &lt;<a href=3D"mailto:msplanchard@gmail.com">msplanchard@gm=
ail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">I have a very active ta=
ble with around 1,000 partitions. I would like<br>
to drop a GIN index on one of its columns.<br>
<br>
Unfortunately, this is a top-level index, and so it seems impossible<br>
to drop the indexes on each partition individually, which means a<br>
&#39;drop index&#39; requires holding an access exclusive lock on the entir=
e<br>
table until the process completes for all children. That process turns<br>
out to be much too slow, since it requires locking one of our most<br>
</blockquote><div><br></div><div>Too bad there&#39;s no ONLY. clause in DRO=
P INDEX.</div></div><div><br></div><span class=3D"gmail_signature_prefix">-=
- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">De=
ath to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m s=
till alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div><=
/div></div>

--0000000000006b816b0640a46c4a--