pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Partition detach / index question 2+ messages / 2 participants [nested] [flat]
* Partition detach / index question @ 2019-01-31 16:52 Jorge Torralba <jorge.torralba@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Jorge Torralba @ 2019-01-31 16:52 UTC (permalink / raw) To: pgsql-admin 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Partition detach / index question @ 2019-01-31 17:01 Alvaro Herrera <alvherre@2ndquadrant.com> parent: Jorge Torralba <jorge.torralba@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Alvaro Herrera @ 2019-01-31 17:01 UTC (permalink / raw) To: Jorge Torralba <jorge.torralba@gmail.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-01-31 17:01 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-01-31 16:52 Partition detach / index question Jorge Torralba <jorge.torralba@gmail.com> 2019-01-31 17:01 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox