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

Bắt đầu với reloptions.c, đặc biệt là allocateReloptStruct() và ao_amoptions(), sau đó lần theo extract_autovac_opts() và cơ chế kế thừa TOAST trong autovacuum.c. Tái hiện vấn đề với bảng AO và các reloptions được cung cấp, đồng thời kiểm tra các quyết định và log của autovacuum. Công việc được xem là hoàn tất khi một regression test bao quát các bảng AO dạng hàng và dạng cột, các relation TOAST có tuổi thấp, các giá trị mặc định toàn cục và các tùy chọn TOAST được chỉ định rõ ràng, mà không có các lần vacuum wraparound lặp lại.

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:

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions.c#L286-L297

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

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions.c#L1735-L1760

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

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/access/common/reloptions_gp.c#L1911-L1931

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:

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L2855-L2887

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

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L2245-L2301

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:

   https://github.com/apache/cloudberry/blob/bdf90c5518f916f5dfea335c1cd83724e0ebe9e2/src/backend/postmaster/autovacuum.c#L3262-L3287

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:

  https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/access/common/reloptions_gp.c#L1911-L1931

• AO autovacuum option extraction:

  https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/postmaster/autovacuum.c#L2858-L2890

• TOAST inheritance:

  https://github.com/apache/cloudberry/blob/f684c7db45eb05198875b366a3832047cb3b5f86/src/backend/postmaster/autovacuum.c#L2248-L2304

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.