On 2019-Apr-26, Tom Lane wrote:
> Alvaro Herrera <alvhe...@2ndquadrant.com> writes:
> > I suppose I better add something in Chapter 5 (DDL), possibly inside the
> > 5.10 section (Table Partitioning) -- a new 5.10.6 "Indexes for
> > Partitioned Tables" perhaps?
>
> I was expecting to find it in "5.11.2.2. Partition Maintenance".
Here's a first attempt at doing that. I vote to backpatch this to pg11
(since this functionality is all there).
> BTW, is there anything equivalent for unique/pkey constraints?
> I tried "add constraint unique ... not valid" and just got a
> raspberry.
Sure, just "alter table only parent", without explicitly marking it not
valid. Then the indexes on children must be attached to the parent
indexes; this searches for constraints and does the right thing.
On 2019-Apr-26, Jonathan S. Katz wrote:
> I'd suggest keeping the title of the section similar to the one with
> constraints, i.e. "Partitioning and Indexes"
In the end, it seemed material far too short to have its own subsection,
but maybe if we redesign the whole section we could lay it out
differently? (One idea would be to leave 5.11 for declarative
partitioning, and add a new section 5.12 for legacy inheritance.
Something to think about for pg13)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From 88204641fce0614551c3b85f921e3cc044650320 Mon Sep 17 00:00:00 2001
From: Alvaro Herrera <alvhe...@alvh.no-ip.org>
Date: Thu, 16 May 2019 16:04:29 -0400
Subject: [PATCH] Describe creation of partitioned index piecemeal
---
doc/src/sgml/ddl.sgml | 38 ++++++++++++++++++++++++++++++++++++++
1 file changed, 38 insertions(+)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index a0a7435a03d..53e0504ac86 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3948,6 +3948,44 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
One may then drop the constraint after <command>ATTACH PARTITION</command>
is finished, because it is no longer necessary.
</para>
+
+ <para>
+ As explained above, it is possible to create indexes on partitioned tables
+ and they are applied automatically to the entire hierarchy. This is very
+ convenient, as not only the existing partitions will become indexed, but
+ also any partitions that are created in the future do. One limitation is
+ that it's not possible to use the <literal>CONCURRENTLY</literal>
+ qualifier when creating such a partitioned index. To overcome long lock
+ times, it is possible to use <command>CREATE INDEX ON ONLY</command>
+ the partitioned table; such an index is marked invalid, and the partitions
+ do not get the index applied automatically. The indexes on partitions can
+ be created separately using <literal>CONCURRENTLY</literal>, and later
+ <firstterm>attached</firstterm> to the index on the parent using
+ <command>ALTER INDEX .. ATTACH PARTITION</command>. Once indexes for all
+ partitions are attached to the parent index, the parent index is marked
+ valid automatically. Example:
+<programlisting>
+CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);
+
+CREATE INDEX measurement_usls_200602_idx
+ ON measurement_y2006m02 (unitsales);
+ALTER INDEX measurement_usls_idx
+ ATTACH PARTITION measurement_usls_200602_idx;
+...
+</programlisting>
+
+ This technique can be used with <literal>UNIQUE</literal> and
+ <literal>PRIMARY KEY</literal> constraints too; the indexes are created
+ implicitly when the constraint is created. Example:
+<programlisting>
+ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);
+
+ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
+ALTER INDEX measurement_city_id_logdate_key
+ ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
+...
+</programlisting>
+ </para>
</sect3>
<sect3 id="ddl-partitioning-declarative-limitations">
--
2.17.1
