public inbox for [email protected]
help / color / mirror / Atom feedFrom: Greg Smith <[email protected]>
To: pgsql-docs <[email protected]>
Subject: Improving index maintenance suggestions
Date: Wed, 17 Jul 2013 22:08:44 -0400
Message-ID: <[email protected]> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-docs>
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 [email protected] Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
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;
--
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Attachments:
[text/plain] rebuild-steps-v1.patch (2.9K, 2-rebuild-steps-v1.patch)
download | inline diff:
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;
view thread (2+ messages) latest in thread
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: [email protected]
Cc: [email protected]
Subject: Re: Improving index maintenance suggestions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox