public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Matthew Planchard <[email protected]>
To: [email protected]
Subject: Re: Dropping index from large, partitioned table
Date: Wed, 08 Oct 2025 09:56:18 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>
References: <CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com>

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

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], [email protected]
  Subject: Re: Dropping index from large, partitioned table
  In-Reply-To: <[email protected]>

* 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