public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthew Planchard <[email protected]>
To: [email protected]
Subject: Dropping index from large, partitioned table
Date: Tue, 7 Oct 2025 17:00:34 -0400
Message-ID: <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com> (raw)

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!

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Dropping index from large, partitioned table
  In-Reply-To: <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox