Обсуждение: Dropping index from large, partitioned table

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.

I have tried on a local database detaching each child partition,
dropping its index, and reattaching it, all within a transaction, but
the child index is recreated when the table is reattached.

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?

Thanks!

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