Re: Partition detach / index question

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