[Bug] VACUUM and VACUUM FULL appear to be ineffective on PAX tables.
- 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
Assessment
This issue has not been assessed yet.