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

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Maksim Milyutin <milyutinma(at)gmail(dot)com>
Subject: Re: [HACKERS] Proposal: Local indexes for partitioned table
Date: 2017-11-13 17:06:46
Message-ID: 20171113170646.gzweigyrgg6pwsg4@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v3-0001-Fix-summarization-concurrent-with-relation-extens.patch text/plain 8.0 KB
v3-0001-Simplify-index_-constraint_-create-API.patch text/plain 14.9 KB
v3-0002-Get-rid-of-copy_partition_key.patch text/plain 4.2 KB
v3-0003-export-generateClonedIndexStmt.patch text/plain 6.6 KB
v3-0004-Allow-indexes-on-partitioned-tables.patch text/plain 107.9 KB
v3-0005-allow-indexes-on-partitioned-tables-to-be-unique.patch text/plain 20.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2017-11-13 17:10:06 Re: Migration to PGLister - After
Previous Message Tom Lane 2017-11-13 17:03:52 Re: [HACKERS] pgbench: Skipping the creating primary keys after initialization