Home Explore Blog CI



postgresql

9th chunk of `doc/src/sgml/maintenance.sgml`
fb7dfcb6f4baf9c1662d10bf76f7eee89fdc725e9e5bee1a0000000100000fa8
 to freeze
    more all-visible pages by increasing <xref
    linkend="guc-vacuum-max-eager-freeze-failure-rate"/>. Even if eager
    scanning has kept the number of all-visible but not all-frozen pages to a
    minimum, most tables still require periodic aggressive vacuuming. However,
    any pages successfully eager frozen may be skipped during an aggressive
    vacuum, so eager freezing may minimize the overhead of aggressive vacuums.
   </para>

   <para>
    <xref linkend="guc-vacuum-freeze-table-age"/>
    controls when a table is aggressively vacuumed. All all-visible but not all-frozen
    pages are scanned if the number of transactions that have passed since the
    last such scan is greater than <varname>vacuum_freeze_table_age</varname> minus
    <varname>vacuum_freeze_min_age</varname>. Setting
    <varname>vacuum_freeze_table_age</varname> to 0 forces <command>VACUUM</command> to
    always use its aggressive strategy.
   </para>

   <para>
    The maximum time that a table can go unvacuumed is two billion
    transactions minus the <varname>vacuum_freeze_min_age</varname> value at
    the time of the last aggressive vacuum. If it were to go
    unvacuumed for longer than
    that, data loss could result.  To ensure that this does not happen,
    autovacuum is invoked on any table that might contain unfrozen rows with
    XIDs older than the age specified by the configuration parameter <xref
    linkend="guc-autovacuum-freeze-max-age"/>.  (This will happen even if
    autovacuum is disabled.)
   </para>

   <para>
    This implies that if a table is not otherwise vacuumed,
    autovacuum will be invoked on it approximately once every
    <varname>autovacuum_freeze_max_age</varname> minus
    <varname>vacuum_freeze_min_age</varname> transactions.
    For tables that are regularly vacuumed for space reclamation purposes,
    this is of little importance.  However, for static tables
    (including tables that receive inserts, but no updates or deletes),
    there is no need to vacuum for space reclamation, so it can
    be useful to try to maximize the interval between forced autovacuums
    on very large static tables.  Obviously one can do this either by
    increasing <varname>autovacuum_freeze_max_age</varname> or decreasing
    <varname>vacuum_freeze_min_age</varname>.
   </para>

   <para>
    The effective maximum for <varname>vacuum_freeze_table_age</varname> is 0.95 *
    <varname>autovacuum_freeze_max_age</varname>; a setting higher than that will be
    capped to the maximum. A value higher than
    <varname>autovacuum_freeze_max_age</varname> wouldn't make sense because an
    anti-wraparound autovacuum would be triggered at that point anyway, and
    the 0.95 multiplier leaves some breathing room to run a manual
    <command>VACUUM</command> before that happens.  As a rule of thumb,
    <command>vacuum_freeze_table_age</command> should be set to a value somewhat
    below <varname>autovacuum_freeze_max_age</varname>, leaving enough gap so that
    a regularly scheduled <command>VACUUM</command> or an autovacuum triggered by
    normal delete and update activity is run in that window.  Setting it too
    close could lead to anti-wraparound autovacuums, even though the table
    was recently vacuumed to reclaim space, whereas lower values lead to more
    frequent aggressive vacuuming.
   </para>

   <para>
    The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</varname>
    (and <varname>vacuum_freeze_table_age</varname> along with it) is that
    the <filename>pg_xact</filename> and <filename>pg_commit_ts</filename>
    subdirectories of the database cluster will take more space, because it
    must store the commit status and (if <varname>track_commit_timestamp</varname> is
    enabled) timestamp of all transactions back to
    the <varname>autovacuum_freeze_max_age</varname> horizon.  The commit status uses
    two bits per transaction, so if
    <varname>autovacuum_freeze_max_age</varname>

Title: Vacuum Freeze Parameters and Autovacuum
Summary
This section discusses the parameters that control when and how PostgreSQL's VACUUM command freezes row versions to prevent transaction ID wraparound, including the relationship between vacuum_freeze_table_age, autovacuum_freeze_max_age, and vacuum_freeze_min_age, and how to balance the need for frequent vacuuming with the desire to minimize overhead and optimize performance, particularly for large static tables.