Vacuum is no longer capped at one gigabyte

A ceiling that was never in the documentation you read

Vacuum works in rounds. It scans the heap and collects pointers to dead rows until it runs out of room to hold them, then it visits every index on the table to remove those pointers, then it goes back to scanning where it left off. The amount of room is what maintenance_work_mem buys, and the practical consequence is arithmetic: a table with enough dead rows to fill that memory twice will have every one of its indexes read twice.

Up to and including 16, raising the setting stopped helping at a point nobody announced on the page where the setting is documented. The structure holding the pointers was a single flat array, and a single allocation of that kind is bounded inside the server. Set maintenance_work_mem to eight gigabytes on a large table and vacuum used one, silently, and made the same number of index passes it would have made at one gigabyte. The release notes for 17 state the old behaviour plainly, under General Performance in the PostgreSQL 17 release notes: vacuum is no longer silently limited to one gigabyte when the setting is higher.

What replaced the array is a store built for this shape of data. Dead row pointers arrive sorted and clustered, many of them on the same heap page, and the new structure keeps them per page rather than as independent six-byte values. It uses less memory for the same number of dead rows, and it grows as needed rather than being allocated once at its maximum size.

Asking each server what budget it was given

Showing the budget means catching a vacuum in progress, so the fixture runs one from a second connection and reads the progress view. Two tables, so that the second measurement below gets its own untouched heap.

CREATE EXTENSION dblink;
CREATE TABLE audit_event (audit_id bigint PRIMARY KEY, actor text, action text);
INSERT INTO audit_event
SELECT g, 'user-' || (g % 800), CASE WHEN g % 2 = 0 THEN 'update' ELSE 'insert' END
FROM generate_series(1, 300000) AS g;
CREATE INDEX audit_event_actor ON audit_event (actor);
CREATE TABLE audit_event_archive (archive_id bigint PRIMARY KEY, actor text);
INSERT INTO audit_event_archive SELECT g, 'user-' || (g % 800) FROM generate_series(1, 300000) AS g;
CREATE INDEX audit_event_archive_actor ON audit_event_archive (actor);
DELETE FROM audit_event WHERE audit_id % 3 = 0;
DELETE FROM audit_event_archive WHERE archive_id % 3 = 0;

CREATE EXTENSION
CREATE TABLE
INSERT 0 300000
CREATE INDEX
CREATE TABLE
INSERT 0 300000
CREATE INDEX
DELETE 100000
DELETE 100000

On 16, with two gigabytes offered, the view reports a capacity in tuples.

DO $$
BEGIN
  PERFORM dblink_connect('audit_sweep', 'dbname=' || current_database() || ' user=postgres');
  PERFORM dblink_exec('audit_sweep', 'SET maintenance_work_mem = ''2GB''');
  PERFORM dblink_exec('audit_sweep', 'SET vacuum_cost_delay = 20');
  PERFORM dblink_exec('audit_sweep', 'SET vacuum_cost_limit = 60');
  PERFORM dblink_send_query('audit_sweep', 'VACUUM audit_event');
  PERFORM pg_sleep(1);
END $$;

SELECT relid::regclass AS table_name, max_dead_tuples, num_dead_tuples
FROM pg_stat_progress_vacuum
WHERE relid = 'audit_event'::regclass;

DO
 table_name  | max_dead_tuples | num_dead_tuples 
-------------+-----------------+-----------------
 audit_event |          556101 |          100000
(1 row)

That number is not two gigabytes divided by anything. On 16 the array is also bounded by an estimate of how many dead rows the table could possibly contain, so on a small table the setting is not the binding constraint and the reported capacity is much lower than the memory would allow. On a table large enough for the memory to bind, the ceiling the release note describes is what you would meet instead. This fixture is deliberately small, so what it demonstrates is the unit and the derivation, not the gigabyte itself.

On 17 the same two gigabytes are reported as two gigabytes.

DO $$
BEGIN
  PERFORM dblink_connect('audit_sweep', 'dbname=' || current_database() || ' user=postgres');
  PERFORM dblink_exec('audit_sweep', 'SET maintenance_work_mem = ''2GB''');
  PERFORM dblink_exec('audit_sweep', 'SET vacuum_cost_delay = 20');
  PERFORM dblink_exec('audit_sweep', 'SET vacuum_cost_limit = 60');
  PERFORM dblink_send_query('audit_sweep', 'VACUUM audit_event');
  PERFORM pg_sleep(1);
END $$;

SELECT relid::regclass AS table_name, max_dead_tuple_bytes, dead_tuple_bytes, num_dead_item_ids
FROM pg_stat_progress_vacuum
WHERE relid = 'audit_event'::regclass;

DO
 table_name  | max_dead_tuple_bytes | dead_tuple_bytes | num_dead_item_ids 
-------------+----------------------+------------------+-------------------
 audit_event |           2147483648 |          1048576 |            100000
(1 row)

The budget is above a gigabyte, which is the change, stated as a measurement rather than as a claim about the previous version. dead_tuple_bytes is what the store is actually occupying, and it is far smaller than the number of dead pointers would suggest, because pointers sharing a heap page are held together.

Turning the setting down and watching the budget follow

The other half of “it grows as needed” is that it is genuinely bounded by the setting rather than by an internal constant. The second table, vacuumed with a deliberately small budget:

DO $$
BEGIN
  PERFORM dblink_connect('archive_sweep', 'dbname=' || current_database() || ' user=postgres');
  PERFORM dblink_exec('archive_sweep', 'SET maintenance_work_mem = ''4MB''');
  PERFORM dblink_exec('archive_sweep', 'SET vacuum_cost_delay = 20');
  PERFORM dblink_exec('archive_sweep', 'SET vacuum_cost_limit = 60');
  PERFORM dblink_send_query('archive_sweep', 'VACUUM audit_event_archive');
  PERFORM pg_sleep(1);
END $$;

SELECT relid::regclass AS table_name, max_dead_tuple_bytes, dead_tuple_bytes
FROM pg_stat_progress_vacuum
WHERE relid = 'audit_event_archive'::regclass;

DO
     table_name      | max_dead_tuple_bytes | dead_tuple_bytes 
---------------------+----------------------+------------------
 audit_event_archive |              4194304 |           524288
(1 row)

The ceiling is exactly the setting. That is the property the old array did not have in either direction: below, because the capacity was derived from a table-size estimate as well as from memory, and above, because of the allocation limit.

Fewer passes is the whole benefit

None of this makes a single index pass faster. What it changes is how many of them a large table needs, and that is usually where a long vacuum goes. Every extra pass reads every index on the table from the beginning.

The practical consequences are worth separating, because only one of them is an action.

• On a very large table where vacuum was making several passes, raising maintenance_work_mem above a gigabyte now does something on 17 where it did nothing on 16. That is the action.
• On a table whose vacuum already completed in one pass, nothing changes. Raising the setting buys nothing on either version and costs real memory per autovacuum worker.
• Autovacuum uses autovacuum_work_mem when it is set, and that is the setting that matters for the work happening while nobody is watching. Raising the interactive one and leaving the background one alone is a common and disappointing mistake.

Whether a table is making several passes is visible directly on 17, in the pass counter of the vacuum progress view, so this is one of the rare tuning decisions with a measurement in front of it rather than behind it.

What it costs, and what it does not

The store is allocated as vacuum needs it rather than up front, so the change also reduces what a vacuum costs when there is little to do. On 16 a vacuum of a nearly clean large table would still take its allocation; on 17 it takes what it uses.

There is no setting to enable and no restart. The one thing to keep in mind is that a budget is per vacuum, and autovacuum can run several workers at once, so the memory a fleet should reserve is the setting multiplied by the worker count rather than the setting itself. That was true before 17 as well; what is new is that the number you write down is now the number that will be used.