Vacuum finally says how many indexes are left

The phase that had a name and no denominator

Vacuum alternates between two kinds of work. It scans the heap, collecting pointers to dead rows, and when it has collected as many as its memory allows it stops and visits every index on the table to remove those pointers. Then it goes back to scanning. The progress view has always reported the heap side well: total blocks, blocks scanned, blocks vacuumed, and a count of how many times the index pass has already happened.

The index pass itself reported nothing. The phase column would read “vacuuming indexes” and stay there, and the only other information available was index_vacuum_count, which tells you how many complete passes have finished and says nothing about the one you are waiting on. A table with a dozen indexes and a table with one index looked the same from outside, and so did a vacuum three quarters of the way through its index work and one that had barely started.

PostgreSQL 17 added the denominator and the numerator: indexes_total, the number of indexes this pass will visit, and indexes_processed, how many of them are done. The addition is recorded under Monitoring in the PostgreSQL 17 release notes. Nothing was renamed and nothing was removed, so this page shows only an after; the queries that worked on 16 keep working on 17 unchanged.

Watching the index phase advance

Reading a progress row requires a vacuum to be running, so the fixture starts one from a second connection and samples the view twice. The table carries three secondary indexes on top of its primary key, which is the point: we want to watch a denominator appear and a numerator climb toward it.

CREATE EXTENSION dblink;
CREATE TABLE sensor_reading (reading_id bigint PRIMARY KEY, station text, celsius numeric, taken_at timestamptz);
INSERT INTO sensor_reading
SELECT g, 'station-' || (g % 120), ((g % 700) / 10.0) - 20, now() - (g || ' seconds')::interval
FROM generate_series(1, 300000) AS g;
CREATE INDEX sensor_reading_station ON sensor_reading (station);
CREATE INDEX sensor_reading_celsius ON sensor_reading (celsius);
CREATE INDEX sensor_reading_taken_at ON sensor_reading (taken_at);
DELETE FROM sensor_reading WHERE reading_id % 3 = 0;

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

A 16 server has no answer to the question at all:

SELECT phase, indexes_total, indexes_processed FROM pg_stat_progress_vacuum;

ERROR:  column "indexes_total" does not exist
LINE 1: SELECT phase, indexes_total, indexes_processed FROM pg_stat_...
                      ^

On 17 the same two columns are there, and sampling twice a few seconds apart shows them moving.

DO $$
BEGIN
  PERFORM dblink_connect('reading_sweep', 'dbname=' || current_database() || ' user=postgres');
  PERFORM dblink_exec('reading_sweep', 'SET vacuum_cost_delay = 20');
  PERFORM dblink_exec('reading_sweep', 'SET vacuum_cost_limit = 40');
  PERFORM dblink_send_query('reading_sweep', 'VACUUM (PARALLEL 0) sensor_reading');
  PERFORM pg_sleep(1.5);
END $$;

SELECT 'first sample' AS taken, phase, index_vacuum_count, indexes_total, indexes_processed
FROM pg_stat_progress_vacuum;

DO $$ BEGIN PERFORM pg_sleep(4); END $$;

SELECT 'second sample' AS taken, phase, index_vacuum_count, indexes_total, indexes_processed
FROM pg_stat_progress_vacuum;

DO
    taken     |       phase       | index_vacuum_count | indexes_total | indexes_processed 
--------------+-------------------+--------------------+---------------+-------------------
 first sample | vacuuming indexes |                  0 |             4 |                 0
(1 row)

DO
     taken     |       phase       | index_vacuum_count | indexes_total | indexes_processed 
---------------+-------------------+--------------------+---------------+-------------------
 second sample | vacuuming indexes |                  0 |             4 |                 2
(1 row)

Two details in that output are worth more than the counts. The denominator is four on a table with three secondary indexes, because the primary key has an index too and vacuum visits it like any other. And index_vacuum_count counts completed passes, so on a vacuum that has to make several trips it goes up while indexes_processed returns to zero. A display that treats the two as one progress bar will run backwards.

What the pair makes visible that the phase never did

The reason this matters is that “vacuuming indexes” covers at least three different situations that an operator would treat differently.

A vacuum working steadily through many indexes is fine and needs nothing but patience. A vacuum stuck on one index, with indexes_processed unmoving while the phase stays the same, is usually a lock conflict or a very large index, and the response is to look at what else is touching that table. A vacuum making repeated passes, visible as index_vacuum_count climbing, is short of memory rather than short of time, and the fix is a larger budget rather than a longer window.

Before 17 those three looked identical from the view, and the usual way to tell them apart was to read the vacuum’s verbose output afterwards, which is not much use while you are deciding whether to wait or intervene.

One thing the columns do not do is account for parallel index vacuuming. When workers are involved, indexes_processed counts indexes finished across all of them, so the pace is not a straight line and a single stalled worker is not visible as a stall.

Worth graphing, and one rule worth writing

This is an addition and it comes with no threshold that means anything on its own. Most of its value is in a graph you look at during a maintenance window rather than an alert that wakes somebody.

The exception is the repeated-pass case. index_vacuum_count above one on a table you vacuum regularly says the memory budget ran out before the heap scan did, and that is a real, actionable and cheap finding: every extra pass reads every index again. Alerting on it once per vacuum, not once per poll, gives you a list of tables whose autovacuum memory is too small for their churn.

What it costs to collect

Nothing gates the view. There is no setting to switch on, no restart, and no extension. The two new columns are written by the vacuum into memory it is already updating, and reading them costs a round trip.

The only real cost is polling frequency, and it is a cost in your own scheduler rather than in the server. A row exists only while a vacuum is running, so a five-minute scrape interval will miss most vacuums entirely and see the long ones once. If the intent is to catch stalls, poll while a vacuum you care about is running rather than continuously.