Home Explore Blog CI



postgresql

26th chunk of `doc/src/sgml/ref/create_table.sgml`
d366bdeb2b64f7540cf7296b4e6b4ebdc9b863be4ea628220000000100000fa4
 INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
    <listitem>
     <para>
      This clause allows selection of the tablespace in which the index
      associated with a <literal>UNIQUE</literal>, <literal>PRIMARY
      KEY</literal>, or <literal>EXCLUDE</literal> constraint will be created.
      If not specified,
      <xref linkend="guc-default-tablespace"/> is consulted, or
      <xref linkend="guc-temp-tablespaces"/> if the table is temporary.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>

  <refsect2 id="sql-createtable-storage-parameters" xreflabel="Storage Parameters">
   <title>Storage Parameters</title>

 <indexterm zone="sql-createtable-storage-parameters">
  <primary>storage parameters</primary>
 </indexterm>

   <para>
    The <literal>WITH</literal> clause can specify <firstterm>storage parameters</firstterm>
    for tables, and for indexes associated with a <literal>UNIQUE</literal>,
    <literal>PRIMARY KEY</literal>, or <literal>EXCLUDE</literal> constraint.
    Storage parameters for
    indexes are documented in <xref linkend="sql-createindex"/>.
    The storage parameters currently
    available for tables are listed below.  For many of these parameters, as
    shown, there is an additional parameter with the same name prefixed with
    <literal>toast.</literal>, which controls the behavior of the
    table's secondary <acronym>TOAST</acronym> table, if any
    (see <xref linkend="storage-toast"/> for more information about TOAST).
    If a table parameter value is set and the
    equivalent <literal>toast.</literal> parameter is not, the TOAST table
    will use the table's parameter value.
    Specifying these parameters for partitioned tables is not supported,
    but you may specify them for individual leaf partitions.
   </para>

   <variablelist>

    <varlistentry id="reloption-fillfactor" xreflabel="fillfactor">
    <term><varname>fillfactor</varname> (<type>integer</type>)
    <indexterm>
     <primary><varname>fillfactor</varname> storage parameter</primary>
    </indexterm>
    </term>
    <listitem>
     <para>
      The fillfactor for a table is a percentage between 10 and 100.
      100 (complete packing) is the default.  When a smaller fillfactor
      is specified, <command>INSERT</command> operations pack table pages only
      to the indicated percentage; the remaining space on each page is
      reserved for updating rows on that page.  This gives <command>UPDATE</command>
      a chance to place the updated copy of a row on the same page as the
      original, which is more efficient than placing it on a different
      page, and makes <link linkend="storage-hot">heap-only tuple
      updates</link> more likely.
      For a table whose entries are never updated, complete packing is the
      best choice, but in heavily updated tables smaller fillfactors are
      appropriate.  This parameter cannot be set for TOAST tables.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="reloption-toast-tuple-target" xreflabel="toast_tuple_target">
    <term><literal>toast_tuple_target</literal> (<type>integer</type>)
    <indexterm>
     <primary><varname>toast_tuple_target</varname> storage parameter</primary>
    </indexterm>
    </term>
    <listitem>
     <para>
      The toast_tuple_target specifies the minimum tuple length required before
      we try to compress and/or move long column values into TOAST tables, and
      is also the target length we try to reduce the length below once toasting
      begins. This affects columns marked as External (for move),
      Main (for compression), or Extended (for both) and applies only to new
      tuples. There is no effect on existing rows.
      By default this parameter is set to allow at least 4 tuples per block,
      which with the default block size will be 2040 bytes. Valid values are
      between 128 bytes and the (block size - header), by default 8160 bytes.

Title: Storage Parameters for CREATE TABLE: fillfactor and toast_tuple_target
Summary
This section describes storage parameters that can be specified using the WITH clause for tables and their associated indexes. It specifically details the 'fillfactor' parameter, which controls the packing density of table pages, and the 'toast_tuple_target' parameter, which determines when long column values are compressed or moved to TOAST tables. It also notes that specifying these parameters for partitioned tables is not directly supported but is possible for leaf partitions.