8000 Support disabling index bypassing by VACUUM. · postgrespro/postgres@3499df0 · GitHub
[go: up one dir, main page]
Skip to content

    • Commit 3499df0

    Browse files
    petergeogheganpetergeoghegan
    committed
    Support disabling index bypassing by VACUUM.
    Generalize the INDEX_CLEANUP VACUUM parameter (and the corresponding reloption): make it into a ternary style boolean parameter. It now exposes a third option, "auto". The "auto" option (which is now the default) enables the "bypass index vacuuming" optimization added by commit 1e55e7d. "VACUUM (INDEX_CLEANUP TRUE)" is redefined to once again make VACUUM simply do any required index vacuuming, regardless of how few dead tuples are encountered during the first scan of the target heap relation (unless there are exactly zero). This gives users a way of opting out of the "bypass index vacuuming" optimization, if for whatever reason that proves necessary. It is also expected to be used by PostgreSQL developers as a testing option from time to time. "VACUUM (INDEX_CLEANUP FALSE)" does the same thing as it always has: it forcibly disables both index vacuuming and index cleanup. It's not expected to be used much in PostgreSQL 14. The failsafe mechanism added by commit 1e55e7d addresses the same problem in a simpler way. INDEX_CLEANUP can now be thought of as a testing and compatibility option. Author: Peter Geoghegan <pg@bowt.ie> Reviewed-By: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-By: Justin Pryzby <pryzby@telsasoft.com> Discussion: https://postgr.es/m/CAH2-WznrBoCST4_Gxh_G9hA8NzGUbeBGnOUC8FcXcrhqsv6OHQ@mail.gmail.com
    1 parent 0912698 commit 3499df0

    File tree

    13 files changed

    +313
    -127
    lines changed

    13 files changed

    +313
    -127
    lines changed

    ‎doc/src/sgml/ref/create_table.sgml

    Lines changed: 15 additions & 8 deletions
    Original file line numberDiff line numberDiff line change
    @@ -1473,20 +1473,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
    14731473
    </varlistentry>
    14741474

    14751475
    <varlistentry id="reloption-vacuum-index-cleanup" xreflabel="vacuum_index_cleanup">
    1476-
    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>boolean</type>)
    1476+
    <term><literal>vacuum_index_cleanup</literal>, <literal>toast.vacuum_index_cleanup</literal> (<type>enum</type>)
    14771477
    <indexterm>
    14781478
    <primary><varname>vacuum_index_cleanup</varname> storage parameter</primary>
    14791479
    </indexterm>
    14801480
    </term>
    14811481
    <listitem>
    14821482
    <para>
    1483-
    Enables or disables index cleanup when <command>VACUUM</command> is
    1484-
    run on this table. The default value is <literal>true</literal>.
    1485-
    Disabling index cleanup can speed up <command>VACUUM</command> very
    1486-
    significantly, but may also lead to severely bloated indexes if table
    1487-
    modifications are frequent. The <literal>INDEX_CLEANUP</literal>
    1488-
    parameter of <link linkend="sql-vacuum"><command>VACUUM</command></link>, if specified, overrides
    1489-
    the value of this option.
    1483+
    Forces or disables index cleanup when <command>VACUUM</command>
    1484+
    is run on this table. The default value is
    1485+
    <literal>AUTO</literal>. With <literal>OFF</literal>, index
    1486+
    cleanup is disabled, with <literal>ON</literal> it is enabled,
    1487+
    and with <literal>AUTO</literal> a decision is made dynamically,
    1488+
    each time <command>VACUUM</command> runs. The dynamic behavior
    1489+
    allows <command>VACUUM</command> to avoid needlessly scanning
    1490+
    indexes to remove very few dead tuples. Forcibly disabling all
    1491+
    index cleanup can speed up <command>VACUUM</command> very
    1492+
    significantly, but may also lead to severely bloated indexes if
    1493+
    table modifications are frequent. The
    1494+
    <literal>INDEX_CLEANUP</literal> parameter of <link
    1495+
    linkend="sql-vacuum"><command>VACUUM</command></link>, if
    1496+
    specified, overrides the value of this option.
    14901497
    </para>
    14911498
    </listitem>
    14921499
    </varlistentry>

    ‎doc/src/sgml/ref/vacuum.sgml

    Lines changed: 41 additions & 16 deletions
    Original file line numberDiff line numberDiff line change
    @@ -32,7 +32,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
    3232
    ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
    3333
    DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
    3434
    SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
    35-
    INDEX_CLEANUP [ <replaceable class="parameter">boolean</replaceable> ]
    35+
    INDEX_CLEANUP { AUTO | ON | OFF }
    3636
    PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
    3737
    TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
    3838
    PARALLEL <replaceable class="parameter">integer</replaceable>
    @@ -193,20 +193,45 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
    193193
    <term><literal>INDEX_CLEANUP</literal></term>
    194194
    <listitem>
    195195
    <para>
    196-
    Specifies that <command>VACUUM</command> should attempt to remove
    197-
    index entries pointing to dead tuples. This is normally the desired
    198-
    behavior and is the default unless the
    199-
    <literal>vacuum_index_cleanup</literal> option has been set to false
    200-
    for the table to be vacuumed. Setting this option to false may be
    201-
    useful when it is necessary to make vacuum run as quickly as possible,
    202-
    for example to avoid imminent transaction ID wraparound
    203-
    (see <xref linkend="vacuum-for-wraparound"/>). However, if index
    204-
    cleanup is not performed regularly, performance may suffer, because
    205-
    as the table is modified, indexes will accumulate dead tuples
    206-
    and the table itself will accumulate dead line pointers that cannot be
    207-
    removed until index cleanup is completed. This option has no effect
    208-
    for tables that do not have an index and is ignored if the
    209-
    <literal>FULL</literal> option is used.
    196+
    Normally, <command>VACUUM</command> will skip index vacuuming
    197+
    when there are very few dead tuples in the table. The cost of
    198+
    processing all of the table's indexes is expected to greatly
    199+
    exceed the benefit of removing dead index tuples when this
    200+
    happens. This option can be used to force
    201+
    <command>VACUUM</command> to process indexes when there are more
    202+
    than zero dead tuples. The default is <literal>AUTO</literal>,
    203+
    which allows <command>VACUUM</command> to skip index vacuuming
    204+
    when appropriate. If <literal>INDEX_CLEANUP</literal> is set to
    205+
    <literal>ON</literal>, <command>VACUUM</command> will
    206+
    conservatively remove all dead tuples from indexes. This may be
    207+
    useful for backwards compatibility with earlier releases of
    208+
    <productname>PostgreSQL</productname> where this was the
    209+
    standard behavior.
    210+
    </para>
    211+
    <para>
    212+
    <literal>INDEX_CLEANUP</literal> can also be set to
    213+
    <literal>OFF</literal> to force <command>VACUUM</command> to
    214+
    <emphasis>always</emphasis> skip index vacuuming, even when
    215+
    there are many dead tuples in the table. This may be useful
    216+
    when it is necessary to make <command>VACUUM</command> run as
    217+
    quickly as possible to avoid imminent transaction ID wraparound
    218+
    (see <xref linkend="vacuum-for-wraparound"/>). However, the
    219+
    wraparound failsafe mechanism controlled by <xref
    220+
    linkend="guc-vacuum-failsafe-age"/> will generally trigger
    221+
    automatically to avoid transaction ID wraparound failure, and
    222+
    should be preferred. If index cleanup is not performed
    223+
    regularly, performance may suffer, because as the table is
    224+
    modified indexes will accumulate dead tuples and the table
    225+
    itself will accumulate dead line pointers that cannot be removed
    226+
    until index cleanup is completed.
    227+
    </para>
    228+
    <para>
    229+
    This option has no effect for tables that have no index and is
    230+
    ignored if the <literal>FULL</literal> option is used. It also
    231+
    has no effect on the transaction ID wraparound failsafe
    232+
    mechanism. When triggered it will skip index vacuuming, even
    233+
    when <literal>INDEX_CLEANUP</literal> is set to
    234+
    <literal>ON</literal>.
    210235
    </para>
    211236
    </listitem>
    212237
    </varlistentry>
    @@ -217,7 +242,7 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
    217242
    <para>
    218243
    Specifies that <command>VACUUM</command> should attempt to process the
    219244
    corresponding <literal>TOAST</literal> table for each relation, if one
    220-
    exists. This is normally the desired behavior and is the default.
    245+
    exists. This is usually the desired behavior and is the default.
    221246
    Setting this option to false may be useful when it is only necessary to
    222247
    vacuum the main relation. This option is required when the
    223248
    <literal>FULL</literal> option is used.

    ‎doc/src/sgml/ref/vacuumdb.sgml

    Lines changed: 15 additions & 0 deletions
    Original file line numberDiff line numberDiff line change
    @@ -244,6 +244,21 @@ PostgreSQL documentation
    244244
    </listitem>
    245245
    </varlistentry>
    246246

    247+
    <varlistentry>
    248+
    <term><option>--force-index-cleanup</option></term>
    249+
    <listitem>
    250+
    <para>
    251+
    Always remove index entries pointing to dead tuples.
    252+
    </para>
    253+
    <note>
    254+
    <para>
    255+
    This option is only available for servers running
    256+
    <productname>PostgreSQL</productname> 12 and later.
    257+
    </para>
    258+
    </note>
    259+
    </listitem>
    260+
    </varlistentry>
    261+
    247262
    <varlistentry>
    248263
    <term><option>--no-process-toast</option></term>
    249264
    <listitem>

    ‎src/backend/access/common/reloptions.c

    Lines changed: 26 additions & 9 deletions
    EDF6
    Original file line numberDiff line numberDiff line change
    @@ -140,15 +140,6 @@ static relopt_bool boolRelOpts[] =
    140140
    },
    141141
    false
    142142
    },
    143-
    {
    144-
    {
    145-
    "vacuum_index_cleanup",
    146-
    "Enables index vacuuming and index cleanup",
    147-
    RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
    148-
    ShareUpdateExclusiveLock
    149-
    },
    150-
    true
    151-
    },
    152143
    {
    153144
    {
    154145
    "vacuum_truncate",
    @@ -474,6 +465,21 @@ static relopt_real realRelOpts[] =
    474465
    {{NULL}}
    475466
    };
    476467

    468+
    /* values from StdRdOptIndexCleanup */
    469+
    relopt_enum_elt_def StdRdOptIndexCleanupValues[] =
    470+
    {
    471+
    {"auto", STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO},
    472+
    {"on", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
    473+
    {"off", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
    474+
    {"true", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
    475+
    {"false", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
    476+
    {"yes", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
    477+
    {"no", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
    478+
    {"1", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON},
    479+
    {"0", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF},
    480+
    {(const char *) NULL} /* list terminator */
    481+
    };
    482+
    477483
    /* values from GistOptBufferingMode */
    478484
    relopt_enum_elt_def gistBufferingOptValues[] =
    479485
    {
    @@ -494,6 +500,17 @@ relopt_enum_elt_def viewCheckOptValues[] =
    494500

    495501
    static relopt_enum enumRelOpts[] =
    496502
    {
    503+
    {
    504+
    {
    505+
    "vacuum_index_cleanup",
    506+
    "Controls index vacuuming and index cleanup",
    507+
    RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
    508+
    ShareUpdateExclusiveLock
    509+
    },
    510+
    StdRdOptIndexCleanupValues,
    511+
    STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO,
    512+
    gettext_noop("Valid values are \"on\", \"off\", and \"auto\".")
    513+
    },
    497514
    {
    498515
    {
    499516
    "buffering",

    0 commit comments

    Comments
     (0)
    0