public inbox for [email protected]help / color / mirror / Atom feed
Dropping index from large, partitioned table 3+ messages / 3 participants [nested] [flat]
* Dropping index from large, partitioned table @ 2025-10-07 21:00 Matthew Planchard <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: Matthew Planchard @ 2025-10-07 21:00 UTC (permalink / raw) To: [email protected] 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! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Dropping index from large, partitioned table @ 2025-10-08 07:56 Laurenz Albe <[email protected]> parent: Matthew Planchard <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Laurenz Albe @ 2025-10-08 07:56 UTC (permalink / raw) To: Matthew Planchard <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Dropping index from large, partitioned table @ 2025-10-08 11:59 Ron Johnson <[email protected]> parent: Matthew Planchard <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Ron Johnson @ 2025-10-08 11:59 UTC (permalink / raw) To: pgsql-admin On Tue, Oct 7, 2025 at 5:01 PM Matthew Planchard <[email protected]> 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 > Too bad there's no ONLY. clause in DROP INDEX. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-08 11:59 UTC | newest] Thread overview: 3+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-07 21:00 Dropping index from large, partitioned table Matthew Planchard <[email protected]> 2025-10-08 07:56 ` Laurenz Albe <[email protected]> 2025-10-08 11:59 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox