Message-ID: <a279669270841cfc83ce2ac86a69491ec05cdeaf.camel@cybertec.at>
Subject: Re: Dropping index from large, partitioned table
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Matthew Planchard <msplanchard@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Wed, 08 Oct 2025 09:56:18 +0200
In-Reply-To: 
 <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
References: 
	<CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/a279669270841cfc83ce2ac86a69491ec05cdeaf.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-07 at 17:00 -0400, Matthew Planchard 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.
>=20
> 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
> read and written to tables for the duration.
>=20
> In one of our larger environments, I attempted a drop with a
> two-minute timeout, with no success, and two minutes is really pushing
> what we're able to do without causing really obvious downtime.
>=20
> Some additional context is that our production deploys are in RDS, so
> even with an admin user I am not able to modify the postgres system
> tables to do things like marking indexes as invalid.
>=20
> Is there any way to manage this without requiring the massive global
> access exclusive lock for the duration of the drop on every child
> partition?

I don't think there is, and if you are using a hosted database, you
are free from the temptation to mess with the catalogs manually and
risk destroying your database.

I don't think that the actual DROP INDEX will take long (you could
run it on a test system), the challenge is to quiesce the application.

Take a down time of five minutes, shut down the application, run your
DROP INDEX, check with pg_blocking_pids() if there are any stragglers
that are locking you out and kill them with pg_terminate_backend().

Yours,
Laurenz Albe