apache / apache/cloudberry

[Bug] VACUUM and VACUUM FULL appear to be ineffective on PAX tables.

Open
#1,766 1 comment 0 reactions 1 assignee Claimed by @gfphoenix78 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

2.1

### What happened

After inserting a large amount of data into a PAX table and deleting most of the rows, running either VACUUM or VACUUM FULL does not reduce the table size / disk usage.

### What you think should happen instead

- VACUUM should reclaim or compact storage occupied by deleted rows, if supported.
- VACUUM FULL should reclaim the space more aggressively, likely by rewriting/compacting the table.
- If VACUUM / VACUUM FULL is currently unsupported for PAX tables, Cloudberry should return a clear warning/error instead of silently doing nothing.

### How to reproduce

```
DROP TABLE IF EXISTS pax_vacuum_repro;

CREATE TABLE pax_vacuum_repro (
id int,
payload text
) USING PAX
DISTRIBUTED BY (id);

INSERT INTO pax_vacuum_repro
SELECT
g,
repeat(md5(g::text), 50)
FROM generate_series(1, 5000000) AS g;

-- Check initial row count and size.
SELECT count(*) FROM pax_vacuum_repro;
SELECT pg_size_pretty(pg_total_relation_size('pax_vacuum_repro')) AS size_before_delete;>

-- Delete most rows.
DELETE FROM pax_vacuum_repro
WHERE id <= 4500000;

SELECT count(*) FROM pax_vacuum_repro;
SELECT pg_size_pretty(pg_total_relation_size('pax_vacuum_repro')) AS size_after_delete;

analyze pax_vacuum_repro;
-- Try normal VACUUM.
VACUUM pax_vacuum_repro;
analyze pax_vacuum_repro;

SELECT pg_size_pretty(pg_total_relation_size('pax_vacuum_repro')) AS size_after_vacuum;

-- Try VACUUM FULL.
VACUUM FULL pax_vacuum_repro;
analyze pax_vacuum_repro;
SELECT pg_size_pretty(pg_total_relation_size('pax_vacuum_repro')) AS size_after_vacuum_full;

```
### Operating System

rocky 9.6

### Anything else

_No response_

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

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.