Re: Visibility map and freezing

Heikki Linnakangas wrote:
> Peter Eisentraut wrote:
>> Heikki Linnakangas wrote:
>>> I think we need a threshold similar to autovacuum_freeze_max_age for
>>> manual vacuums as well: vacuum_freeze_max_age. If you run VACUUM, and
>>> relfrozenxid is older than vacuum_freeze_max_age, the visibility map
>>> is ignored and all pages are scanned.
>>
>> Would one parameter to control both suffice?  (i.e., rename
>> autovacuum_freeze_max_age to vacuum_freeze_max_age)
>
> Imagine that you run a nightly VACUUM from cron, and have autovacuum
> disabled. If autovacuum_freeze_max_age is the same as
> vacuum_freeze_max_age, as soon as that age is reached, an
> anti-wraparound autovacuum launched. What we'd want to happen is for the
> next nightly VACUUM to do the work instead. So they need to be separate
> settings, with some space between them by default.

Attached is a proposed patch to handle freezing. In a nutshell:

Introduce new vacuum_freeze_max_age setting. Manual VACUUM will scan the
whole table and advance relfrozenxid, if relfrozenxid is older than
vacuum_freeze_max_age.

If vacuum_freeze_max_age is greater than autovacuum_freeze_max_age - 1
million transactions, it's effectively capped at that value. It doesn't
make sense to have vacuum_freeze_max_age > autovacuum_freeze_max_age,
because the manual VACUUM wouldn't have a chance to do the full sweep
before the anti-wraparound autovacuum is launched. The "minus one
million transactions" is to give some headroom.

I'm lowering vacuum_freeze_min_age from 100 million transactions to 50
million, so that the whole-table vacuum doesn't need to run as often.
Note that since VACUUM normally only scans pages that need vacuuming
according to the visibility map, tuples on skipped pages are not frozen
any earlier even though vacuum_freeze_min_age is lower.

To recap, here's the new defaults:
autovacuum_freeze_max_age    200000000
vacuum_freeze_max_age         150000000
vacuum_freeze_min_age          50000000

This means that with defaults, autovacuum will launch a whole-table
vacuum every 150 million transactions (autovacuum_freeze_max_age -
vacuum_freeze_min_age). If you run a nightly manual VACUUM, you'll get a
whole-table vacuum every 100 million transactions.

vacuum_freeze_max_age also affects autovacuums. If an autovacuum is
launched on table to remove dead tuples, and vacuum_freeze_max_age has
been reached (but not yet autovacuum_freeze_max_age), the autovacuum
will scan the whole table. I'm not sure if this is desirable, to avoid
having to launch separate anti-wraparound autovacuums even when there's
not many dead tuples, or just confusing.

If you set vacuum_freeze_max_age to 0, the visibility map is not used to
skip pages, so you'll get the pre-8.4 old behavior.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7493ca9..9848ce0 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3925,6 +3925,26 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
       </listitem>
      </varlistentry>

+     <varlistentry id="guc-vacuum-freeze-max-age" xreflabel="vacuum_freeze_max_age">
+      <term><varname>vacuum_freeze_max_age</varname> (<type>integer</type>)</term>
+      <indexterm>
+       <primary><varname>vacuum_freeze_max_age</> configuration parameter</primary>
+      </indexterm>
+      <listitem>
+       <para>
+        <command>VACUUM</> performs a whole-table scan if the table's
+        <structname>pg_class</>.<structfield>relfrozenxid</> field reaches the
+        age specified by this setting.  The default is 150 million
+        transactions.  Although users can set this value anywhere from zero to
+        one billion, <command>VACUUM</> will silently limit the effective value
+        to the value of <xref linkend="guc-autovacuum-freeze-max-age"> minus
+        1 million transactions, so that regular manual <command>VACUUM</> has a
+        chance to run before autovacuum is launched to prevent XID wraparound.
+        For more information see <xref linkend="vacuum-for-wraparound">.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-vacuum-freeze-min-age" xreflabel="vacuum_freeze_min_age">
       <term><varname>vacuum_freeze_min_age</varname> (<type>integer</type>)</term>
       <indexterm>
@@ -3935,7 +3955,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
         Specifies the cutoff age (in transactions) that <command>VACUUM</>
         should use to decide whether to replace transaction IDs with
         <literal>FrozenXID</> while scanning a table.
-        The default is 100 million transactions.  Although
+        The default is 50 million transactions.  Although
         users can set this value anywhere from zero to one billion,
         <command>VACUUM</> will silently limit the effective value to half
         the value of <xref linkend="guc-autovacuum-freeze-max-age">, so
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 03ce2e9..c41d464 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -367,10 +367,14 @@
    </para>

    <para>
-    <command>VACUUM</>'s behavior is controlled by the configuration parameter
-    <xref linkend="guc-vacuum-freeze-min-age">: any XID older than
-    <varname>vacuum_freeze_min_age</> transactions is replaced by
-    <literal>FrozenXID</>.  Larger values of <varname>vacuum_freeze_min_age</>
+    <command>VACUUM</>'s behavior is controlled by the two configuration
+    parameters: <xref linkend="guc-vacuum-freeze-min-age"> and
+    <xref linkend="guc-vacuum-freeze-max-age">.
+    <varname>vacuum_freeze_max_age</> controls when <command>VACUUM</>
+    performs a full sweep of the table to replace old XID values with
+    <literal>FrozenXID</>.  <varname>vacuum_freeze_min_age</>
+    controls how old an XID value has to be before it's replaced with
+    <literal>FrozenXID</>.  Larger values of these settings
     preserve transactional information longer, while smaller values increase
     the number of transactions that can elapse before the table must be
     vacuumed again.
@@ -379,7 +383,8 @@
    <para>
     The maximum time that a table can go unvacuumed is two billion
     transactions minus the <varname>vacuum_freeze_min_age</> that was used
-    when it was last vacuumed.  If it were to go unvacuumed for longer than
+    when <command>VACUUM</> last scanned the whole table.  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 XIDs older than the
     age specified by the configuration parameter <xref
@@ -403,7 +408,8 @@
    </para>

    <para>
-    The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</>
+    The sole disadvantage of increasing <varname>vacuum_freeze_max_age</>
+    and <varname>autovacuum_freeze_max_age</>
     is that the <filename>pg_clog</> subdirectory of the database cluster
     will take more space, because it must store the commit status for all
     transactions back to the <varname>autovacuum_freeze_max_age</> horizon.
@@ -459,9 +465,20 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
     <command>VACUUM</>, <literal>age(relfrozenxid)</> should be a little
     more than the <varname>vacuum_freeze_min_age</> setting that was used
     (more by the number of transactions started since the <command>VACUUM</>
-    started).  If <literal>age(relfrozenxid)</> exceeds
-    <varname>autovacuum_freeze_max_age</>, an autovacuum will soon be forced
-    for the table.
+    started).
+   </para>
+
+   <para>
+    <command>VACUUM</> normally only scans pages that have been modified
+    since last vacuum, but <structfield>relfrozenxid</> can only be advanced
+    when the whole table is scanned. The whole table is scanned when
+    <structfield>relfrozenxid</> is more than
+    <varname>vacuum_freeze_max_age</> transactions old, if
+    <command>VACUUM FREEZE</> command is used, or if all pages happen to
+    require vacuuming to remove dead row versions.
+    If no whole-table-scanning <command>VACUUM</> is issued on the table until
+    <varname>autovacuum_freeze_max_age</> is reached, an autovacuum will soon
+    be forced for the table.
    </para>

    <para>
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index c8e4226..39e0cca 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -62,6 +62,7 @@
  * GUC parameters
  */
 int            vacuum_freeze_min_age;
+int            vacuum_freeze_max_age;

 /*
  * VacPage structures keep track of each page on which we find useful
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index acb02a4..a82fea5 100644
--- a/src/backend/commands/vacuumlazy.c
+++ b/src/backend/commands/vacuumlazy.c
@@ -144,6 +144,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
     BlockNumber possibly_freeable;
     PGRUsage    ru0;
     TimestampTz starttime = 0;
+    bool        scan_all;

     pg_rusage_init(&ru0);

@@ -156,6 +157,42 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
     else
         elevel = DEBUG2;

+    /*
+     * Determine if we should scan the whole relation, either because we were
+     * told to, or because relfrozenxid has reached vacuum_freeze_max_age.
+     */
+    if (vacstmt->scan_all)
+        scan_all = true;
+    else
+    {
+        TransactionId limit;
+        TransactionId effective_max_age;
+
+        /*
+         * Limit vacuum_freeze_max_age to autovacuum_freeze_max_age - 1
+         * million transactions, so that regular VACUUM gets a chance to
+         * freeze tuples before anti-wraparound autovacuum is launched.
+         */
+        if (vacuum_freeze_max_age > autovacuum_freeze_max_age - 1000000)
+            effective_max_age = autovacuum_freeze_max_age - 1000000;
+        else
+            effective_max_age = vacuum_freeze_max_age;
+
+        /*
+         * Compute the cutoff XID, being careful not to generate a
+         * "permanent" XID
+         */
+        limit = ReadNewTransactionId() - effective_max_age;
+        if (!TransactionIdIsNormal(limit))
+            limit = FirstNormalTransactionId;
+
+        if (TransactionIdPrecedes(onerel->rd_rel->relfrozenxid, limit))
+            scan_all = true;
+        else
+            scan_all = false;
+    }
+
+
     vac_strategy = bstrategy;

     vacuum_set_xid_limits(vacstmt->freeze_min_age, onerel->rd_rel->relisshared,
@@ -171,7 +208,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
     vacrelstats->hasindex = (nindexes > 0);

     /* Do the vacuuming */
-    lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, vacstmt->scan_all);
+    lazy_scan_heap(onerel, vacrelstats, Irel, nindexes, scan_all);

     /* Done with indexes */
     vac_close_indexes(nindexes, Irel, NoLock);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index b912df0..cf27055 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1533,7 +1533,16 @@ static struct config_int ConfigureNamesInt[] =
             NULL
         },
         &vacuum_freeze_min_age,
-        100000000, 0, 1000000000, NULL, NULL
+        50000000, 0, 1000000000, NULL, NULL
+    },
+
+    {
+        {"vacuum_freeze_max_age", PGC_USERSET, CLIENT_CONN_STATEMENT,
+            gettext_noop("Age at which VACUUM should scan whole table to freeze tuples."),
+            NULL
+        },
+        &vacuum_freeze_max_age,
+        150000000, 0, 2000000000, NULL, NULL
     },

     {
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index bb51632..e81e7c8 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -122,6 +122,7 @@ typedef struct VacAttrStats
 extern PGDLLIMPORT int default_statistics_target;        /* PGDLLIMPORT for
                                                          * PostGIS */
 extern int    vacuum_freeze_min_age;
+extern int    vacuum_freeze_max_age;


 /* in commands/vacuum.c */