Message-ID: <51E74E2C.8020505@2ndQuadrant.com>
Date: Wed, 17 Jul 2013 22:08:44 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8;
 rv:17.0) Gecko/20130620 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-docs <pgsql-docs@postgresql.org>
Subject: Improving index maintenance suggestions
Content-Type: multipart/mixed; boundary="------------040702060708010208030002"
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

This is a multi-part message in MIME format.
--------------040702060708010208030002
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

The attached documentation patch overhauls the "Routine Reindexing" 
documentation to introduce production index rebuilding techniques. 
REBUILD is useless for a lot of environments, replaced with CREATE INDEX 
CONCURRENTLY and various naming shuffles if you want the new index to 
have the same name.  I tried to link to all of the commands needed to 
pull that off usefully.

I also noticed that it's easy to get a wrong idea from the CREATE INDEX 
CONCURRENTLY documentation:  that invalid indexes are only possible when 
building certain types of indexes, like unique ones.  I mention 
deadlocks there to make it more obvious that any index built 
concurrently can fail.  I have now seen several REBUILD simulation 
scripts written that assume simple concurrent index building will always 
work.  Those can really crash and burn on a deadlock, leaving no index 
at all left behind in some bad sequences.  (DROP the original, ALTER the 
new name to the old one, but it's invalid)

I think all of this would make a good backport candidate going back to 
9.1, when several of the ALTER TABLE methods were added for replacing 
indexes backing constraints.  Before then this technique had so many 
limitations it was barely worth mentioning.  I didn't want to push this 
approach so heavily in the manual at the time 9.1 was released, for fear 
there might have some unexpected real-world issues here.  Indexes 
referred to by foreign keys have turned out to be the only thing I see 
regularly that are hard to rebuild in 9.1 and 9.2.  The CREATE/ALTER 
shuffle works great for everything else.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

--------------040702060708010208030002
Content-Type: text/plain; charset=UTF-8; x-mac-type="0"; x-mac-creator="0";
 name="rebuild-steps-v1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="rebuild-steps-v1.patch"

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
new file mode 100644
index c34ca53..dc687f9
*** a/doc/src/sgml/maintenance.sgml
--- b/doc/src/sgml/maintenance.sgml
*************** analyze threshold = analyze base thresho
*** 745,752 ****
  
    <para>
     In some situations it is worthwhile to rebuild indexes periodically
!    with the <xref linkend="sql-reindex">
!    command.
    </para>
  
    <para>
--- 745,753 ----
  
    <para>
     In some situations it is worthwhile to rebuild indexes periodically
!    with the <xref linkend="sql-reindex"> command or a series of individual
!    rebuilding steps.
! 
    </para>
  
    <para>
*************** analyze threshold = analyze base thresho
*** 772,777 ****
--- 773,796 ----
     (This consideration does not apply to non-B-tree indexes.)  It
     might be worthwhile to reindex periodically just to improve access speed.
    </para>
+ 
+   <para>
+    <xref linkend="sql-reindex"> can be used safely and easily in all cases.
+    But since the command requires an exclusive table lock, it is
+    often preferable to execute an index rebuild with a sequence of
+    creation and replacement steps.  Index types that support
+    <xref linkend="sql-createindex"> with the <literal>CONCURRENTLY</>
+    option can instead be recreated that way. If that is successful and the
+    resulting index is valid, the original index can then be replaced by
+    the newly built one using combinations of <xref linkend="sql-alterindex">
+    and <xref linkend="sql-dropindex">. When an index is used to enforce
+    uniqueness or other constraints, <xref linkend="sql-altertable"> may
+    also be necessary, to swap the existing constraint to one enforced by
+    the new index. Review this alternate, multiple step rebuild approach
+    very carefully before using it, as there are limitations on which
+    indexes can be reindexed this way, and errors you should detect and
+    handle correctly.
+   </para>
   </sect1>
  
  
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
new file mode 100644
index 01faa3a..9a6a683
*** a/doc/src/sgml/ref/create_index.sgml
--- b/doc/src/sgml/ref/create_index.sgml
*************** CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ]
*** 405,411 ****
     </para>
  
     <para>
!     If a problem arises while scanning the table, such as a
      uniqueness violation in a unique index, the <command>CREATE INDEX</>
      command will fail but leave behind an <quote>invalid</> index. This index
      will be ignored for querying purposes because it might be incomplete;
--- 405,411 ----
     </para>
  
     <para>
!     If a problem arises while scanning the table, such as a deadlock or a
      uniqueness violation in a unique index, the <command>CREATE INDEX</>
      command will fail but leave behind an <quote>invalid</> index. This index
      will be ignored for querying purposes because it might be incomplete;

--------------040702060708010208030002
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

--------------040702060708010208030002--