PostgreSQL DBA Part 12: VACUUM and Autovacuum Tuning

This is Part 12 of the PostgreSQL DBA series. In Part 11 (MVCC, xmin/xmax and row visibility) you saw that every UPDATE and DELETE leaves an old row version behind. This part is about the process that cleans those old versions up: VACUUM, and its automatic version, autovacuum.

The short answer: VACUUM removes dead row versions and marks their space as free for new rows in the same table. Autovacuum is a set of background processes that runs VACUUM and ANALYZE for you whenever a table has changed enough. On most servers you never run VACUUM by hand; your job as a DBA is to check that autovacuum keeps up, and to tune it when it does not.

By the end of this part you will be able to run and read VACUUM (VERBOSE), work out exactly when autovacuum will visit a table, watch it work in the log and in the progress view, and change its settings for the whole server or for one busy table. Everything is tested on PostgreSQL 18 on Ubuntu.

What you will learn

• What VACUUM does, and why it frees space for reuse but usually does not make the table file smaller
• How to read the output of VACUUM (VERBOSE) line by line
• How the autovacuum launcher and workers decide which table to vacuum, with the exact formula
• How cost-based delay throttles VACUUM, and how to watch a running VACUUM in pg_stat_progress_vacuum
• How to tune autovacuum in postgresql.conf and per table, including what changed in PostgreSQL 18

Prerequisites

• Parts 1 to 11 of this series. Part 11 (MVCC) explains dead tuples, and Part 5 explains reload versus restart.
• One Ubuntu server (db1 in this series) with PostgreSQL 18 from the PGDG repository, cluster 18 main, as installed in Part 2.
• sudo access, so you can edit /etc/postgresql/18/main/postgresql.conf and run psql as the postgres operating system user.
• Two terminal windows (two SSH sessions) on db1 for Step 10.
• A test server. Some steps change server settings; the last step undoes them.

How it works

Think of a table as a notebook where you never erase anything. To change a line you cross it out and write the new version at the bottom of the page or on a new page. After a while the notebook is full of crossed-out lines. VACUUM is the person who goes through the notebook and marks the crossed-out lines as blank space you may write over again. It does not tear pages out of the middle of the notebook.

A crossed-out line is a dead tuple: a row version that no running or future transaction can ever see again. As Part 11 showed, a tuple only becomes removable when every snapshot in the cluster has moved past it.

VACUUM has four jobs, according to the documentation:

1. Recover or reuse the disk space used by updated or deleted rows.
2. Update the statistics the query planner uses (when you run VACUUM ANALYZE, or when autovacuum decides to analyze).
3. Update the visibility map, a small file per table that marks pages where every row is visible to everyone. VACUUM can skip those pages next time, and index-only scans can avoid reading the table.
4. Protect against transaction ID wraparound by freezing old rows. Part 14 covers this in detail.

There are two kinds of VACUUM, and they behave very differently:

The goal of routine maintenance is to run plain VACUUM often enough that you never need VACUUM FULL. Finding and fixing tables that have already grown too big (bloat) is the topic of Part 13.

The autovacuum launcher and workers

Autovacuum is on by default. It is made of one autovacuum launcher process, which wakes up regularly, and up to autovacuum_max_workers autovacuum worker processes (default 3), which do the actual VACUUM and ANALYZE. The launcher tries to start a worker in each database once every autovacuum_naptime (default 1 minute). The worker checks every table in that database and processes the ones that crossed their threshold. Autovacuum needs track_counts to be on (the default), because it reads the counters of inserted, updated and deleted rows.

A table is vacuumed when its number of dead tuples is greater than this threshold (formula from the documentation):

vacuum threshold = Minimum(autovacuum_vacuum_max_threshold,
                           autovacuum_vacuum_threshold
                         + autovacuum_vacuum_scale_factor * reltuples)

Here reltuples is the estimated number of rows in pg_class. With the defaults (50 rows plus 20%, capped at 100 million) a table with 1,000,000 rows is vacuumed after about 200,050 dead tuples. The cap, autovacuum_vacuum_max_threshold, is new in PostgreSQL 18; before that, a table with 1 billion rows waited for 200 million dead tuples.

Two more rules matter. Inserts also trigger a vacuum: when rows inserted since the last vacuum exceed autovacuum_vacuum_insert_threshold (1000) plus autovacuum_vacuum_insert_scale_factor (0.2) times the rows on not-yet-frozen pages. And ANALYZE runs when rows inserted, updated or deleted since the last ANALYZE exceed autovacuum_analyze_threshold (50) plus autovacuum_analyze_scale_factor (0.1) times reltuples.

Cost-based delay: why autovacuum is slow on purpose

VACUUM reads and writes a lot of pages, which can slow down your application. To limit that, PostgreSQL counts a cost for every page: vacuum_cost_page_hit (1) for a page found in shared buffers, vacuum_cost_page_miss (2) for a page read from disk, and vacuum_cost_page_dirty (20) for a page VACUUM modifies. When the total reaches the cost limit, the process sleeps for the cost delay, then continues.

Two facts follow. A manual VACUUM runs at full speed, while autovacuum is throttled. And the autovacuum cost limit is balanced across the running workers, so the total I/O stays the same whether one or five workers are running. Older blog posts quote a 20ms autovacuum delay; that was the default before PostgreSQL 12.

Running VACUUM by hand and reading its output

Step 1: Create a lab database and an orders table

Where: on db1, as the postgres user. Open psql with sudo -u postgres psql, then create a lab database and an orders table with 100,000 rows.

sudo -u postgres psql

CREATE DATABASE vaclab;
\c vaclab
CREATE TABLE orders (
    order_id    integer PRIMARY KEY,
    customer_id integer NOT NULL,
    status      text    NOT NULL,
    amount      numeric(10,2) NOT NULL
) WITH (autovacuum_enabled = false);
INSERT INTO orders
SELECT g, (g % 5000) + 1, 'NEW', (g % 900) + 100
  FROM generate_series(1, 100000) AS g;

CREATE DATABASE
You are now connected to database "vaclab" as user "postgres".
CREATE TABLE
INSERT 0 100000

Now collect statistics and note the size of the table file:

ANALYZE orders;
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       relpages, reltuples
  FROM pg_class WHERE relname = 'orders';

ANALYZE
 table_size | relpages | reltuples
------------+----------+-----------
 5096 kB    |      637 |    100000
(1 row)

The table uses 637 pages of 8 kB each. relpages and reltuples are the estimates autovacuum uses in its formula.

Check it worked: SELECT count(*) FROM orders; returns 100000.

Step 2: Create dead tuples

Where: the same psql session. Ship 30,000 orders and delete 10,000 cancelled ones, then look at the counters in pg_stat_user_tables.

UPDATE orders SET status = 'SHIPPED' WHERE order_id <= 30000;
DELETE FROM orders WHERE order_id BETWEEN 30001 AND 40000;

UPDATE 30000
DELETE 10000

SELECT n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
  FROM pg_stat_user_tables WHERE relname = 'orders';
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

 n_live_tup | n_dead_tup | last_vacuum | last_autovacuum
------------+------------+-------------+-----------------
      90000 |      40000 |             |
(1 row)

 table_size
------------
 6632 kB
(1 row)

There are 90,000 live rows and 40,000 dead tuples: 30,000 old versions from the UPDATE and 10,000 deleted rows. The table grew from 5096 kB to 6632 kB, because the pages were full and the 30,000 new row versions had to go into new pages at the end of the file. These counters are maintained by the statistics system and can lag by a second or so after a change.

Step 3: Run VACUUM (VERBOSE) and read the report

Where: the same psql session.

VACUUM (VERBOSE) orders;

INFO:  vacuuming "vaclab.public.orders"
INFO:  finished vacuuming "vaclab.public.orders": index scans: 1
pages: 0 removed, 829 remain, 829 scanned (100.00% of total), 0 eagerly scanned
tuples: 39987 removed, 90000 remain, 0 are dead but not yet removable
removable cutoff: 765, which was 0 XIDs old when operation ended
new relfrozenxid: 761, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 829 pages set all-visible, 254 pages set all-frozen (0 were all-visible)
index scan needed: 255 pages from table (30.76% of total) had 40000 dead item identifiers removed
index "orders_pkey": pages: 358 in total, 27 newly deleted, 27 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 2.026 MB/s
buffer usage: 2521 hits, 0 reads, 4 dirtied
WAL usage: 1591 records, 4 full page images, 445054 bytes, 0 buffers full
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s
INFO:  vacuuming "vaclab.pg_toast.pg_toast_16397"
...
VACUUM

Here is what the important lines mean:

The second report (pg_toast_16397) is for the TOAST table, a side table PostgreSQL uses to store long values. VACUUM processes it by default.

SELECT n_live_tup, n_dead_tup, last_vacuum, vacuum_count
  FROM pg_stat_user_tables WHERE relname = 'orders';
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

 n_live_tup | n_dead_tup |          last_vacuum          | vacuum_count
------------+------------+-------------------------------+--------------
      90000 |          0 | 2026-11-04 12:48:27.918523+00 |            1
(1 row)

 table_size
------------
 6632 kB
(1 row)

Check it worked: n_dead_tup is 0 and last_vacuum has a timestamp. The size is still 6632 kB: plain VACUUM freed the space inside the table, but did not give it back to the operating system.

Step 4: See that new rows reuse the freed space

Where: the same psql session. Insert 30,000 new orders and check the size again.

INSERT INTO orders
SELECT g, (g % 5000) + 1, 'NEW', (g % 900) + 100
  FROM generate_series(100001, 130000) AS g;
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

INSERT 0 30000
 table_size
------------
 6632 kB
(1 row)

30,000 new rows, and the file did not grow by a single page. PostgreSQL found the free space through the free space map that VACUUM updated, and put the new rows there. This is why a table that is vacuumed regularly reaches a stable size instead of growing forever.

Step 5: Watch VACUUM truncate empty pages at the end

Plain VACUUM does give space back in one case: when pages at the very end of the table become completely empty, and it can briefly get an exclusive lock. The shipped orders from Step 2 live in the pages at the end of the file, so delete them and vacuum again.

Where: the same psql session.

DELETE FROM orders WHERE status = 'SHIPPED';
VACUUM (VERBOSE) orders;
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;

DELETE 30000
INFO:  vacuuming "vaclab.public.orders"
INFO:  table "orders": truncated 829 to 637 pages
INFO:  finished vacuuming "vaclab.public.orders": index scans: 1
pages: 192 removed, 637 remain, 385 scanned (46.44% of total), 0 eagerly scanned
tuples: 30000 removed, 78351 remain, 0 are dead but not yet removable
...
VACUUM
 table_size
------------
 5096 kB
(1 row)

The line truncated 829 to 637 pages shows VACUUM cutting the 192 empty pages off the end, and the file is back to 5096 kB. Notice 385 scanned (46.44% of total): VACUUM skipped the pages the visibility map already marked all-visible in Step 3. Because of that skipping, the 78351 remain figure is an estimate; SELECT count(*) FROM orders; returns the real 90000.

Step 6: Vacuum from the shell with vacuumdb

vacuumdb is a command-line wrapper around VACUUM that ships with PostgreSQL. It is handy in cron jobs and scripts. Where: on db1, in a normal shell (quit psql with \q or open another terminal), running the command as the postgres user.

sudo -u postgres vacuumdb --analyze --verbose --table=orders vaclab
sudo -u postgres vacuumdb --all --analyze --jobs=2

vacuumdb: vacuuming database "vaclab"
INFO:  vacuuming "vaclab.public.orders"
INFO:  finished vacuuming "vaclab.public.orders": index scans: 0
pages: 0 removed, 637 remain, 1 scanned (0.16% of total), 0 eagerly scanned
...
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "vaclab"

This time VACUUM scanned only 1 of 637 pages, because nothing had changed since the last run: the visibility map lets it skip the rest. That is why vacuuming a quiet table is cheap. vacuumdb skips template0, which does not accept connections.

Check it worked: in psql, SELECT last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname = 'orders'; shows fresh timestamps in both columns.

Watching autovacuum at work

Step 7: Check the autovacuum processes and settings

Where: on db1. First the shell, to see the launcher process; then psql, connected to vaclab.

ps -u postgres -o pid,cmd | grep autovacuum

 2347 postgres: 18/main: autovacuum launcher

SELECT pid, backend_type, state
  FROM pg_stat_activity
 WHERE backend_type LIKE 'autovacuum%';

SELECT name, setting, unit, context
  FROM pg_settings
 WHERE name LIKE 'autovacuum%' OR name = 'log_autovacuum_min_duration'
 ORDER BY name;

 pid  |    backend_type     | state
------+---------------------+-------
 2347 | autovacuum launcher |
(1 row)

                 name                  |  setting  | unit |  context
---------------------------------------+-----------+------+------------
 autovacuum                            | on        |      | sighup
 autovacuum_analyze_scale_factor       | 0.1       |      | sighup
 autovacuum_analyze_threshold          | 50        |      | sighup
 autovacuum_freeze_max_age             | 200000000 |      | postmaster
 autovacuum_max_workers                | 3         |      | sighup
 autovacuum_multixact_freeze_max_age   | 400000000 |      | postmaster
 autovacuum_naptime                    | 60        | s    | sighup
 autovacuum_vacuum_cost_delay          | 2         | ms   | sighup
 autovacuum_vacuum_cost_limit          | -1        |      | sighup
 autovacuum_vacuum_insert_scale_factor | 0.2       |      | sighup
 autovacuum_vacuum_insert_threshold    | 1000      |      | sighup
 autovacuum_vacuum_max_threshold       | 100000000 |      | sighup
 autovacuum_vacuum_scale_factor        | 0.2       |      | sighup
 autovacuum_vacuum_threshold           | 50        |      | sighup
 autovacuum_work_mem                   | -1        | kB   | sighup
 autovacuum_worker_slots               | 16        |      | postmaster
 log_autovacuum_min_duration           | 600000    | ms   | sighup
(17 rows)

Only the launcher is running; workers appear when there is work to do. In the context column, sighup means a reload is enough and postmaster means a restart is needed (Part 5). Three settings need a restart: the two freeze ages (Part 14) and autovacuum_worker_slots, new in PostgreSQL 18.

autovacuum_worker_slots reserves process slots for autovacuum workers at server start. On Ubuntu, initdb writes autovacuum_worker_slots = 16 into postgresql.conf. Because of these slots, autovacuum_max_workers can now be changed with a reload, up to the number of slots. Before PostgreSQL 18, changing autovacuum_max_workers needed a restart. autovacuum_work_mem = -1 means each worker uses maintenance_work_mem (64MB by default) to remember dead tuples.

Step 8: Log every autovacuum run

By default autovacuum only logs runs that take 10 minutes or more, so most of its work is invisible. On a lab server, log every run. Where: on db1, as a sudo user, edit the configuration file.

sudo nano /etc/postgresql/18/main/postgresql.conf

# before
#log_autovacuum_min_duration = 10min	# log autovacuum activity;

# after
log_autovacuum_min_duration = 0	# log autovacuum activity;

The setting is reloadable (sighup), so reload instead of restarting. Either run sudo systemctl reload postgresql@18-main in the shell, or this in psql:

SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

SELECT name, setting, unit, source, sourceline
  FROM pg_settings WHERE name = 'log_autovacuum_min_duration';

            name             | setting | unit |       source       | sourceline
-----------------------------+---------+------+--------------------+------------
 log_autovacuum_min_duration | 0       | ms   | configuration file |        592
(1 row)

The value is 0 and comes from line 592 of the configuration file. A value of 0 logs all autovacuum actions, -1 turns logging off, and any other value logs only runs that take at least that long. On a busy production server, 0 can fill the log; choose a threshold that shows the slow runs you care about. Any value other than -1 also logs runs that were skipped because of a conflicting lock.

Undo: put the # back in front of the line (or set it to 10min) and reload again. Step 12 does this.

Step 9: Calculate when autovacuum will visit the table, then trigger it

Where: psql, connected to vaclab as postgres. Turn autovacuum back on for the table by removing the storage parameter, then calculate its threshold with the formula from "How it works".

ALTER TABLE orders RESET (autovacuum_enabled);

SELECT s.relname,
       c.reltuples::bigint AS reltuples,
       s.n_dead_tup,
       least(current_setting('autovacuum_vacuum_max_threshold')::float8,
             current_setting('autovacuum_vacuum_threshold')::float8
           + current_setting('autovacuum_vacuum_scale_factor')::float8 * c.reltuples
            )::bigint AS vacuum_trigger
  FROM pg_stat_user_tables s
  JOIN pg_class c ON c.oid = s.relid
 WHERE s.relname = 'orders';

ALTER TABLE
 relname | reltuples | n_dead_tup | vacuum_trigger
---------+-----------+------------+----------------
 orders  |     90000 |          0 |          18050
(1 row)

50 + 0.2 × 90,000 = 18,050. Autovacuum will vacuum the table once it has more than 18,050 dead tuples. Now update 20,000 rows to cross that line:

UPDATE orders SET amount = amount + 1 WHERE order_id BETWEEN 40001 AND 60000;

UPDATE 20000

Wait about a minute (one autovacuum_naptime), then check:

SELECT n_dead_tup, last_autovacuum, autovacuum_count,
       last_autoanalyze, autoanalyze_count, total_autovacuum_time
  FROM pg_stat_user_tables WHERE relname = 'orders';

 n_dead_tup |        last_autovacuum        | autovacuum_count |       last_autoanalyze        | autoanalyze_count | total_autovacuum_time
------------+-------------------------------+------------------+-------------------------------+-------------------+-----------------------
          0 | 2026-11-04 12:51:41.310785+00 |                1 | 2026-11-04 12:51:41.370391+00 |                 1 |                    26
(1 row)

Autovacuum vacuumed the table, and also analyzed it, because 20,000 changed rows is more than the analyze threshold (50 + 0.1 × 90,000 = 9,050). total_autovacuum_time is the total time in milliseconds autovacuum has spent on this table; it and total_vacuum_time, total_analyze_time and total_autoanalyze_time are new columns in PostgreSQL 18.

Now look at the server log. On Ubuntu it is /var/log/postgresql/postgresql-18-main.log.

sudo grep -A13 'vaclab.public.orders' /var/log/postgresql/postgresql-18-main.log

2026-11-04 12:51:41.310 UTC [2717] LOG:  automatic vacuum of table "vaclab.public.orders": index scans: 1
	pages: 0 removed, 701 remain, 257 scanned (36.66% of total), 0 eagerly scanned
	tuples: 20000 removed, 83024 remain, 0 are dead but not yet removable
	removable cutoff: 897, which was 0 XIDs old when operation ended
	frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
	visibility map: 257 pages set all-visible, 127 pages set all-frozen (0 were all-visible)
	index scan needed: 128 pages from table (18.26% of total) had 19965 dead item identifiers removed
	index "orders_pkey": pages: 442 in total, 0 newly deleted, 136 currently deleted, 136 reusable
	avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
	buffer usage: 1278 hits, 0 reads, 0 dirtied
	WAL usage: 625 records, 0 full page images, 208573 bytes, 0 buffers full
	system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
2026-11-04 12:51:41.370 UTC [2717] LOG:  automatic analyze of table "vaclab.public.orders"
	avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s

The log entry has the same format as VACUUM (VERBOSE). This is the best place to see what autovacuum did overnight: which tables, how long, and whether anything was "dead but not yet removable". The process ID in square brackets (2717) is the worker that did the job.

Step 10: Watch a throttled VACUUM in pg_stat_progress_vacuum

To see cost-based delay in action, slow a manual VACUUM down on purpose and watch it from a second session. First stop autovacuum from racing us on this table again, and create dead tuples in every row.

Where: Terminal 1, psql connected to vaclab as postgres.

ALTER TABLE orders SET (autovacuum_enabled = false);
UPDATE orders SET amount = amount + 1;
SET vacuum_cost_delay = '50ms';
SET vacuum_cost_limit = 20;
SET track_cost_delay_timing = on;
\timing on
VACUUM orders;

While Terminal 1 waits, run this in Terminal 2 (sudo -u postgres psql vaclab):

SELECT pid, relid::regclass AS table_name, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, round(delay_time) AS delay_ms
  FROM pg_stat_progress_vacuum;

SELECT pid, wait_event_type, wait_event, state, query
  FROM pg_stat_activity WHERE query LIKE 'VACUUM%';

 pid  | table_name |     phase      | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | delay_ms
------+------------+----------------+-----------------+-------------------+--------------------+--------------------+----------
 2773 | orders     | vacuuming heap |            1147 |              1147 |               1014 |                  1 |     7911
(1 row)

 pid  | wait_event_type | wait_event  | state  |     query
------+-----------------+-------------+--------+----------------
 2773 | Timeout         | VacuumDelay | active | VACUUM orders;
(1 row)

The progress view shows one row for every backend or autovacuum worker that is running VACUUM. Process 2773 has scanned all 1147 heap blocks, cleaned the index once, and is in the vacuuming heap phase, removing the dead tuples page by page. It has slept for 7.9 seconds so far, and pg_stat_activity shows it waiting on VacuumDelay: it is not stuck, it is being throttled.

ALTER TABLE
UPDATE 90000
SET
SET
SET
Timing is on.
VACUUM
Time: 8784.426 ms (00:08.784)

Now compare with the defaults. In Terminal 1, quit psql with \q and start a new session with sudo -u postgres psql vaclab, so the SET values are gone. Then create the same amount of work and vacuum again:

UPDATE orders SET amount = amount + 1;
SHOW vacuum_cost_delay;
\timing on
VACUUM orders;

UPDATE 90000
 vacuum_cost_delay
-------------------
 0
(1 row)

Timing is on.
VACUUM
Time: 30.056 ms

The same work took 30 milliseconds without throttling and almost 9 seconds with it. On a real server the numbers are much bigger, but the lesson is the same: when autovacuum falls behind on a large table, the cause is usually its cost limit, not a lack of workers.

Check it worked: in Terminal 2, SELECT count(*) FROM pg_stat_progress_vacuum; returns 0 once VACUUM has finished.

Tuning autovacuum

Tuning works on two levels. Server-wide settings in postgresql.conf apply to every table. Table storage parameters override them for one table, which is the right tool for a few very large or very busy tables. Change one thing at a time, and use the log from Step 8 to see the effect.

Step 11: Give autovacuum more workers and a bigger cost budget

Where: on db1, as a sudo user. Edit /etc/postgresql/18/main/postgresql.conf and change two lines.

# before
#autovacuum_max_workers = 3		# max number of autovacuum subprocesses
#autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for

# after
autovacuum_max_workers = 5		# max number of autovacuum subprocesses
autovacuum_vacuum_cost_limit = 1000	# default vacuum cost limit for

These values are an example, not a universal recommendation. A higher cost limit means more I/O from autovacuum, so raise it in steps and watch disk latency. Both settings are reloadable in PostgreSQL 18:

SELECT pg_reload_conf();

SELECT name, setting, context, pending_restart
  FROM pg_settings
 WHERE name IN ('autovacuum_max_workers', 'autovacuum_vacuum_cost_limit',
                'autovacuum_worker_slots')
 ORDER BY name;

             name             | setting |  context   | pending_restart
------------------------------+---------+------------+-----------------
 autovacuum_max_workers       | 5       | sighup     | f
 autovacuum_vacuum_cost_limit | 1000    | sighup     | f
 autovacuum_worker_slots      | 16      | postmaster | f
(3 rows)

Both new values are active and pending_restart is false, so no restart is needed. The server log confirms it:

sudo tail -3 /var/log/postgresql/postgresql-18-main.log

2026-11-04 12:53:06.276 UTC [2339] LOG:  received SIGHUP, reloading configuration files
2026-11-04 12:53:06.276 UTC [2339] LOG:  parameter "autovacuum_max_workers" changed to "5"
2026-11-04 12:53:06.276 UTC [2339] LOG:  parameter "autovacuum_vacuum_cost_limit" changed to "1000"

Undo: put the # back in front of both lines and reload. On PostgreSQL 17 and older, autovacuum_max_workers could only be changed with a full restart, not a reload.

Step 12: Tune one busy table with storage parameters

The default scale factor of 20% is fine for small tables but far too lazy for big ones: a table with 50 million rows would collect 10 million dead tuples before autovacuum starts. Busy tables such as order tables, session tables and job queue tables usually need a lower scale factor of their own.

Where: psql, connected to vaclab as postgres. Turn autovacuum back on for the table and give it its own threshold.

ALTER TABLE orders RESET (autovacuum_enabled);
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01,
                        autovacuum_vacuum_threshold = 1000);
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

ALTER TABLE
ALTER TABLE
 relname |                               reloptions
---------+------------------------------------------------------------------------
 orders  | {autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=1000}
(1 row)

The per-table values are stored in pg_class.reloptions. The threshold query from Step 9 ignores them, so here is a version that reads them. It works for every table in the current database, which makes it a useful monitoring query (it does not apply a per-table autovacuum_vacuum_max_threshold).

SELECT s.relname,
       c.reltuples::bigint AS reltuples,
       s.n_dead_tup,
       least(current_setting('autovacuum_vacuum_max_threshold')::float8,
             coalesce(o.threshold, current_setting('autovacuum_vacuum_threshold')::float8)
           + coalesce(o.scale_factor, current_setting('autovacuum_vacuum_scale_factor')::float8)
             * c.reltuples)::bigint AS vacuum_trigger,
       s.last_autovacuum
  FROM pg_stat_user_tables s
  JOIN pg_class c ON c.oid = s.relid
  LEFT JOIN LATERAL (
       SELECT max(option_value) FILTER (WHERE option_name = 'autovacuum_vacuum_threshold')::float8    AS threshold,
              max(option_value) FILTER (WHERE option_name = 'autovacuum_vacuum_scale_factor')::float8 AS scale_factor
         FROM pg_options_to_table(c.reloptions)) o ON true
 ORDER BY s.n_dead_tup DESC;

 relname | reltuples | n_dead_tup | vacuum_trigger |        last_autovacuum
---------+-----------+------------+----------------+-------------------------------
 orders  |     90000 |          0 |           1900 | 2026-11-04 12:51:41.310785+00
(1 row)

The trigger dropped from 18,050 to 1000 + 0.01 × 90,000 = 1,900. Test it with 5,000 updated rows, which is far below the server-wide trigger:

UPDATE orders SET status = 'PAID' WHERE order_id BETWEEN 60001 AND 65000;

UPDATE 5000

SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count
  FROM pg_stat_user_tables WHERE relname = 'orders';

 relname | n_dead_tup |        last_autovacuum        | autovacuum_count
---------+------------+-------------------------------+------------------
 orders  |          0 | 2026-11-04 12:53:54.338099+00 |                2
(1 row)

Check it worked: autovacuum_count went from 1 to 2 with only 5,000 dead tuples, so the per-table setting is in effect.

Other storage parameters work the same way: autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor for insert-only tables, autovacuum_analyze_scale_factor for statistics, autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay for the speed of vacuum on that table, and log_autovacuum_min_duration for logging. Workers on tables with their own cost settings are left out of the cost balancing between workers.

Undo: ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold); removes the per-table values, and reloptions is empty again.

Step 13: Clean up the lab

Where: on db1. First undo the postgresql.conf changes from Steps 8 and 11 by putting a # back in front of the three lines, so they read as below, and reload.

#log_autovacuum_min_duration = 10min	# log autovacuum activity;
#autovacuum_max_workers = 3		# max number of autovacuum subprocesses
#autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for

SELECT pg_reload_conf();
\c postgres
DROP DATABASE vaclab;

 pg_reload_conf 
----------------
 t
(1 row)

You are now connected to database "postgres" as user "postgres".
DROP DATABASE

SELECT name, setting, source FROM pg_settings
 WHERE name IN ('log_autovacuum_min_duration', 'autovacuum_max_workers',
                'autovacuum_vacuum_cost_limit')
 ORDER BY name;

             name             | setting | source
------------------------------+---------+---------
 autovacuum_max_workers       | 3       | default
 autovacuum_vacuum_cost_limit | -1      | default
 log_autovacuum_min_duration  | 600000  | default
(3 rows)

Check it worked: all three settings show default as their source, and \l no longer lists vaclab.

Common mistakes and how to fix them

ERROR: VACUUM cannot run inside a transaction block

VACUUM manages its own transactions, so it cannot run between BEGIN and COMMIT. You also get this error from tools that wrap every statement in a transaction, such as some GUI clients and migration frameworks.

BEGIN
ERROR:  VACUUM cannot run inside a transaction block
ROLLBACK

Fix: run VACUUM on its own in autocommit mode (the psql default), or use vacuumdb from the shell. In a GUI client, switch the connection to autocommit first.

Turning autovacuum off because it "uses too much I/O"

With autovacuum = off or autovacuum_enabled = false on a table, dead tuples pile up, tables grow, statistics go stale and queries slow down. Autovacuum still runs to prevent transaction ID wraparound anyway, often at the worst time and on a huge backlog.

Fix: leave autovacuum on. If its I/O hurts, tune the cost settings instead, and schedule a manual VACUUM in a quiet window for the biggest tables if needed.

Expecting VACUUM to shrink the table

Step 3 showed the file staying at 6632 kB after VACUUM. That is normal: plain VACUUM frees space for reuse and only truncates empty pages at the end. Fix: if the table really needs to shrink, read Part 13 on bloat before you reach for VACUUM FULL.

ERROR: canceling autovacuum task

Autovacuum holds a SHARE UPDATE EXCLUSIVE lock. When another session asks for a conflicting lock (ALTER TABLE, CREATE INDEX without CONCURRENTLY, LOCK TABLE, TRUNCATE), the autovacuum worker cancels itself so it does not block the application. In our lab, an ALTER TABLE orders ADD COLUMN note text; during a slow autovacuum produced this in the log:

2026-11-04 12:54:57.146 UTC [2991] ERROR:  canceling autovacuum task
2026-11-04 12:54:57.146 UTC [2991] CONTEXT:  while scanning block 20 of relation "public.orders"
	automatic vacuum of table "vaclab.public.orders"

Once in a while this is harmless. If a job runs such commands every few minutes, autovacuum may never finish on that table. Fix: grep the log for this message, find the job that takes the lock, and change it (or run a manual VACUUM on the table in a quiet window). An autovacuum run to prevent wraparound (its query in pg_stat_activity ends with "(to prevent wraparound)") is not cancelled this way.

Raising autovacuum_max_workers and nothing gets faster

The cost limit is shared, so five workers each get a fifth of the budget. Fix: raise autovacuum_vacuum_cost_limit together with the worker count, as in Step 11. And keep the worker count at or below autovacuum_worker_slots, otherwise the log shows:

WARNING:  "autovacuum_max_workers" (20) should be less than or equal to "autovacuum_worker_slots" (16)
DETAIL:  The server will only start up to "autovacuum_worker_slots" (16) autovacuum workers at a given time.

To get more slots, raise autovacuum_worker_slots and restart with sudo systemctl restart postgresql@18-main.

"dead but not yet removable" keeps growing

VACUUM runs, but the dead tuples stay. Autovacuum cannot fix this by running more often. The cause is something holding back the xmin horizon: a session idle in transaction, an old prepared transaction or a stale replication slot. Fix: find it with the backend_xmin query from Part 11, and end it as shown in Part 10.

Forgetting that partitioned parents and temporary tables are not auto-analyzed

Autovacuum processes each partition like a normal table, but it never runs ANALYZE on the partitioned parent table, and it cannot access temporary tables at all. Fix: run ANALYZE on the parent after large data loads, and VACUUM or ANALYZE temporary tables from the session that created them. In PostgreSQL 18, ANALYZE ONLY parent_table analyzes just the parent without its partitions.

Practice exercise

1. Write one query against pg_settings that lists every vacuum or autovacuum setting that needs a server restart.
2. A payments table has 2,000,000 rows (reltuples). With the default settings, how many dead tuples does it need before autovacuum vacuums it? How many after ALTER TABLE payments SET (autovacuum_vacuum_scale_factor = 0.01);?

Quick reference

# Open psql as the postgres user on db1
sudo -u postgres psql vaclab

# Vacuum one table and print a detailed report (in psql)
VACUUM (VERBOSE) orders;

# Vacuum and analyze one table from the shell
sudo -u postgres vacuumdb --analyze --verbose --table=orders vaclab

# Vacuum and analyze every database, 2 jobs in parallel
sudo -u postgres vacuumdb --all --analyze --jobs=2

# Dead tuples and last (auto)vacuum per table (in psql)
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

# All autovacuum settings and whether they need reload (sighup) or restart (postmaster)
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name;

# Running VACUUMs and their phase
SELECT pid, relid::regclass, phase, heap_blks_total, heap_blks_scanned, round(delay_time) FROM pg_stat_progress_vacuum;

# Edit server-wide settings, then reload (no restart needed for autovacuum_max_workers in PostgreSQL 18)
sudo nano /etc/postgresql/18/main/postgresql.conf
sudo systemctl reload postgresql@18-main

# Read autovacuum entries in the server log (needs log_autovacuum_min_duration other than -1)
sudo grep -A13 'automatic vacuum' /var/log/postgresql/postgresql-18-main.log

# Per-table autovacuum settings: set, check, undo (in psql)
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);
SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold);

Summary and what's next

• Plain VACUUM removes dead tuples and makes their space reusable without blocking reads or writes; it only returns empty pages at the end of the file to the operating system.
• VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock; autovacuum never runs it, and you should not schedule it.
• Autovacuum vacuums a table when dead tuples exceed min(autovacuum_vacuum_max_threshold, threshold + scale_factor × reltuples), and analyzes it on a similar rule.
• Autovacuum is throttled by cost-based delay, and its cost limit is shared between workers: raise the limit, not just the worker count.
• In PostgreSQL 18, autovacuum_max_workers is reloadable up to autovacuum_worker_slots, and pg_stat_user_tables reports total_vacuum_time and total_autovacuum_time.
• log_autovacuum_min_duration, pg_stat_user_tables and pg_stat_progress_vacuum show what autovacuum is doing.
• Big or busy tables get their own scale factor with ALTER TABLE ... SET (...), stored in pg_class.reloptions.

In Part 13 we look at table and index bloat: how to find it and how to fix it.