Vacuum progress stopped counting in tuples

A progress bar whose unit was an implementation detail

The progress view for vacuum has always reported two things about memory. One is the budget: how much room this vacuum has to remember the dead row pointers it finds before it must stop scanning and go clean the indexes. The other is how much of that budget has been used. Before 17 both were expressed as a count of tuples, because the structure holding them was a flat array of six-byte item pointers and dividing the memory by six gave a tuple capacity directly.

That arithmetic stopped being true in 17, when the array was replaced by a store that compresses runs of pointers on the same page. There is no longer a fixed number of bytes per dead tuple, so a capacity expressed in tuples would be a guess rather than a measurement. The two columns were renamed to say what they now hold: the budget became max_dead_tuple_bytes, and the count of item identifiers found became num_dead_item_ids. A third column, dead_tuple_bytes, was added to report how much of the budget the store is actually occupying.

The rename is recorded under Migration to Version 17 in the PostgreSQL 17 release notes, alongside the other changes that stop existing queries. The store itself is a separate entry in the same release, and it is the reason vacuum is no longer capped at a gigabyte.

Polling a live vacuum on each server

The progress view has rows only while a vacuum is running, so the fixture starts one from a second connection and then looks at it. A shipment table with two secondary indexes, a quarter of it deleted, and a cost delay large enough that the vacuum is still going when we ask.

CREATE EXTENSION dblink;
CREATE TABLE shipment (shipment_id bigint PRIMARY KEY, carrier text, weight_g integer);
INSERT INTO shipment
SELECT g, 'carrier-' || (g % 40), (g * 13) % 30000
FROM generate_series(1, 300000) AS g;
CREATE INDEX shipment_carrier ON shipment (carrier);
CREATE INDEX shipment_weight ON shipment (weight_g);
DELETE FROM shipment WHERE shipment_id % 4 = 0;

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

On 16, this is what a polling script sees.

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

SELECT phase, heap_blks_total, heap_blks_scanned, max_dead_tuples, num_dead_tuples
FROM pg_stat_progress_vacuum;

DO
       phase       | heap_blks_total | heap_blks_scanned | max_dead_tuples | num_dead_tuples 
-------------------+-----------------+-------------------+-----------------+-----------------
 vacuuming indexes |            1911 |              1911 |          556101 |           75000
(1 row)

Two columns, both counts, and the ratio between them is what a progress display divides. Send that second statement to a 17 server and it does not get as far as the view.

SELECT phase, heap_blks_total, heap_blks_scanned, max_dead_tuples, num_dead_tuples
FROM pg_stat_progress_vacuum;

ERROR:  column "max_dead_tuples" does not exist
LINE 1: SELECT phase, heap_blks_total, heap_blks_scanned, max_dead_t...
                                                          ^

There is no vacuum running for that statement and it errors anyway, which is the useful part: this failure does not wait for a maintenance window to show itself. The view resolves, the column does not, and the error names the first missing column in the list rather than all of them.

The same poll on 17, against the columns that exist:

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

SELECT phase, max_dead_tuple_bytes, dead_tuple_bytes, num_dead_item_ids
FROM pg_stat_progress_vacuum;

DO
       phase       | max_dead_tuple_bytes | dead_tuple_bytes | num_dead_item_ids 
-------------------+----------------------+------------------+-------------------
 vacuuming indexes |             67108864 |          1048576 |             75000
(1 row)

Why swapping the column names is the wrong repair

The tempting fix is a find-and-replace: max_dead_tuples becomes max_dead_tuple_bytes, num_dead_tuples becomes num_dead_item_ids, done. That compiles and it is wrong, because the two columns in the new pair do not measure the same quantity as each other. One is a number of bytes, the other is a number of item identifiers. Dividing the second by the first gives a ratio with no meaning, and a progress bar built on it will sit near zero through an entire vacuum and then jump.

The pair that belongs together on 17 is dead_tuple_bytes over max_dead_tuple_bytes. Both are bytes, their ratio is the fraction of the budget consumed, and reaching one is what forces the index pass. num_dead_item_ids is a separate and useful number: it is how many dead row pointers this pass has collected, which is a measure of how much work the table has generated rather than of how close the vacuum is to its memory ceiling. On 16 those two questions had one answer because the structure made them equivalent. On 17 they do not, and the release that made them different is the release worth checking your query against.

There is a second trap in the direction nobody looks. A dashboard that computes “dead tuples found” from the old column and stores it in a time series will, after a find-and-replace to max_dead_tuple_bytes, carry on filling the same series with numbers that are now eight or nine orders of magnitude larger. The graph does not break. The y-axis quietly rescales and every historical point flattens to zero.

Thresholds to recompute rather than rename

• Memory pressure during vacuum: dead_tuple_bytes as a share of max_dead_tuple_bytes. Approaching the ceiling means another index pass is coming, and on a table with several indexes that is where the time goes. The old rule expressed the same thing in tuples and the new one cannot simply inherit its number.
• Scan position: heap_blks_scanned over heap_blks_total. Both survived the rename unchanged, and on a long vacuum this is still the honest answer to “how far through is it”.
• Dead pointers collected, from num_dead_item_ids, as a graph rather than an alert. It tells you how much churn the table produced between vacuums, which is an argument about autovacuum settings rather than an incident.

If you keep only one, keep the first. It is the one that predicts a vacuum taking several passes over indexes instead of one.

What it costs to poll

The view is assembled from shared memory that the vacuum is updating anyway, so reading it costs a round trip and nothing else. There is no setting to turn it on and no restart. A row exists only while a vacuum is in progress, which means an empty result is the normal state and a monitoring rule that treats “no rows” as a failure will page you constantly.

The number in max_dead_tuple_bytes comes from maintenance_work_mem, or from autovacuum_work_mem when autovacuum is the one running, and it is decided when the vacuum starts. Changing the setting mid-vacuum does not move the ceiling for the vacuum already under way.