What autovacuum actually takes care of

Dead tuple removal after UPDATE or DELETE is the best-known job of VACUUM and autovacuum, but it is not the only one. Autovacuum launches VACUUM to clear dead tuples from tables and indexes, to maintain the visibility map so that index-only scans stay efficient, and to freeze old visible tuples, which is what keeps transaction ID wraparound and multixact ID wraparound at bay. Through autoanalyze it also runs ANALYZE to collect optimizer statistics. Since database health and SQL performance depend on all of these, monitoring tells you when autovacuum is falling behind, and lets you tune it before trouble starts.

Dead tuple removal and the vacuum urgency factor

VACUUM's garbage collection kicks in once the number of dead tuples passes a threshold.

1

2

3

4

minimum(

   autovacuum_vacuum_max_threshold,

   autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of rows

)

From that threshold you can derive a "vacuum urgency" factor; autovacuum is expected to trigger once the factor exceeds one.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

SELECT schemaname, relname, total_rows, n_dead_tup,

       /* avoid division by zero */

       coalesce(

          n_dead_tup / nullif(least(

                                 max_threshold,

                                 total_rows * scale_factor + threshold

                              ),

                              0

                       ),

          100.0 /* random high value if the effective threshold is 0 */

       ) AS vacuum_urgency,

       last_autovacuum

FROM (SELECT st.schemaname, st.relname, t.reltuples AS total_rows, st.n_dead_tup,

             coalesce(

                /* first, use the table's "relopt" setting */

                min(split_part(ro.o, '=', 2))

                   FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_scale_factor'),

                /* fall back to the parameter */

                current_setting('autovacuum_vacuum_scale_factor')

             )::float8 AS scale_factor,

             coalesce(

                /* first, use the table's "relopt" setting */

                min(split_part(ro.o, '=', 2))

                   FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_threshold'),

                /* fall back to the parameter */

                current_setting('autovacuum_vacuum_threshold')

             )::float8 AS threshold,

             coalesce(

                /* first, use the table's "relopt" setting */

                min(split_part(ro.o, '=', 2))

                   FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_vacuum_max_threshold'),

                /* fall back to the parameter, if it exists */

                current_setting('autovacuum_vacuum_max_threshold', TRUE),

                /* fall back to Infinity to avoid affecting the minimum */

                'Infinity'

             )::float8 AS max_threshold,

             st.last_autovacuum

      FROM pg_stat_all_tables AS st

         JOIN pg_class AS t

            ON st.relid = t.oid

         LEFT JOIN LATERAL unnest(t.reloptions) AS ro(o)

            ON ro.o ~~ ANY ('{autovacuum_vacuum_scale_factor=%,autovacuum_vacuum_threshold=%,autovacuum_vacuum_max_threshold=%}')

      GROUP BY t.oid, st.schemaname, st.relname, t.reltuples, st.n_dead_tup, st.last_autovacuum

     ) AS subq

ORDER BY vacuum_urgency DESC;

The query's complexity comes from two things: autovacuum_vacuum_max_threshold exists only from PostgreSQL v18 on, and per-table storage options can override the global parameters.

A factor far above 1 points to one of several situations:

  • autovacuum runs but is blocked from cleaning up
  • the workload produces dead tuples faster than autovacuum can remove them
  • autovacuum_max_workers is too small to get around to the table
  • concurrent statements take strong locks that conflict with VACUUM's SHARE UPDATE EXCLUSIVE lock, for example the LOCK statement
  • data corruption aborts autovacuum before it finishes

Bloat and the limits of guessing

Plenty of dead rows may still be cleaned up eventually, but the space they occupied stays behind as bloat. PostgreSQL does not track free space inside a table, and monitor queries that try to guess it are wrong too often to trust. The reliable route is the pgstattuple extension:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SELECT t.table_name,

       s.free_percent,

       s.free_space

FROM (SELECT c.oid::regclass AS table_name,

             CASE WHEN s.size > 163840

                  THEN FALSE

                  ELSE TRUE

             END AS tiny

      FROM pg_class AS c

         CROSS JOIN LATERAL pg_relation_size(c.oid) AS s(size)

      WHERE c.relkind = ANY (_char '{r,t}')

      /* prevent subquery flattening */

      OFFSET 0) AS t

   CROSS JOIN LATERAL pgstattuple(t.table_name) AS s

WHERE NOT t.tiny

ORDER BY free_percent DESC

LIMIT 20;

This returns the 20 tables with the largest percentage of free space, skipping tables smaller than 20 blocks because they would skew the statistics. Be aware that it sequentially scans all your tables, so run it rarely and during a quiet period. If an approximate answer suffices, pgstattuple_approx() samples only part of the table instead of pgstattuple().

Autoanalyze

The vacuum urgency query adapts easily to report tables due for automatic ANALYZE:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

SELECT schemaname, relname, total_rows, n_mod_since_analyze,

       n_mod_since_analyze / (total_rows * scale_factor + threshold) AS analyze_urgency,

       last_autoanalyze

FROM (SELECT st.schemaname, st.relname, t.reltuples AS total_rows, st.n_mod_since_analyze,

             coalesce(

                /* first, use the table's "relopt" setting */

                min(split_part(ro.o, '=', 2))

                   FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_analyze_scale_factor'),

                /* fall back to the parameter */

                current_setting('autovacuum_analyze_scale_factor')

             )::float8 AS scale_factor,

             coalesce(

                /* first, use the table's "relopt" setting */

                min(split_part(ro.o, '=', 2))

                   FILTER (WHERE split_part(ro.o, '=', 1) = 'autovacuum_analyze_threshold'),

                /* fall back to the parameter */

                current_setting('autovacuum_analyze_threshold')

             )::float8 AS threshold,

             st.last_autoanalyze

      FROM pg_stat_all_tables AS st

         JOIN pg_class AS t

            ON st.relid = t.oid AND t.relkind = 'r' AND t.oid <> 2619

         LEFT JOIN LATERAL unnest(t.reloptions) AS ro(o)

            ON ro.o ~~ ANY ('{autovacuum_analyze_scale_factor=%,autovacuum_analyze_threshold=%}')

      GROUP BY t.oid, st.schemaname, st.relname, t.reltuples, st.n_mod_since_analyze, st.last_autoanalyze

     ) AS subq

ORDER BY analyze_urgency DESC;

TOAST tables and pg_statistic itself are excluded here, since they are not analyzed. The formula is simpler because no autovacuum_analyze_max_threshold equivalent exists. Autoanalyze rarely causes problems; realistically, tables only get starved when autovacuum_max_workers is too small.

Visibility map and wraparound monitoring

Index-only scans pay off when most of a table's pages are marked all-visible in the visibility map, which lets PostgreSQL skip the heap fetch needed to check tuple visibility. The pg_visibility extension exposes that map:

1

2

3

4

5

6

7

8

9

10

11

SELECT t.oid::regclass AS table_name,

       /* report empty tables as 1.0 */

       coalesce(

          vm.all_visible::float8 / nullif((rs.size / 8192)::float8, 0),

          1.0

       ) AS all_visible_factor

FROM pg_class AS t

   CROSS JOIN LATERAL pg_visibility_map_summary(t.oid) AS vm

   CROSS JOIN LATERAL pg_relation_size(t.oid) AS rs(size)

WHERE t.relkind = 'r'

ORDER BY all_visible_factor;

Restrict this to the tables that genuinely need efficient index-only scans. When all_visible_factor drops too low, lower autovacuum_vacuum_scale_factor, or autovacuum_vacuum_insert_scale_factor for INSERT-heavy tables, and check whether one of the dead tuple removal blockers above is interfering.

Transaction ID wraparound

The simplest check uses pg_database, where PostgreSQL tracks the oldest transaction ID in any unfrozen row of any table:

1

2

3

SELECT datname, age(datfrozenxid) AS transaction_age

FROM pg_database

ORDER BY transaction_age DESC;

An age well above autovacuum_freeze_max_age means something is stopping anti-wraparound workers from freezing tuples; the same list of causes applies. Two details matter here: anti-wraparound workers do not exit when their lock conflicts with a user statement, and autovacuum starts them even when autovacuum is disabled.

Multixact ID wraparound

A multixact is created when several transactions or subtransactions lock the same row. Its identifiers resemble transaction IDs and come from a counter that also wraps. Most workloads produce multixact IDs far less often than transaction IDs, so wraparound is usually not a concern, but not always — so monitor it too:

1

2

3

SELECT datname, mxid_age(datminmxid) AS multixact_age

FROM pg_database

ORDER BY multixact_age DESC;

Act when the age grows well beyond autovacuum_multixact_freeze_max_age; the causes are the same as for transaction ID wraparound.

These queries are meant to be wired into whatever monitoring system you already run.