pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: [PATCH] Describe creation of partitioned index piecemeal
Date: Thu, 16 May 2019 16:04:29 -0400

---
 doc/src/sgml/ddl.sgml | 38 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 38 insertions(+)

inline patch 1: doc/src/sgml/ddl.sgml (+38/-0 lines) download inline-1.patch
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


--u3/rZRmxL6MmkK24--

Message-ID: <no-message-id-6967@localhost>
Permalink:  ../no-message-id-6967@localhost/
Also on:    postgresql.org/message-id/no-message-id-6967@localhost

copy link · copy postgr.es · copy git am

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: alvherre@alvh.no-ip.org
  Subject: Re: [PATCH] Describe creation of partitioned index piecemeal
  In-Reply-To: <no-message-id-6967@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox