apache / apache/cloudberry

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

Open
#1,915 0 comments 2 reactions 0 assignees View on GitHub
type: Bug
Dominant language
C
Stars
1.4k
Forks
247
Avg merge
4d 3h
Merged PRs (30d)
39

Description

### 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:

```text
automatic aggressive vacuum to prevent wraparound of table ".pg_toast.pg_toast_"
```

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:

```text
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:

```text
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:

```sql
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:

```sql
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:

```sql
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:

```bash
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:

```text
automatic aggressive vacuum to prevent wraparound of table ".pg_toast.pg_toast_"
```

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:

```sql
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:

```sql
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:

```c
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

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).

Contributor guide

Open the contributing guide

Research direction

Start with reloptions.c, especially allocateReloptStruct() and ao_amoptions(), then trace extract_autovac_opts() and TOAST inheritance in autovacuum.c. Reproduce the issue with the supplied AO table and reloptions, and inspect the autovacuum decisions and logs. Done means a regression test covers AO row and column tables, low-age TOAST relations, global defaults, and explicit TOAST options without repeated wraparound vacuums.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.