Thread: Partition detach / index question
We have a very large database and we are using declarative partitioning 12 12 partitions. In a nutshell, only the current month and last month have data. With only the current month receiving activity.
I need to create a new index on the table but do not want the overhead. And these are my thoughts.
At the beginning of the month when the new partition hardly has any data, detach the previous month partition and apply the index. At this point there should be hardly any data in the current month partition and the index should build in a few seconds.
Having said that, with the previous month partition detached, The index should not be applied to that partition. Correct ?
After the index is applied to all other partitions, then re-attach the old partition. Does it automatically get the new index applied or do I have to apply it manually? If it is automatically, will only that partition be impacted with the locks for the index build or will all partitions be affected?
--
Thanks,
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwse use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
Jorge Torralba
----------------------------
Note: This communication may contain privileged or other confidential information. If you are not the intended recipient, please do not print, copy, retransmit, disseminate or otherwse use the information. Please indicate to the sender that you have received this email in error and delete the copy you received. Thank You.
On 2019-Jan-31, Jorge Torralba wrote: > Having said that, with the previous month partition detached, The index > should not be applied to that partition. Correct ? Well, if a partition is detached, it's not a partition. It's a completely separate table. > After the index is applied to all other partitions, then re-attach the old > partition. Does it automatically get the new index applied or do I have to > apply it manually? If it is automatically, will only that partition be > impacted with the locks for the index build or will all partitions be > affected? You can create indexes individually on each partition, without needing to detach anything. This is useful if you want to have indexes that exist on some partitions but not others, but otherwise it's just a maintenance headache. On the other hand, if your partitioned table has an index and you attach a table as a partition, the attach code is going to search for an index with the same definition in the partition being attached. If there is such an index, that index will be used and no new index will be created. If there is no equivalent index, a new index will be created. If you want to attach a new partition and don't want to wait for a long index build time, and your new partition already has data, you can use CREATE INDEX CONCURRENTLY on the partition before attaching. Attaching and detaching partitions acquires access exclusive lock on the parent table, on the partition being attached/detached, and on the default partition. The other partitions are not locked. Of course, if your queries go through the partitioned table instead of directly querying individual partitions, they're going to block. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services