The references to data warehousing and large tables were bothering me since --
while true -- it's not really the main use case for CREATE INDEX CONCURRENTLY.
Really it's OLTP systems that may or may not have large tables but regardless
cannot stand the downtime caused by locks.
Index: doc/src/sgml/indices.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.58
diff -c -r1.58 indices.sgml
*** doc/src/sgml/indices.sgml 25 Aug 2006 04:06:44 -0000 1.58
--- doc/src/sgml/indices.sgml 11 Sep 2006 14:36:04 -0000
***************
*** 91,101 ****
</para>
<para>
! Creating an index on a large table can take a long time. By default,
! <productname>PostgreSQL</productname> allows reads (selects) to occur
! on the table in parallel with index creation, but writes (inserts,
! updates, deletes) are blocked until the index build is finished.
! It is possible to allow writes to occur in parallel with index
creation, but there are several caveats to be aware of —
for more information see <xref linkend="SQL-CREATEINDEX-CONCURRENTLY"
endterm="SQL-CREATEINDEX-CONCURRENTLY-title">.
--- 91,101 ----
</para>
<para>
! By default when creating indexes <productname>PostgreSQL</productname>
! allows reads (selects) to occur on the table being indexed in parallel with
! index creation, but writes (inserts, updates, deletes) are blocked until
! the index build is finished. In production environments this is often
! unacceptable. It is possible to allow writes to occur in parallel with index
creation, but there are several caveats to be aware of —
for more information see <xref linkend="SQL-CREATEINDEX-CONCURRENTLY"
endterm="SQL-CREATEINDEX-CONCURRENTLY-title">.
cvs diff: Diffing doc/src/sgml/ref
Index: doc/src/sgml/ref/create_index.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_index.sgml,v
retrieving revision 1.56
diff -c -r1.56 create_index.sgml
*** doc/src/sgml/ref/create_index.sgml 25 Aug 2006 04:06:45 -0000 1.56
--- doc/src/sgml/ref/create_index.sgml 11 Sep 2006 14:36:06 -0000
***************
*** 264,281 ****
</indexterm>
<para>
! Creating an index for a large table can be a long operation. In large data
! warehousing applications it can easily take hours or even days to build
! indexes. It's important to understand the impact creating indexes has on a
! system.
! </para>
!
! <para>
Normally <productname>PostgreSQL</> locks the table to be indexed against
writes and performs the entire index build with a single scan of the
table. Other transactions can still read the table, but if they try to
insert, update, or delete rows in the table they will block until the
! index build is finished.
</para>
<para>
--- 264,278 ----
</indexterm>
<para>
! Creating an index can interfere with regular operation of a database.
Normally <productname>PostgreSQL</> locks the table to be indexed against
writes and performs the entire index build with a single scan of the
table. Other transactions can still read the table, but if they try to
insert, update, or delete rows in the table they will block until the
! index build is finished. This could have a severe effect if the system is
! a live production database. Large tables can take many hours to be
! indexed, and even smaller tables can lock out writers for unacceptably
! long periods for a production system.
</para>
<para>
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
