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

On 5 January 2018 at 11:01, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> (The more I think of this, the more I believe that pg_inherits is a
> better answer. Opinions?)

I admit to not having had a chance to look at any code with this yet,
but I'm just thinking about a case like the following.

CREATE TABLE part (a INT, b INT) PARTITION BY RANGE (a);
CREATE TABLE part_a1 PARTITION OF part FOR VALUES FROM (0) TO (10)
PARTITION BY RANGE (b);
CREATE TABLE part_a1_b1 PARTITION OF part_a1 FOR VALUES FROM (0) TO (10);

CREATE INDEX ON part_a1 (a); -- sub-partition index (creates index on
part_a1_b1)

CREATE INDEX ON part (a); -- What do we do here?

Should we:

1. Create another identical index on part_a1_b1; or
2. Allow the existing index on part_a1_b1 to have multiple parents; or
3. ERROR... (probably not)

I guess pg_index.indparentidx won't allow #2 to work. Some pg_inherits
arrangement should.

We don't really want to go creating indexes that we don't need to, so
I think we should probably make an effort to allow #2 to work.

Question is, how likely is the above scenario to take place?

Normally, I see customers requiring partitioning only when an existing
table grows too large for maintenance tasks to complete a reasonable
timeframe. Probably there might also come a time when each of the
partitions they've then gone and created also grows too large. So it
does not seem unrealistic to be attaching existing tables/partitioned
tables as partitions multiple times. Wanting to reuse leaf indexes for
some new higher level partition parent does seem reasonable.

This argument might be voided by if we allowed a DROP INDEX on part_a1
without dropping the leaf indexes. I've not checked what the patch
does here, but I'd imagine if the indexes are marked as parents of
that index, then they'll be dropped.

I'll go off and look at the code now...

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services