Home Explore Blog CI



postgresql

8th chunk of `doc/src/sgml/ref/create_index.sgml`
2414f9a79d724409ee3b5caf18c6063261ab447f1e936b200000000100000fa7
 parameter</secondary>
     </indexterm>
    </term>
    <listitem>
    <para>
     Overrides the global setting of
     <xref linkend="guc-gin-pending-list-limit"/> for this index.
     This value is specified in kilobytes.
    </para>
    </listitem>
   </varlistentry>
   </variablelist>

   <para>
    <acronym>BRIN</acronym> indexes accept these parameters:
   </para>

   <variablelist>
   <varlistentry id="index-reloption-pages-per-range" xreflabel="pages_per_range">
    <term><literal>pages_per_range</literal> (<type>integer</type>)
     <indexterm>
      <primary><varname>pages_per_range</varname> storage parameter</primary>
     </indexterm>
    </term>
    <listitem>
    <para>
     Defines the number of table blocks that make up one block range for
     each entry of a <acronym>BRIN</acronym> index (see <xref linkend="brin-intro"/>
     for more details).  The default is <literal>128</literal>.
    </para>
    </listitem>
   </varlistentry>

   <varlistentry id="index-reloption-autosummarize" xreflabel="autosummarize">
    <term><literal>autosummarize</literal> (<type>boolean</type>)
     <indexterm>
      <primary><varname>autosummarize</varname> storage parameter</primary>
     </indexterm>
    </term>
    <listitem>
    <para>
     Defines whether a summarization run is queued for the previous page
     range whenever an insertion is detected on the next one
     (see <xref linkend="brin-operation"/> for more details).
     The default is <literal>off</literal>.
    </para>
    </listitem>
   </varlistentry>
   </variablelist>
  </refsect2>

  <refsect2 id="sql-createindex-concurrently" xreflabel="Building Indexes Concurrently">
   <title>Building Indexes Concurrently</title>

   <indexterm zone="sql-createindex-concurrently">
   <primary>index</primary>
   <secondary>building concurrently</secondary>
   </indexterm>

   <para>
    Creating an index can interfere with regular operation of a database.
    Normally <productname>PostgreSQL</productname> 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.  Very large tables can take many hours to be
    indexed, and even for smaller tables, an index build can lock out writers
    for periods that are unacceptably long for a production system.
   </para>

   <para>
    <productname>PostgreSQL</productname> supports building indexes without locking
    out writes.  This method is invoked by specifying the
    <literal>CONCURRENTLY</literal> option of <command>CREATE INDEX</command>.
    When this option is used,
    <productname>PostgreSQL</productname> must perform two scans of the table, and in
    addition it must wait for all existing transactions that could potentially
    modify or use the index to terminate.  Thus
    this method requires more total work than a standard index build and takes
    significantly longer to complete.  However, since it allows normal
    operations to continue while the index is built, this method is useful for
    adding new indexes in a production environment.  Of course, the extra CPU
    and I/O load imposed by the index creation might slow other operations.
   </para>

   <para>
    In a concurrent index build, the index is actually entered as an
    <quote>invalid</quote> index into
    the system catalogs in one transaction, then two table scans occur in
    two more transactions.  Before each table scan, the index build must
    wait for existing transactions that have modified the table to terminate.
    After the second scan, the index build must wait for any transactions
    that have a snapshot (see <xref linkend="mvcc"/>) predating the second
    scan to terminate, including transactions used by any phase of concurrent

Title: BRIN Index Parameters and Building Indexes Concurrently
Summary
This section discusses BRIN index parameters, specifically 'pages_per_range' and 'autosummarize', which define block range size and control summarization runs, respectively. It then explains how to build indexes concurrently using the 'CONCURRENTLY' option, which allows writes to the table during index creation at the cost of increased build time and resource usage. The process involves two table scans and waiting for existing transactions to terminate to avoid conflicts.