[Bug] AO storage reloptions are copied as zeroed autovacuum options, causing repeated aggressive TOAST wraparound vacuums

[ryapandt]

Apache Cloudberry version

Apache Cloudberry 2.1.0

What happened

Cloudberry repeatedly performs automatic aggressive wraparound vacuums on thousands of nearly empty TOAST relations belonging to append-optimized row tables.

Typical log message:

automatic aggressive vacuum to prevent wraparound of table "<database>.pg_toast.pg_toast_<oid>"

This happens even though the affected TOAST relations are nowhere near the configured wraparound threshold.

Production observations from two databases:

• One database repeatedly vacuumed exactly 1,231 TOAST relations per cycle.
• Another database repeatedly vacuumed exactly 2,198 TOAST relations per cycle.
• These counts exactly matched the number of AO row parent tables having non-empty storage reloptions.
• The same TOAST OIDs were processed again during every autovacuum cycle.
• All affected parent tables used the ao_row access method.
• Their reloptions contained only AO storage settings such as compresstype, compresslevel, blocksize, or checksum.
• Affected TOAST relations had transaction ID ages of only a few hundred; the maximum observed age was below 1,000.
• Their multixact ages were zero.
• None was close to the normal autovacuum_freeze_max_age value of approximately 200 million transactions.
• The vacuums commonly reported zero pages and zero tuples.
• There was no cutoff for removing and freezing tuples is far in the past warning.
• log_autovacuum_min_duration was globally set to -1, but these vacuums were still logged.
• In one five-second sample, 466 aggressive vacuum records were written and the logs grew by approximately 1 MB.

This causes continuous autovacuum worker activity, CPU consumption, buffer accesses, WAL generation, and very large log volume on databases containing many AO tables.

This may explain the still-unresolved AO/TOAST behavior reported in #1850. However, this case is deterministic and is not caused by a held-back OldestXmin: the affected relations have very low XID ages, there is no old-Xmin warning, and the affected set exactly matches AO parents with storage reloptions.

The problem appears to be caused by an interaction between AO reloption parsing and TOAST autovacuum option inheritance.

1. Autovacuum reloptions such as autovacuum_freeze_max_age are registered only for RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, not for RELOPT_KIND_APPENDOPTIMIZED:

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

   Lines 286 to 297 in bdf90c5

   	-1, 0, 1000000000
   	},
   	{
   	{
   	"autovacuum_freeze_max_age",
   	"Age at which to autovacuum a table to prevent transaction ID wraparound",
   	RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
   	ShareUpdateExclusiveLock
   	},
   	-1, 100000, 2000000000
   	},
   	{

2. allocateReloptStruct() zero-initializes the complete StdRdOptions structure using palloc0():

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

   Lines 1735 to 1760 in bdf90c5

   	allocateReloptStruct(Size base, relopt_value *options, int numoptions)
   	{
   	Size size = base;
   	int i;
   	for (i = 0; i < numoptions; i++)
   	{
   	relopt_value *optval = &options[i];
   	if (optval->gen->type == RELOPT_TYPE_STRING)
   	{
   	relopt_string *optstr = (relopt_string *) optval->gen;
   	if (optstr->fill_cb)
   	{
   	const char *val = optval->isset ? optval->values.string_val :
   	optstr->default_isnull ? NULL : optstr->default_val;
   	size += optstr->fill_cb(val, NULL);
   	}
   	else
   	size += GET_STRING_RELOPTION_LEN(*optval) + 1;
   	}
   	}
   	return palloc0(size);

3. ao_amoptions() parses AO parent-table reloptions using only RELOPT_KIND_APPENDOPTIMIZED:

   cloudberry/src/backend/access/common/reloptions_gp.c

   Lines 1911 to 1931 in bdf90c5

   	ao_amoptions(Datum reloptions, char relkind, bool validate)
   	{
   	StdRdOptions *rdopts;
   	switch (relkind)
   	{
   	case RELKIND_TOASTVALUE:
   	rdopts = (StdRdOptions *)
   	default_reloptions(reloptions, validate, RELOPT_KIND_TOAST);
   	if (rdopts != NULL)
   	{
   	/* adjust default-only parameters for TOAST relations */
   	rdopts->fillfactor = 100;
   	rdopts->autovacuum.analyze_threshold = -1;
   	rdopts->autovacuum.analyze_scale_factor = -1;
   	}
   	return (bytea *) rdopts;
   	case RELKIND_RELATION:
   	case RELKIND_MATVIEW:
   	return default_reloptions(reloptions, validate, RELOPT_KIND_APPENDOPTIMIZED);
   	default:

4. Therefore, when an AO table has storage reloptions, a non-NULL StdRdOptions is returned, but its embedded AutoVacOpts fields were never populated with the expected -1 sentinel/default values. They remain zero because of palloc0().

5. extract_autovac_opts() explicitly accepts AO_ROW_TABLE_AM_OID and AO_COLUMN_TABLE_AM_OID, then copies the zero-filled embedded AutoVacOpts:

   cloudberry/src/backend/postmaster/autovacuum.c

   Lines 2855 to 2887 in bdf90c5

   	extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc)
   	{
   	bytea *relopts;
   	AutoVacOpts *av;
   	Oid relam;
   	const TableAmRoutine *tam;
   	Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_DIRECTORY_TABLE ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOSEGMENTS ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOBLOCKDIR ||
   	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOVISIMAP);
   	relam = ((Form_pg_class) GETSTRUCT(tup))->relam;
   	tam = GetTableAmRoutineByAmId(relam);
   	/* FIXME: external TAM may have reloption other than StdRdOptions. */
   	if (relam != HEAP_TABLE_AM_OID &&
   	relam != AO_ROW_TABLE_AM_OID &&
   	relam != AO_COLUMN_TABLE_AM_OID)
   	return NULL;
   	relopts = extractRelOptions(tup, pg_class_desc, tam->amoptions);
   	if (relopts == NULL)
   	return NULL;
   	av = palloc(sizeof(AutoVacOpts));
   	memcpy(av, &(((StdRdOptions *) relopts)->autovacuum), sizeof(AutoVacOpts));
   	pfree(relopts);
   	return av;

6. A TOAST relation without its own reloptions inherits this copied structure from its AO parent:

   cloudberry/src/backend/postmaster/autovacuum.c

   Lines 2245 to 2301 in bdf90c5

   	if (!found)
   	{
   	/* hash_search already filled in the key */
   	hentry->ar_relid = relid;
   	hentry->ar_hasrelopts = false;
   	if (relopts != NULL)
   	{
   	hentry->ar_hasrelopts = true;
   	memcpy(&hentry->ar_reloptions, relopts,
   	sizeof(AutoVacOpts));
   	}
   	}
   	}
   	}
   	table_endscan(relScan);
   	/* second pass: check TOAST tables */
   	ScanKeyInit(&key,
   	Anum_pg_class_relkind,
   	BTEqualStrategyNumber, F_CHAREQ,
   	CharGetDatum(RELKIND_TOASTVALUE));
   	relScan = table_beginscan_catalog(classRel, 1, &key);
   	while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
   	{
   	Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
   	PgStat_StatTabEntry *tabentry;
   	Oid relid;
   	AutoVacOpts *relopts = NULL;
   	bool dovacuum;
   	bool doanalyze;
   	bool wraparound;
   	/*
   	* We cannot safely process other backends' temp tables, so skip 'em.
   	*/
   	if (classForm->relpersistence == RELPERSISTENCE_TEMP)
   	continue;
   	relid = classForm->oid;
   	/*
   	* fetch reloptions -- if this toast table does not have them, try the
   	* main rel
   	*/
   	relopts = extract_autovac_opts(tuple, pg_class_desc);
   	if (relopts == NULL)
   	{
   	av_relation *hentry;
   	bool found;
   	hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found);
   	if (found && hentry->ar_hasrelopts)
   	relopts = &hentry->ar_reloptions;
   	}

7. The resulting effective values include:

enabled = false
freeze_min_age = 0
freeze_max_age = 0
freeze_table_age = 0
multixact_freeze_max_age = 0
vacuum_cost_delay = 0
log_min_duration = 0

8. relation_needs_vacanalyze() considers any non-negative freeze_max_age to be an explicitly configured value:

   cloudberry/src/backend/postmaster/autovacuum.c

   Lines 3262 to 3287 in bdf90c5

   	freeze_max_age = (relopts && relopts->freeze_max_age >= 0)
   	? Min(relopts->freeze_max_age, autovacuum_freeze_max_age)
   	: autovacuum_freeze_max_age;
   	multixact_freeze_max_age = (relopts && relopts->multixact_freeze_max_age >= 0)
   	? Min(relopts->multixact_freeze_max_age, effective_multixact_freeze_max_age)
   	: effective_multixact_freeze_max_age;
   	av_enabled = (relopts ? relopts->enabled : true);
   	/* Force vacuum if table is at risk of wraparound */
   	xidForceLimit = recentXid - freeze_max_age;
   	if (xidForceLimit < FirstNormalTransactionId)
   	xidForceLimit -= FirstNormalTransactionId;
   	/*
   	* GPDB: Append-optimized tables don't have any transaction IDs and don't
   	* need to be considered for anti-wraparound vacuums. They are implicitly
   	* excluded from anti-wraparound vacuums below since their relfrozenxid is
   	* always InvalidTransactionId.
   	*/
   	AssertImply(IsAccessMethodAO(classForm->relam),
   	!TransactionIdIsValid(classForm->relfrozenxid));
   	force_vacuum = (TransactionIdIsNormal(classForm->relfrozenxid) &&
   	TransactionIdPrecedes(classForm->relfrozenxid,
   	xidForceLimit));

Because the inherited value is zero, the force limit becomes effectively:

xidForceLimit = recentXid - 0

Consequently, almost every normal TOAST relfrozenxid precedes the force limit and is immediately classified as requiring wraparound vacuum.

The forced-wraparound condition bypasses autovacuum_enabled=false, while freeze_table_age=0 makes the operation aggressive. The inherited log_min_duration=0 also explains why the operations are logged even when the global log_autovacuum_min_duration is -1.

What you think should happen instead

AO storage reloptions must not be interpreted as explicit autovacuum settings.

When an AO parent table has only compression, checksum, or block-size options:

• Its unused embedded AutoVacOpts values should not be copied as zero-valued overrides.
• Its TOAST relation should use its own explicit autovacuum reloptions, if any.
• Otherwise, the TOAST relation should use the normal global autovacuum defaults.
• A TOAST relation with an XID age of only a few hundred must not be classified as requiring wraparound vacuum.
• The global log_autovacuum_min_duration=-1 setting should remain effective unless a real per-table override exists.

How to reproduce

For faster reproduction, use a short autovacuum_naptime, for example one second. Keep log_autovacuum_min_duration=-1; this helps demonstrate that the zero-valued inherited option overrides the global setting.

Create an AO row table with a TOAST-able column and explicit AO storage reloptions:

CREATE SCHEMA av_ao_relopts_repro;

CREATE TABLE av_ao_relopts_repro.ao_with_storage_opts
(
    id integer,
    payload text
)
WITH
(
    appendonly=true,
    orientation=row,
    compresstype=zlib,
    compresslevel=1,
    checksum=true
)
DISTRIBUTED RANDOMLY;

Confirm the AO parent, its storage reloptions, and its TOAST relation:

SELECT
    n.nspname AS parent_schema,
    c.relname AS parent_relation,
    am.amname AS access_method,
    c.reloptions AS parent_reloptions,
    t.oid AS toast_oid,
    t.relname AS toast_relation,
    age(t.relfrozenxid) AS toast_xid_age,
    mxid_age(t.relminmxid) AS toast_mxid_age
FROM pg_class c
JOIN pg_namespace n
  ON n.oid = c.relnamespace
JOIN pg_am am
  ON am.oid = c.relam
JOIN pg_class t
  ON t.oid = c.reltoastrelid
WHERE n.nspname = 'av_ao_relopts_repro'
  AND c.relname = 'ao_with_storage_opts';

Advance several normal transactions:

SELECT txid_current();
SELECT txid_current();
SELECT txid_current();
SELECT txid_current();
SELECT txid_current();

For a continuous reproduction, an external session can generate one transaction at a time:

for i in $(seq 1 300); do
    psql -Atqc 'SELECT txid_current()' >/dev/null
    sleep 0.2
done

Wait for at least two autovacuum cycles and inspect the coordinator and segment logs.

Expected buggy result:

automatic aggressive vacuum to prevent wraparound of table "<database>.pg_toast.pg_toast_<toast_oid>"

The message appears while age(relfrozenxid) is still very small. As additional transactions are generated, the same TOAST relation is selected repeatedly.

To reproduce the high-volume effect, create multiple AO tables with storage reloptions:

DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..100 LOOP
        EXECUTE format(
            'CREATE TABLE av_ao_relopts_repro.ao_bug_%s
             (
                 id integer,
                 payload text
             )
             WITH
             (
                 appendonly=true,
                 orientation=row,
                 compresstype=zlib,
                 compresslevel=1,
                 checksum=true
             )
             DISTRIBUTED RANDOMLY',
            i
        );
    END LOOP;
END
$$;

After advancing transactions, the TOAST relations of these tables should be selected for aggressive vacuum repeatedly, despite their very low XID ages.

Cleanup:

DROP SCHEMA av_ao_relopts_repro CASCADE;

Operating System

rocky 9.6

Anything else

The exact problematic logic is still present in the latest REL_2_STABLE branch:

• AO option parsing:

  cloudberry/src/backend/access/common/reloptions_gp.c

  Lines 1911 to 1931 in f684c7d

  	ao_amoptions(Datum reloptions, char relkind, bool validate)
  	{
  	StdRdOptions *rdopts;
  	switch (relkind)
  	{
  	case RELKIND_TOASTVALUE:
  	rdopts = (StdRdOptions *)
  	default_reloptions(reloptions, validate, RELOPT_KIND_TOAST);
  	if (rdopts != NULL)
  	{
  	/* adjust default-only parameters for TOAST relations */
  	rdopts->fillfactor = 100;
  	rdopts->autovacuum.analyze_threshold = -1;
  	rdopts->autovacuum.analyze_scale_factor = -1;
  	}
  	return (bytea *) rdopts;
  	case RELKIND_RELATION:
  	case RELKIND_MATVIEW:
  	return default_reloptions(reloptions, validate, RELOPT_KIND_APPENDOPTIMIZED);
  	default:

• AO autovacuum option extraction:

  cloudberry/src/backend/postmaster/autovacuum.c

  Lines 2858 to 2890 in f684c7d

  	extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc)
  	{
  	bytea *relopts;
  	AutoVacOpts *av;
  	Oid relam;
  	const TableAmRoutine *tam;
  	Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_DIRECTORY_TABLE ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOSEGMENTS ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOBLOCKDIR ||
  	((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_AOVISIMAP);
  	relam = ((Form_pg_class) GETSTRUCT(tup))->relam;
  	tam = GetTableAmRoutineByAmId(relam);
  	/* FIXME: external TAM may have reloption other than StdRdOptions. */
  	if (relam != HEAP_TABLE_AM_OID &&
  	relam != AO_ROW_TABLE_AM_OID &&
  	relam != AO_COLUMN_TABLE_AM_OID)
  	return NULL;
  	relopts = extractRelOptions(tup, pg_class_desc, tam->amoptions);
  	if (relopts == NULL)
  	return NULL;
  	av = palloc(sizeof(AutoVacOpts));
  	memcpy(av, &(((StdRdOptions *) relopts)->autovacuum), sizeof(AutoVacOpts));
  	pfree(relopts);
  	return av;

• TOAST inheritance:

  cloudberry/src/backend/postmaster/autovacuum.c

  Lines 2248 to 2304 in f684c7d

  	if (!found)
  	{
  	/* hash_search already filled in the key */
  	hentry->ar_relid = relid;
  	hentry->ar_hasrelopts = false;
  	if (relopts != NULL)
  	{
  	hentry->ar_hasrelopts = true;
  	memcpy(&hentry->ar_reloptions, relopts,
  	sizeof(AutoVacOpts));
  	}
  	}
  	}
  	}
  	table_endscan(relScan);
  	/* second pass: check TOAST tables */
  	ScanKeyInit(&key,
  	Anum_pg_class_relkind,
  	BTEqualStrategyNumber, F_CHAREQ,
  	CharGetDatum(RELKIND_TOASTVALUE));
  	relScan = table_beginscan_catalog(classRel, 1, &key);
  	while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
  	{
  	Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
  	PgStat_StatTabEntry *tabentry;
  	Oid relid;
  	AutoVacOpts *relopts = NULL;
  	bool dovacuum;
  	bool doanalyze;
  	bool wraparound;
  	/*
  	* We cannot safely process other backends' temp tables, so skip 'em.
  	*/
  	if (classForm->relpersistence == RELPERSISTENCE_TEMP)
  	continue;
  	relid = classForm->oid;
  	/*
  	* fetch reloptions -- if this toast table does not have them, try the
  	* main rel
  	*/
  	relopts = extract_autovac_opts(tuple, pg_class_desc);
  	if (relopts == NULL)
  	{
  	av_relation *hentry;
  	bool found;
  	hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found);
  	if (found && hentry->ar_hasrelopts)
  	relopts = &hentry->ar_reloptions;
  	}

One possible minimal fix is to prevent an AO parent relation from returning an AutoVacOpts structure when AO autovacuum reloptions are not supported:

relam = ((Form_pg_class) GETSTRUCT(tup))->relam;

if (IsAccessMethodAO(relam))
    return NULL;

AO auxiliary relations and TOAST relations use the heap access method, so this guard would only prevent the invalid AO parent options from being inherited.

Another possible fix is to initialize unsupported/missing AutoVacOpts members to their intended -1 sentinel values instead of leaving them zero.

A regression test should cover both AO row and AO column tables with non-empty storage reloptions and verify that:

• Their low-age TOAST relations are not marked for wraparound vacuum.
• The configured global autovacuum_freeze_max_age is used.
• log_autovacuum_min_duration=-1 is not overridden by an unintended zero value.
• Explicit TOAST autovacuum options continue to work.

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.