From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Maksim Milyutin <m.milyutin@postgrespro.ru>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Date: Tue, 14 Nov 2017 16:23:28 +0100
Message-ID: <20171114152328.qysi76w6i2wnsn6m@alvherre.pgsql> (raw)
In-Reply-To: <CAKJS1f_Wf=M06o8iQi72SxN+ZpLV4c0CwYoN5xYVY4aWWX-jBQ@mail.gmail.com>
David Rowley wrote:
> But if you can DETACH a partition from requiring an index to back up
> this partitioned index, then the partitioned index is not valid while
> the leaf table's index is missing.
Yeah. I don't see this as too much of a problem myself. At present,
from the planner's POV each partition is its own table and incurs its
own planning, so there's no restriction on having different sets of
indexes. If in the future we want to see partition hierarchies as a
single relation which must have identical properties such as the same
index everywhere, this is easily changed: just set indisready flag false
for the parent table until every partition can be proven to have the
index.
Example: on DETACH we set indisready=false on the parent; on ATTACH we
scan the whole hierarchy and set it true if all partitions have the
index. So the planner can rely on the parent's indisready. Since those
operations are catalog-only, the transaction which attaches/detaches the
index can be short (though AFAICS it requires AEL on the whole
hierarchy, sadly).
> Maybe I misunderstood what you mean by DETACH here, but if you remove
> an index and there's some period of time where none exists for a leaf
> partition, then you're not going to be able to ever do partitioned
> UNIQUE indexes.
Well, UNIQUE indexes have additional restrictions of their own. I have
no problem decreeing that you cannot DETACH an index from a primary key.
Then a future project can implement index replacement like you propose:
> I'd have thought some sort of: ALTER INDEX name_of_partitioned_index
> REPLACE INDEX FOR partitioned_tablename WITH
> name_of_new_matching_bloat_free_index;
>
> ... or something along those lines, and just have an atomic swap out
> of the index with some new one that's been created.
(My intention here is to avoid scope creep.)
--
Á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-hackers@postgresql.org
Cc: alvherre@alvh.no-ip.org, david.rowley@2ndquadrant.com, robertmhaas@gmail.com, m.milyutin@postgrespro.ru
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
In-Reply-To: <20171114152328.qysi76w6i2wnsn6m@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