Fixes inconsistent behavior in vacuum when it processes multiple relations
shihao zhong <zhong950419@gmail.com>
From: shihao zhong <zhong950419@gmail.com>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-06-18T15:15:31Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Rationalize handling of VacuumParams
- 2252fcd4276c 19 (unreleased) landed
-
Avoid scribbling of VACUUM options
- 65c3223f93a0 13.22 landed
- c079ba3fc2a4 14.19 landed
- 354944663df3 15.14 landed
- d187cabddadc 16.10 landed
- 2e0b5d252b10 17.6 landed
- 661643dedad9 18.0 landed
Attachments
- vacuum_tables_options.patch (application/octet-stream) patch
Hi team,
One of our customers recently encountered an issue with PostgreSQL's
pg_cron and VACUUM ANALYZE. They had configured a table with
vacuum_truncate=false to prevent exclusive lock contention, assuming
this would apply globally. However, VACUUM ANALYZE executed across the
entire database doesn't honor this table-specific setting, though
autovacuum does.
I investigated the code and found a small bug with how we're passing
the VacuumParams pointer.
The call flow is
ExecVacuum -> vacuum -> vacuum_rel
The initial VaccumParams pointer is set in ExecVacuum
In vacuum_rel, this pointer might change because it needs to determine
whether to truncate and perform index_cleanup.
Vacuuming a single table works as expected because the options are
applied at the table level. However, when vacuuming multiple tables,
the second table reuses the modified parameters set by the first
table's vacuum_rel.
We can easy repro that with following SQL and same with index_cleanup:
create table test(id int);
create table test_1(id int) with (vacuum_truncate=false);
insert into test select generate_series(1,1000);
insert into test_1 select generate_series(1,1000);
delete from test;
delete from test_1;
vacuum (analyze) test_1, test;
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method |
Size | Description
--------+--------+-------+----------+-------------+---------------+-------+-------------
public | test | table | postgres | permanent | heap | 72 kB |
public | test_1 | table | postgres | permanent | heap | 72 kB |
(2 rows)
I've implemented a fix and included a regression test in the patch.
Thanks,
Shihao