Home Explore Blog CI



postgresql

10th chunk of `doc/src/sgml/ref/create_index.sgml`
5ec90aa8bad180b817448f74c2a0c46b0f48e52ad5b01e150000000100000fa1
 enforce its uniqueness constraint afterwards.
   </para>

   <para>
    Concurrent builds of expression indexes and partial indexes are supported.
    Errors occurring in the evaluation of these expressions could cause
    behavior similar to that described above for unique constraint violations.
   </para>

   <para>
    Regular index builds permit other regular index builds on the
    same table to occur simultaneously, but only one concurrent index build
    can occur on a table at a time.  In either case, schema modification of the
    table is not allowed while the index is being built.  Another difference is
    that a regular <command>CREATE INDEX</command> command can be performed
    within a transaction block, but <command>CREATE INDEX CONCURRENTLY</command>
    cannot.
   </para>

   <para>
    Concurrent builds for indexes on partitioned tables are currently not
    supported.  However, you may concurrently build the index on each
    partition individually and then finally create the partitioned index
    non-concurrently in order to reduce the time where writes to the
    partitioned table will be locked out.  In this case, building the
    partitioned index is a metadata only operation.
   </para>

  </refsect2>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   See <xref linkend="indexes"/> for information about when indexes can
   be used, when they are not used, and in which particular situations
   they can be useful.
  </para>

  <para>
   Currently, only the B-tree, GiST, GIN, and BRIN index methods support
   multiple-key-column indexes.  Whether there can be multiple key
   columns is independent of whether <literal>INCLUDE</literal> columns
   can be added to the index.  Indexes can have up to 32 columns,
   including <literal>INCLUDE</literal> columns.
   (This limit can be altered when building
   <productname>PostgreSQL</productname>.)  Only B-tree currently
   supports unique indexes.
  </para>

  <para>
   An <firstterm>operator class</firstterm> with optional parameters
   can be specified for each column of an index.
   The operator class identifies the operators to be
   used by the index for that column. For example, a B-tree index on
   four-byte integers would use the <literal>int4_ops</literal> class;
   this operator class includes comparison functions for four-byte
   integers. In practice the default operator class for the column's data
   type is usually sufficient. The main point of having operator classes
   is that for some data types, there could be more than one meaningful
   ordering. For example, we might want to sort a complex-number data
   type either by absolute value or by real part. We could do this by
   defining two operator classes for the data type and then selecting
   the proper class when creating an index.  More information about
   operator classes is in <xref linkend="indexes-opclass"/> and in <xref
   linkend="xindex"/>.
  </para>

  <para>
   When <literal>CREATE INDEX</literal> is invoked on a partitioned
   table, the default behavior is to recurse to all partitions to ensure
   they all have matching indexes.
   Each partition is first checked to determine whether an equivalent
   index already exists, and if so, that index will become attached as a
   partition index to the index being created, which will become its
   parent index.
   If no matching index exists, a new index will be created and
   automatically attached; the name of the new index in each partition
   will be determined as if no index name had been specified in the
   command.
   If the <literal>ONLY</literal> option is specified, no recursion
   is done, and the index is marked invalid.
   (<command>ALTER INDEX ... ATTACH PARTITION</command> marks the index
   valid, once all partitions acquire matching indexes.)  Note, however,
   that any partition that is created in the future using
   <command>CREATE TABLE ... PARTITION OF</command> will automatically
   have a matching index,

Title: Concurrent Index Builds and Additional Notes on Indexes
Summary
Concurrent builds of expression and partial indexes are supported, but errors could lead to behavior similar to unique constraint violations. Regular index builds can occur simultaneously, but only one concurrent build can occur per table, and schema modification is prohibited during the build. Concurrent builds are not supported for partitioned tables, but individual partitions can be indexed concurrently. The notes section covers the use and usefulness of indexes, the number of columns supported, operator classes for indexes, and the default behavior of index creation on partitioned tables, which includes recursion to all partitions to ensure matching indexes.