Dropping index from large, partitioned table
От | Matthew Planchard |
---|---|
Тема | Dropping index from large, partitioned table |
Дата | |
Msg-id | CAHncFmQ+EVmXnhhiF0005t9Ceg=YUraTznPw_Ds1zK8+50vJRg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Dropping index from large, partitioned table
Re: Dropping index from large, partitioned table |
Список | pgsql-admin |
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!
В списке pgsql-admin по дате отправления: