Re: [HACKERS] Proposal: Local indexes for partitioned table

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