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

Here's a third iteration of this work; I've polished a few rough edges
and made sure that the behavior on ALTER TABLE ATTACH is consistent with
what you get when creating a new partition via CREATE TABLE .. PARTITION OF,
and with what happens when you do CREATE INDEX on an existing
partitioning tree, and added some docs.

The main big item missing is the CONCURRENTLY option to CREATE INDEX.
With that and once I review a couple of minor items marked XXX, I'm
about to call committable.

I also added ONLY to create index: "CREATE INDEX .. ON ONLY tab".
Normally if you create an index on a partitioned table, it recurses;
with this option, it doesn't. This is used by pg_dump to ensure that
restore doesn't create indexes on partitions that didn't have them on
the original database. Then the others are attached using the new
command ALTER INDEX ATTACH, as discussed.

For symmetry there's also ALTER INDEX DETACH. Can be used to replace an
index on a partition with a fresh version, for cases of bloat.

The last patch is in WIP state yet, and mostly untouched since I last
posted it. It allows creation of unique constraints (and PKs). But any
interaction with the rest of the system is untested (such as FKs, ON
CONFLICT, etc).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services