Skip site navigation (1) Skip section navigation (2)

Proposal: Local indexes for partitioned table

From: Maksim Milyutin <m(dot)milyutin(at)postgrespro(dot)ru>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: Local indexes for partitioned table
Date: 2017-03-01 10:53:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hi hackers!

As I've understood from thread [1] the main issue of creating local 
indexes for partitions is supporting REINDEX and DROP INDEX operations 
on parent partitioned tables. Furthermore Robert Haas mentioned the 
problem of creating index on key that is represented in partitions with 
single value (or primitive interval) [1] i.e. under the 
list-partitioning or range-partitioning with unit interval.

I would like to propose the following solution:

1. Create index for hierarchy of partitioned tables and partitions 
recursively. Don't create relfilenode for indexes on parents, only 
entries in catalog (much like the partitioned table's storage 
elimination in [2]). Abstract index for partitioned tables is only for 
the reference on indexes of child tables to perform REINDEX and DROP 
INDEX operations.

2. Specify created indexes in pg_depend table so that indexes of child 
tables depend on corresponding indexes of parent tables with type of 
dependency DEPENDENCY_NORMAL so that index could be removed separately 
for partitions and recursively/separately for partitioned tables.

3. REINDEX on index of partitioned table would perform this operation on 
existing indexes of corresponding partitions. In this case it is 
necessary to consider such operations as REINDEX SCHEMA | DATABASE | 
SYSTEM so that partitions' indexes wouldn't be re-indexed multiple times 
in a row.

Any thoughts?


Maksim Milyutin
Postgres Professional:
Russian Postgres Company


pgsql-hackers by date

Next:From: Jan MichálekDate: 2017-03-01 11:31:00
Subject: Re: Other formats in pset like markdown, rst, mediawiki
Previous:From: Thomas MunroDate: 2017-03-01 10:47:49
Subject: Re: Measuring replay lag

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group