Re: Dropping index from large, partitioned table

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.
>
> 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.
>
> 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.
>
> 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.
>
> 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