MIME-Version: 1.0
From: Matthew Planchard <msplanchard@gmail.com>
Date: Tue, 7 Oct 2025 17:00:34 -0400
Message-ID: 
 <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
Subject: Dropping index from large, partitioned table
To: pgsql-admin@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHncFmQ%2BEVmXnhhiF0005t9Ceg%3DYUraTznPw_Ds1zK8%2B50vJRg%40mail.gmail.com>
Precedence: bulk

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!