From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Jorge Torralba <jorge.torralba@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Partition detach / index question
Date: Thu, 31 Jan 2019 14:01:49 -0300
Message-ID: <201901311701.daehacbrneox@alvherre.pgsql> (raw)
In-Reply-To: <CACut7uR=NmdVL2pQDN7hh=3V_1NhZzEJcpUJLdWisev3LnG0Cg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, jorge.torralba@gmail.com
Subject: Re: Partition detach / index question
In-Reply-To: <201901311701.daehacbrneox@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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