What each table costs to keep vacuumed

Counting runs was never the same as counting cost

The per-table statistics have recorded how many times each table was vacuumed, and when it last happened, for as long as anyone reading this has been using PostgreSQL. What they never recorded is how long any of it took.

That gap shapes how people reason about maintenance, usually without noticing. The available number is a count, so the tables that get attention are the ones with the highest count, and the assumption underneath is that a vacuum is a vacuum. It is not. A vacuum of a narrow table with one index and a vacuum of a wide table with four are different orders of work, and a table that is vacuumed twice a day can cost the cluster more than one vacuumed hourly.

Version 18 adds four cumulative timers to the per-table statistics: time spent in manual vacuum, in autovacuum, in manual analyze and in autoanalyze, each as milliseconds accumulated since the counters were last cleared. Nothing was renamed or removed, so this is an addition and the old queries keep working. What changes is that the interesting question becomes answerable.

SELECT relname, vacuum_count, total_vacuum_time
FROM pg_stat_user_tables ORDER BY total_vacuum_time DESC;

ERROR:  column "total_vacuum_time" does not exist
LINE 1: SELECT relname, vacuum_count, total_vacuum_time
                                      ^

Two tables, one of them much cheaper than it looks

The fixture is deliberately set up to make the count misleading. One table is wide, with six hundred bytes of text per row and a quarter of a million rows. The other is two integers and an extra index, with nearly a million rows, which is to say almost four times as many.

CREATE TABLE wide_rows (row_id bigint PRIMARY KEY, body text);
CREATE TABLE narrow_rows (row_id bigint PRIMARY KEY, counter integer);
CREATE INDEX narrow_rows_counter ON narrow_rows (counter);
INSERT INTO wide_rows SELECT g, repeat('w', 620) FROM generate_series(1, 250000) AS g;
INSERT INTO narrow_rows SELECT g, g FROM generate_series(1, 900000) AS g;
VACUUM ANALYZE wide_rows, narrow_rows;

CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 250000
INSERT 0 900000
VACUUM

Then a third of each table is updated, and both are vacuumed and analyzed together, so that the two counters are comparable by construction.

UPDATE wide_rows   SET body = upper(body)     WHERE row_id % 3 = 0;
UPDATE narrow_rows SET counter = counter + 1  WHERE row_id % 3 = 0;
VACUUM ANALYZE wide_rows, narrow_rows;
SELECT relname,
       n_live_tup,
       vacuum_count,
       round(total_vacuum_time::numeric)  AS vacuum_ms,
       round(total_analyze_time::numeric) AS analyze_ms
FROM pg_stat_user_tables
ORDER BY total_vacuum_time DESC;

UPDATE 83333
UPDATE 300000
VACUUM
   relname   | n_live_tup | vacuum_count | vacuum_ms | analyze_ms 
-------------+------------+--------------+-----------+------------
 wide_rows   |     250000 |            2 |      6457 |       3280
 narrow_rows |     900000 |            2 |        69 |        325
(2 rows)

Both tables have been vacuumed exactly twice. On 17 that is the whole story and the two rows are indistinguishable. On 18 one of them took six and a half seconds of the cluster’s time and the other took sixty-nine milliseconds, a ratio of about ninety to one, on the table with fewer rows.

The width is doing all of it. Wide rows mean more heap pages for the same row count, more pages to scan, more to dirty, and a great deal more to write out. The narrow table has three times the rows and a second index and is still trivial to maintain, because the whole thing is small.

Dividing by the row count makes the difference legible and is a better ranking than either raw figure.

SELECT relname,
       round((total_vacuum_time / nullif(n_live_tup, 0) * 1000000)::numeric) AS vacuum_ms_per_million_rows,
       pg_size_pretty(pg_relation_size(relid))                               AS heap_size
FROM pg_stat_user_tables
ORDER BY relname;

   relname   | vacuum_ms_per_million_rows | heap_size 
-------------+----------------------------+-----------
 narrow_rows |                         77 | 51 MB
 wide_rows   |                      25828 | 217 MB
(2 rows)

Twenty-five seconds per million rows against seventy-seven milliseconds. If a capacity conversation is about which tables will hurt when they grow, that is the column it should be about, and before 18 there was no way to produce it from the catalog at all.

The automatic side is where the money is

Manual vacuums are the ones an operator schedules and therefore already knows about. The cumulative timers become genuinely interesting on the automatic side, because autovacuum is the thing that runs without anybody watching, and its cost has never been attributable per table without reading the log.

The block below churns the narrow table again and waits for the daemon to deal with it.

UPDATE narrow_rows SET counter = counter - 1 WHERE row_id % 5 = 0;
SELECT pg_sleep(12);
SELECT relname, autovacuum_count, round(total_autovacuum_time::numeric) AS autovacuum_ms,
       autoanalyze_count, round(total_autoanalyze_time::numeric) AS autoanalyze_ms
FROM pg_stat_user_tables
ORDER BY relname;

UPDATE 180000
 pg_sleep 
----------
 
(1 row)

   relname   | autovacuum_count | autovacuum_ms | autoanalyze_count | autoanalyze_ms 
-------------+------------------+---------------+-------------------+----------------
 narrow_rows |                2 |          6597 |                 3 |           1375
 wide_rows   |                0 |             0 |                 0 |              0
(2 rows)

Two automatic vacuums on the narrow table costing six and a half seconds between them, against sixty-nine milliseconds for the two manual ones on the same table earlier. That gap is the point of the measurement. The manual vacuums ran unthrottled in a session; the automatic ones ran under the cost delay, which this cluster leaves at its default and which spends most of a vacuum’s wall-clock time waiting rather than working. The timers record elapsed time, sleep included, and separating the two halves is what the delay measurement is for.

Which is the caveat to carry away from this page: these four columns are elapsed time, not work. A table whose automatic total is large may be expensive, or may simply be throttled, and the two want opposite responses. Reading the totals alone and concluding that a table needs less vacuuming is the mistake this data makes easy.

The wide table has zeros in both automatic columns because nothing made the daemon consider it during this fixture, which is also worth saying: a zero here means the daemon has not visited, not that the visit was free.

They reset like every other per-table counter

SELECT pg_stat_reset_single_table_counters('wide_rows'::regclass);
SELECT relname, vacuum_count, round(total_vacuum_time::numeric) AS vacuum_ms,
       analyze_count, round(total_analyze_time::numeric) AS analyze_ms
FROM pg_stat_user_tables ORDER BY relname;

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

   relname   | vacuum_count | vacuum_ms | analyze_count | analyze_ms 
-------------+--------------+-----------+---------------+------------
 narrow_rows |            2 |        69 |             2 |        325
 wide_rows   |            0 |         0 |             0 |          0
(2 rows)

The timers go with the counts, which is the behaviour you would want and is worth confirming rather than assuming, because it means a per-table reset destroys the history these columns are most useful for. They are cumulative and they have no stamp of their own: the view’s reset time is per table, so a table that somebody reset last Tuesday is comparable with one that has been accumulating for a year only if you check.

How to use them without being misled

• Rank by time per row or time per gigabyte rather than by raw total, and rank manual and automatic separately. The two are different budgets and mixing them hides which one is growing.
• Watch the derivative, not the level. These counters only go up, so the number that means something is how much they went up by over an interval, which is the usual treatment for a cumulative counter and is easy to forget for one that is new.
• Pair a large automatic total with the throttle ratio before drawing a conclusion. Expensive and throttled look identical in this column and are not the same problem.
• Treat the analyze timers as the early warning. Analyze cost scales with the statistics targets and the number of extended statistics objects on a table, both of which get raised during an investigation and almost never lowered afterwards, and this is the first place that shows up as a number.

Where a slow vacuum actually spends its time

The timers say which table is expensive and not why, and the why has a small number of answers that are worth having in mind before reading a ranking.

The heap scan is proportional to the number of pages, which is why the wide table in the measurement above costs so much more than the narrow one despite having fewer rows. This is the term that responds to the row width and to bloat, and it is the term a fillfactor change or a schema change moves.

The index passes are the term that surprises people. Vacuum collects dead row pointers in memory, and when that memory fills it has to stop, visit every index in full, and start again. A table with four indexes and enough dead rows to fill the memory twice pays for eight index scans. The memory available for that collection is a setting, the amount of churn is a property of the workload, and the number of passes is the ratio between them. A table whose cost jumped without its size changing has usually crossed a threshold into a second pass.

The delay is the third term and it is the one the timers cannot distinguish from work, which is the caveat repeated above and the reason the delay measurement belongs beside these columns rather than after them.

Reading a ranking with those three in mind turns it into a decision. Expensive because of pages means a storage or schema conversation. Expensive because of index passes means a memory setting, and it is the cheapest of the three to fix. Expensive because of throttling means the maintenance configuration, and fixing it will make the cluster busier rather than quieter, which is a trade to make deliberately.

The automatic timers add a fourth reading that the manual ones cannot. If a table’s automatic total is growing steadily while its row count is flat, the daemon is visiting it more often rather than working harder each visit, and that points at the trigger thresholds rather than at the cost of the work.

What it costs to collect

The timers are maintained whether or not anyone reads them, there is no setting to enable, and the view they live in is the one most monitoring already scrapes. The practical cost is four more columns on a view that has one row per table, which on a cluster with tens of thousands of tables is a real consideration and was already a real consideration before this release.

There is no additional privilege involved. The columns are visible to anyone who can already read the per-table statistics, and they are populated for every table in the database, including the catalog tables, which are worth excluding from any ranking unless you specifically want to know what the catalog costs.