PostgreSQL in Depth Module 5 – MVCC, VACUUM and freezing
VACUUM and autovacuum: triggers and tuning
How PostgreSQL 18 decides when autovacuum runs VACUUM and ANALYZE on a table, how to read its log entry and progress view, and how to tune it table by table.
What you will learn
- Compute when autovacuum vacuums and analyses a table, including PostgreSQL 18's cap and its insert rule
- Read an autovacuum log entry and the VACUUM progress view
- Tune the cost budget, the number of workers and per-table storage parameters
- Recognise when autovacuum cannot keep up and decide what to change first
Before you start
What you’ll learn · Before you start
What you will learn
- Compute when autovacuum vacuums and analyses a table, including PostgreSQL 18's cap and its insert rule
- Read an autovacuum log entry and the VACUUM progress view
- Tune the cost budget, the number of workers and per-table storage parameters
- Recognise when autovacuum cannot keep up and decide what to change first
Before you start
On this page
A table of 10,000 rows is vacuumed automatically once more than 2,050 of its rows are dead, and a table of a billion rows once more than 100 million are, a cap that PostgreSQL 18 added: the formula alone would wait for 200 million. VACUUM is the command that makes the space of dead tuple: A row version whose deleting or updating transaction has committed, so no new query returns it. It keeps its space on the page until VACUUM removes it, which VACUUM may do only once that transaction is older than the xmin horizon. More in “MVCC in production: the xmin horizon” reusable and keeps a table’s visibility map: A fork of a table (its file ends in _vm) with two bits per page: whether every row on the page is visible to all transactions, and whether every row is frozen. VACUUM and index-only scans use it. More in “PostgreSQL architecture: processes, memory, files”, frozen rows and, with ANALYZE, statistics up to date; autovacuum: The launcher and worker processes that run VACUUM and ANALYZE on tables by themselves whenever a table's counts of dead, inserted or changed rows exceed thresholds worked out from its size and the settings. runs VACUUM and ANALYZE by itself whenever a table’s counts of changed rows exceed thresholds like these.
The first sections compute those thresholds for three tables from the server’s own catalog and settings; the parts after them read, tune and diagnose the work autovacuum does.
VACUUM cleans up after changes, autovacuum decides when
An UPDATE or DELETE leaves the old row version in place as a dead row version, because a snapshot taken earlier
may still need it, as MVCC in production: the xmin horizon shows. A
plain VACUUM makes that space reusable, marks pages all-visible in the visibility map,
freezing: Marking a row as visible to every transaction, so that its transaction ID no longer takes part in comparisons. VACUUM sets a flag in the row's header and keeps its xmin, and a page whose rows are all frozen is marked all-frozen in the visibility map. More in “Freezing and transaction ID wraparound” old rows, the subject of
Freezing and transaction ID wraparound, and with ANALYZE refreshes the
planner’s statistics. It shrinks the file only by cutting empty pages off its end.
Autovacuum does that work unasked. Its launcher starts a worker in each database once per autovacuum_naptime, a
minute by default, so a server with four databases starts one every 15 seconds. At most autovacuum_max_workers, 3,
run at a time. A worker compares each table’s counters from the cumulative statistics with that table’s thresholds and
runs VACUUM, ANALYZE or both where they are exceeded. It never runs VACUUM FULL, cannot reach temporary tables and
needs track_counts, which is on by default. Step through one round:
The drawing shows the whole scene; its 7 steps are listed below.
Every step as text
- The launcher wakes. The autovacuum launcher starts one worker per database in each autovacuum_naptime, a minute by default, so with four databases a new worker starts every 15 seconds. At most autovacuum_max_workers, 3 by default, run at once.
- A worker starts. The worker connects to its database and goes through the tables there. Temporary tables are out of its reach, and a partitioned table holds no rows of its own; its partitions are processed instead.
- The counters are read. For every table it reads three counters of the cumulative statistics, n_dead_tup, n_ins_since_vacuum and n_mod_since_analyze. The run's orders showed 1,500 dead and 1,500 changed rows.
- Compared with the thresholds. Each threshold comes from the table's reltuples and the settings, and a counter must exceed it. For 10,000 orders that meant a vacuum after 2,050 dead rows and an analyze after 1,050 changes, so only ANALYZE was due. One rule overrides the counters: once age(relfrozenxid) passes autovacuum_freeze_max_age, the table is vacuumed anyway.
- Nothing due here. A database where no table is over a threshold costs the worker only that check. It exits, and the database waits for the launcher's next visit.
- The due tables are processed. The worker processes the due tables one after another, with ANALYZE alone for the orders and both commands for the queue and the events.
- Counters start again. VACUUM sets n_dead_tup to the rows it could not remove, usually 0, and n_ins_since_vacuum to 0; ANALYZE starts n_mod_since_analyze again. last_autovacuum and last_autoanalyze record when.
A finished run can leave a log entry, and a running one shows its phase in pg_stat_progress_vacuum.
cost-based vacuum delay: A throttle on VACUUM and ANALYZE: every page found in memory, read from disk or dirtied adds to a cost count, and when the count reaches a limit the process sleeps for a set delay before it goes on. makes a worker pause after each budget of page reads and
writes. A storage parameter: A setting stored with one table or index, given in CREATE TABLE … WITH (…) or ALTER TABLE … SET (…), that overrides the server-wide setting for that object, for example an autovacuum threshold., set with ALTER TABLE … SET, gives one table its own
thresholds and limits.
Checkpoint
Check: What VACUUM and autovacuum do
Three thresholds decide when a table is due
Each threshold is worked out from pg_class.reltuples, the row count that the last VACUUM, ANALYZE or CREATE INDEX
stored, and from the settings. A vacuum is due once the dead rows exceed this one:
That is 50 rows, autovacuum_vacuum_threshold, plus 20 % of the table, autovacuum_vacuum_scale_factor, and at most
autovacuum_vacuum_max_threshold, the cap new in PostgreSQL 18: 2,050 for 10,000 rows. Inserted rows have a rule of
their own, so that a table that only grows is vacuumed too, which sets its pages all-visible and can freeze rows early:
The last factor, new in PostgreSQL 18, is the share of pages not yet all-frozen; version 17 took 20 % of every row. For 10,000 rows with half their pages frozen it gives 1,000 + 1,000 = 2,000 inserts. An analyze is due once inserted, updated and deleted rows together exceed 50 + 10 % of the table, and a table that no VACUUM, ANALYZE or CREATE INDEX has measured yet counts as 0 rows. The run vacuums three tables once, gives them a day of work and computes the thresholds as autovacuum does:
thresholds_session.py
"""Autovacuum thresholds on PostgreSQL 18.6: day.sql creates three tables, vacuums them once and gives them a day of
work; thresholds.sql then computes, for each table, the three thresholds that autovacuum compares with its counters,
from pg_class, pg_stat_user_tables and the server's settings, and the capped vacuum threshold of bigger tables.
day.sql runs with psql -q -A -t, which prints neither its statements nor their command tags (the file is the tab
beside this one), so the record shows the computation and its results."""
from pg_session import Server
# Autovacuum is off for this run, so no worker resets the counters the query reads: every number is the one the
# statements left, and two runs print the same. The thresholds themselves come from the server's default settings.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off", "timezone": "'UTC'"}
with Server(settings=SETTINGS) as pg:
pg.up()
pg.psql("day.sql", flags=("-q", "-A", "-t"))
pg.psql("thresholds.sql")Output
$ psql -X -q -A -t -f day.sql
$ psql -X -a -f thresholds.sql
-- What the first VACUUM stored for each table: the inputs of its thresholds.
SELECT relname, reltuples, relpages, relallfrozen FROM pg_class
WHERE relname IN ('orders', 'events', 'queue') ORDER BY relname;
relname | reltuples | relpages | relallfrozen
---------+-----------+----------+--------------
events | 10000 | 64 | 64
orders | 10000 | 64 | 0
queue | 100 | 1 | 0
(3 rows)
-- The three thresholds as autovacuum works them out (none of these settings is -1 here), beside the counters.
SELECT st.relname AS "table", n_dead_tup AS dead, vacuum_at, n_ins_since_vacuum AS inserted, insert_at,
n_mod_since_analyze AS changed, analyze_at,
concat_ws(', ', CASE WHEN n_dead_tup > vacuum_at OR n_ins_since_vacuum > insert_at THEN 'VACUUM' END,
CASE WHEN n_mod_since_analyze > analyze_at THEN 'ANALYZE' END) AS due
FROM pg_stat_user_tables AS st JOIN pg_class AS c ON c.oid = st.relid,
LATERAL (SELECT greatest(reltuples, 0) AS n,
CASE WHEN relpages > 0 AND relallfrozen > 0
THEN 1 - least(relallfrozen, relpages)::float8 / relpages ELSE 1 END AS unfrozen) AS r,
LATERAL (SELECT round(least(current_setting('autovacuum_vacuum_max_threshold')::float8,
current_setting('autovacuum_vacuum_threshold')::float8
+ current_setting('autovacuum_vacuum_scale_factor')::float8 * n)) AS vacuum_at,
round(current_setting('autovacuum_vacuum_insert_threshold')::float8
+ current_setting('autovacuum_vacuum_insert_scale_factor')::float8 * n * unfrozen) AS insert_at,
round(current_setting('autovacuum_analyze_threshold')::float8
+ current_setting('autovacuum_analyze_scale_factor')::float8 * n) AS analyze_at) AS t
ORDER BY st.relname;
table | dead | vacuum_at | inserted | insert_at | changed | analyze_at | due
--------+------+-----------+----------+-----------+---------+------------+-----------------
events | 0 | 2050 | 2000 | 1000 | 2000 | 1050 | VACUUM, ANALYZE
orders | 1500 | 2050 | 0 | 3000 | 1500 | 1050 | ANALYZE
queue | 500 | 70 | 500 | 1020 | 1000 | 60 | VACUUM, ANALYZE
(3 rows)
-- The vacuum rule for bigger tables: PostgreSQL 18 caps it at autovacuum_vacuum_max_threshold.
SELECT n AS reltuples, round(base + scale * n) AS without_cap, round(least(cap, base + scale * n)) AS vacuum_at
FROM (VALUES (10000), (1000000), (100000000), (1000000000)) AS sizes (n),
LATERAL (SELECT current_setting('autovacuum_vacuum_threshold')::numeric AS base,
current_setting('autovacuum_vacuum_scale_factor')::numeric AS scale,
current_setting('autovacuum_vacuum_max_threshold')::numeric AS cap) AS s;
reltuples | without_cap | vacuum_at
------------+-------------+-----------
10000 | 2050 | 2050
1000000 | 200050 | 200050
100000000 | 20000050 | 20000050
1000000000 | 200000050 | 100000000
(4 rows)
Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 thresholds_session.py
thresholds.sql
-- What the first VACUUM stored for each table: the inputs of its thresholds.
SELECT relname, reltuples, relpages, relallfrozen FROM pg_class
WHERE relname IN ('orders', 'events', 'queue') ORDER BY relname;
-- The three thresholds as autovacuum works them out (none of these settings is -1 here), beside the counters.
SELECT st.relname AS "table", n_dead_tup AS dead, vacuum_at, n_ins_since_vacuum AS inserted, insert_at,
n_mod_since_analyze AS changed, analyze_at,
concat_ws(', ', CASE WHEN n_dead_tup > vacuum_at OR n_ins_since_vacuum > insert_at THEN 'VACUUM' END,
CASE WHEN n_mod_since_analyze > analyze_at THEN 'ANALYZE' END) AS due
FROM pg_stat_user_tables AS st JOIN pg_class AS c ON c.oid = st.relid,
LATERAL (SELECT greatest(reltuples, 0) AS n,
CASE WHEN relpages > 0 AND relallfrozen > 0
THEN 1 - least(relallfrozen, relpages)::float8 / relpages ELSE 1 END AS unfrozen) AS r,
LATERAL (SELECT round(least(current_setting('autovacuum_vacuum_max_threshold')::float8,
current_setting('autovacuum_vacuum_threshold')::float8
+ current_setting('autovacuum_vacuum_scale_factor')::float8 * n)) AS vacuum_at,
round(current_setting('autovacuum_vacuum_insert_threshold')::float8
+ current_setting('autovacuum_vacuum_insert_scale_factor')::float8 * n * unfrozen) AS insert_at,
round(current_setting('autovacuum_analyze_threshold')::float8
+ current_setting('autovacuum_analyze_scale_factor')::float8 * n) AS analyze_at) AS t
ORDER BY st.relname;
-- The vacuum rule for bigger tables: PostgreSQL 18 caps it at autovacuum_vacuum_max_threshold.
SELECT n AS reltuples, round(base + scale * n) AS without_cap, round(least(cap, base + scale * n)) AS vacuum_at
FROM (VALUES (10000), (1000000), (100000000), (1000000000)) AS sizes (n),
LATERAL (SELECT current_setting('autovacuum_vacuum_threshold')::numeric AS base,
current_setting('autovacuum_vacuum_scale_factor')::numeric AS scale,
current_setting('autovacuum_vacuum_max_threshold')::numeric AS cap) AS s;day.sql
-- Three tables with three workloads: orders are updated, events are only appended, the queue is emptied and refilled.
CREATE TABLE orders (id int PRIMARY KEY, status text NOT NULL, note text NOT NULL);
INSERT INTO orders SELECT n, 'placed', 'order ' || n FROM generate_series(1, 10000) AS n;
CREATE TABLE events (id int PRIMARY KEY, kind int NOT NULL, payload text NOT NULL);
INSERT INTO events SELECT n, n % 7, 'event ' || n FROM generate_series(1, 10000) AS n;
CREATE TABLE queue (id int PRIMARY KEY, job text NOT NULL);
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(1, 100) AS n;
-- The session's counts reach the statistics system now; then a first VACUUM resets them and stores reltuples,
-- relpages and relallfrozen in pg_class. The append-only events are frozen as well.
SELECT pg_stat_force_next_flush();
VACUUM (ANALYZE) orders, queue;
VACUUM (FREEZE, ANALYZE) events;
-- A day's work: 1,500 orders shipped, 2,000 events appended, the queue's 100 jobs taken and replaced five times.
UPDATE orders SET status = 'shipped' WHERE id <= 1500;
INSERT INTO events SELECT n, n % 7, 'event ' || n FROM generate_series(10001, 12000) AS n;
DELETE FROM queue;
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(101, 200) AS n;
DELETE FROM queue;
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(201, 300) AS n;
DELETE FROM queue;
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(301, 400) AS n;
DELETE FROM queue;
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(401, 500) AS n;
DELETE FROM queue;
INSERT INTO queue SELECT n, 'job ' || n FROM generate_series(501, 600) AS n;
SELECT pg_stat_force_next_flush();pg_session.py
"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.
Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.
The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. A session may name more patterns (mask=) for numbers that change on
every run, such as the rates and times ANALYZE VERBOSE prints or the random key of pg_dump's \\restrict line; the
session's own file says which, and its record shows a fixed word in their place. The sessions never print the port.
A session stops with status 1 when a command fails that should have worked, or reports a different number of errors
than the session expects, so a run that went wrong is never recorded as if it had worked.
"""
import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path
PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name
def free_port():
"""A TCP port on 127.0.0.1 that nothing listens on now."""
with socket.socket() as probe:
probe.bind(("127.0.0.1", 0))
return probe.getsockname()[1]
class Server:
"""One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""
def __init__(self, settings=None, initdb=(), name="data"):
self.settings = dict(settings or {})
self.initdb_args = list(initdb)
self.name = name
self.running = []
def __enter__(self):
if not (BIN / "postgres").exists():
sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
self.port = free_port()
self.password = secrets.token_urlsafe(24)
(self.base / "pwfile").write_text(self.password + "\n")
self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
"PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
self.env.pop(key, None)
for script in Path(".").glob("*.sql"):
shutil.copy(script, self.base / script.name)
for data in Path(".").glob("*.csv"):
shutil.copy(data, self.base / data.name)
return self
def __exit__(self, *exc):
for folder, _port in reversed(self.running):
self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
self.running = []
shutil.rmtree(self.base, ignore_errors=True)
# ---- the cluster ---------------------------------------------------------------------------------------------
def initdb(self, show=False):
args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
*self.initdb_args]
if not show:
args.append("--no-instructions")
self._expect(args, show=show)
with open(self.base / self.name / "postgresql.conf", "a") as conf:
conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
for key, value in self.settings.items():
conf.write(f"{key} = {value}\n")
def start(self, show=False, folder=None, port=None):
folder = folder or self.name
self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
self.running.append((folder, port or self.port))
def up(self):
"""initdb and start, quietly."""
self.initdb()
self.start()
return self
def standby(self, folder="standby"):
"""A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
port = free_port()
with open(self.base / folder / "postgresql.conf", "a") as conf:
conf.write(f"port = {port}\n")
self.start(folder=folder, port=port)
for _ in range(300):
if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
return port
time.sleep(0.1)
sys.exit("the standby did not start streaming")
# ---- running things ------------------------------------------------------------------------------------------
def _call(self, args, env=None):
args = [str(a) for a in args]
done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
text=True, cwd=self.base, env=env or self.env)
return done.returncode, self.clean(done.stdout)
def _expect(self, args, show=False, status=0, mask=()):
code, out = self._call(args)
out = _masked(out, mask)
if show:
_show(args, out)
if code != status:
if not show:
print(out)
sys.exit(f"{args[0]} ended with status {code}, not {status}")
return out
def clean(self, text):
"""The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
return text.replace(f'"{OS_USER}"', '"learner"')
def wait_for(self, sql, value):
"""Waits (unprinted) until a query returns the value, for example after a reload."""
for _ in range(300):
if self.query(sql) == value:
return
time.sleep(0.1)
sys.exit(f"{sql} never returned {value}")
def show_file(self, path):
"""Prints a text file of the cluster, as `cat` would."""
print(f"$ cat {path}")
print((self.base / path).read_text().rstrip("\n"))
print()
def query(self, sql, port=None):
"""One value from one query, unprinted (the session uses it to wait or to decide)."""
env = {**self.env, "PGPORT": str(port or self.port)}
code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
if code != 0:
sys.exit(f"the query failed: {out}")
return out.strip()
def silent(self, sql):
"""Runs a statement without printing it (used only to give a role the run's password)."""
self.query(sql)
def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None, mask=()):
"""Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
`errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
env = {**self.env, "PGPORT": str(port or self.port)}
if user:
env["PGUSER"] = user
if db:
env["PGDATABASE"] = db
args = ["psql", "-X", *flags, "-f", script]
code, out = self._call(args, env=env)
out = _masked(out, mask)
shown = ["psql", "-X", *flags, "-f", script]
if user:
shown[1:1] = ["-U", user]
if db:
shown[1:1] = ["-d", db]
if note:
print(f"-- {note}")
_show(shown, out)
found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
expected_status = 0 if status is None else status
if found != errors or code != expected_status:
sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")
def sessions(self, count):
"""Opens `count` idle psql connections (each one a client backend) until the with block ends."""
return _Idle(self, count)
def shell(self, *args, status=0, mask=()):
"""Runs one of PostgreSQL's programs and prints the command and its output."""
return self._expect(list(args), show=True, status=status, mask=mask)
def listing(self, folder, prefix=""):
"""Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
path = self.base / folder
names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
print(textwrap.fill(" ".join(names), width=78, initial_indent=" ", subsequent_indent=" ",
break_on_hyphens=False))
print()
return names
def _masked(text, mask):
"""Replaces what differs on every run (a rate, a time, a random key) with fixed text (see the docstring)."""
for pattern, replacement in mask:
text = re.sub(pattern, replacement, text, flags=re.M)
return text
def _arg(a):
"""A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
a = str(a)
if re.fullmatch(r"[\w@%+=:,./-]+", a):
return a
if not re.search(r"[\"$`\\!]", a):
return f'"{a}"'
return shlex.quote(a)
def _show(args, out):
print("$ " + " ".join(_arg(a) for a in args))
if out.strip():
print(out.rstrip("\n"))
print()
class _Idle:
"""Idle psql connections, closed again when the with block ends."""
def __init__(self, server, count):
self.server, self.count, self.procs = server, count, []
def __enter__(self):
before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
for _ in range(self.count):
self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
env=self.server.env, cwd=self.server.base))
sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
for _ in range(300):
# the query's own connection is one more client backend
if int(self.server.query(sql)) == before + self.count:
return self
time.sleep(0.1)
sys.exit("the idle connections did not open")
def __exit__(self, *exc):
for p in self.procs:
p.stdin.close()
p.wait(timeout=30)The orders, 1,500 dead rows against 2,050, were due only for ANALYZE, 1,500 changes against 1,050; the queue, 500
dead rows against 70, for both. The events had no dead rows and still needed VACUUM: VACUUM (FREEZE) had left every
page all-frozen, so 2,000 inserts beat a threshold of 1,000 where PostgreSQL 17 would have waited for 3,000. The last
query shows the cap turning 200,000,050 into 100,000,000.
Key idea
A table is vacuumed when its dead rows exceed 50 + 20 % of its rows, at most 100 million, or its inserts exceed 1,000 + 20 % of its unfrozen rows; it is analysed when its changes exceed 50 + 10 %.
Checkpoint
Check: The vacuum, insert and analyze thresholds
Practise: the next trigger, worked out
Each question gives a table’s numbers. Work out the threshold before you compare it with the counter, as the query of the run does.
Practice that adapts to you
Practise this lesson
8 questions about 9 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.
Reading what autovacuum did: its log entry and the progress view Pro: opens with a Pro, Premium or Ultimate pass
Autovacuum works out of sight, so its log entry and its progress view are how you see what it did. This part records one automatic vacuum on PostgreSQL 18.6 and reads its entry line by line, then watches a throttled worker from another session until a command from that session cancels it.
In this part:
- One automatic vacuum, its log entry line by line
- A throttled worker in the progress view, then cancelled
1 diagram · 2 runnable examples (after.sql, autovacuum.sql, autovacuum_session.py, cancel.sql, finished.sql, throttle.sql, throttle_session.py, watch.sql) · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Tuning the cost budget, the workers and single tables, with numbers Pro: opens with a Pro, Premium or Ultimate pass
Autovacuum’s speed is set by a cost budget, and its reach by the number of workers and each table’s thresholds. This part computes the budget in pages a second from the server’s settings, shows what more workers do to it, and gives a large table and a small queue storage parameters of their own.
In this part:
- The cost budget in pages a second, with numbers
- More workers or a higher cost limit, decided with numbers
- Storage parameters for the tables that need them
2 runnable examples (av_setting.sql, block.sql, budget.sql, budget_session.py, load.sql, pertable.sql, pertable_session.py) · about 10 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
When autovacuum falls behind: the signs and the first change Pro: opens with a Pro, Premium or Ultimate pass
Autovacuum that cannot keep up shows itself in a few views and log lines long before it shows as bloat. This part lists those signs with the place each one shows, then puts the changes in the order in which to try them.
In this part:
- Five signs that autovacuum is falling behind, and where each shows
- What to change first, and why in that order
1 diagram · about 7 minutes
Opening this part with your pass…
This part opens with any MySmartCoPilot pass.
- Pro pass · 1 week₹79$2.99
- 30 days₹179$5.99
- 1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so this part can’t be opened at the moment. The free part above stands on its own. If you have a pass, enter your licence key.
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
Interview questions
Warm-up, fresher to 2–5 years: when does autovacuum process a table, and why can it analyse one without vacuuming it?
A worker compares three counters per table with thresholds from pg_class.reltuples and the settings. It vacuums
when dead rows exceed 50 + 0.2 × reltuples, at most 100 million in PostgreSQL 18, or when inserts
since the last vacuum exceed 1,000 + 0.2 × reltuples × the share of pages not all-frozen. It analyses when inserted,
updated and deleted rows since the last ANALYZE exceed 50 + 0.1 × reltuples. That threshold is the lower one, so a
table can be due for ANALYZE alone: 1,500 updates on 10,000 rows crossed 1,050 but not 2,050. A worker visits each
database once per autovacuum_naptime, a minute by default, so a table may wait about that long after crossing.
Three harder cases, with their first steps, are in the module’s bank:
-
Mid to senior
An orders table on PostgreSQL 18 has 400 million rows, about 120 GiB, and the server's default autovacuum settings. Its queries get slower for days at a time, and every automatic vacuum of it runs for hours. Explain when autovacuum starts on it, why the runs take so long, and what you would change.
-
Senior to staff
On a PostgreSQL 18 server with about 40 busy tables, dead rows grow from week to week on many of them, and pg_stat_activity shows three autovacuum workers at almost any moment. A colleague wants to raise autovacuum_max_workers to 10. What do you check before you agree, and what would you change?
-
Mid to senior
A sessions table is far over its vacuum threshold, yet its last_autovacuum has not changed for two days. The server log shows 'canceling autovacuum task' for it several times an hour. What is cancelling the vacuums, how do you find the job behind it, and how do you get the table vacuumed?
Flashcards · 4 cards
Recall it, then check
Say the answer to yourself before you look: recalling it is what makes it stick.
-
What does VACUUM do, and what does autovacuum decide?
VACUUM makes dead-version space reusable, marks pages all-visible, freezes old rows and (with ANALYZE) refreshes statistics; it only cuts empty trailing pages. Autovacuum runs VACUUM/ANALYZE itself when a table's change counters exceed its thresholds.
-
How does the autovacuum launcher schedule work?
It starts a worker in each database once per autovacuum_naptime (1 minute), with at most autovacuum_max_workers (3) running at a time. It never runs VACUUM FULL, cannot reach temporary tables, and needs track_counts (on by default).
-
When is a table due for autovacuum (dead rows)?
When dead rows exceed min(10^8, 50 + 0.2 x reltuples): autovacuum_vacuum_threshold (50) plus scale factor (20%), capped by autovacuum_vacuum_max_threshold (new in PostgreSQL 18; 2,050 for 10,000 rows).
-
What triggers an insert-vacuum and an analyze?
An insert-vacuum is due at 1000 + 0.2 x reltuples x (unfrozen share of pages) — PostgreSQL 18 adds the unfrozen factor, where PostgreSQL 17 took 20% of every row. An analyze is due once inserted, updated and deleted rows together exceed 50 + 10% of the table.
Key takeaways
- VACUUM makes dead row versions’ space reusable, updates the visibility map and freezes old rows; autovacuum runs it and ANALYZE, with a worker in each database every autovacuum_naptime.
- A vacuum is due when dead rows exceed 50 + 20 % of reltuples, at most 100,000,000 since PostgreSQL 18, and an analyze when changes exceed 50 + 10 %, so the orders needed only ANALYZE.
- Inserts trigger a vacuum at 1,000 + 20 % of the unfrozen rows, so the frozen events were due with no dead rows.
Terms in this lesson
- autovacuum
- The launcher and worker processes that run VACUUM and ANALYZE on tables by themselves whenever a table's counts of dead, inserted or changed rows exceed thresholds worked out from its size and the settings.
- cost-based vacuum delay
- A throttle on VACUUM and ANALYZE: every page found in memory, read from disk or dirtied adds to a cost count, and when the count reaches a limit the process sleeps for a set delay before it goes on.
- storage parameter
- A setting stored with one table or index, given in CREATE TABLE … WITH (…) or ALTER TABLE … SET (…), that overrides the server-wide setting for that object, for example an autovacuum threshold.
References
- PostgreSQL 18 documentation: 24.1 Routine Vacuuming (the autovacuum daemon) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.10 Vacuuming (automatic vacuuming and cost-based vacuum delay) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.8 Error Reporting and Logging (log_autovacuum_min_duration) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 19.9 Run-time Statistics (track_counts, track_cost_delay_timing) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: VACUUM (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: CREATE TABLE (storage parameters) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: ALTER TABLE (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 13.3 Explicit Locking (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 27.4 Progress Reporting (pg_stat_progress_vacuum) (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 27.2 The Cumulative Statistics System (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: 52.11 pg_class (The PostgreSQL Global Development Group)
- PostgreSQL 18 documentation: psql (several statements in one request) (The PostgreSQL Global Development Group)
- PostgreSQL 18 source: src/backend/postmaster/autovacuum.c (thresholds and cost balancing) (The PostgreSQL Global Development Group)
- PostgreSQL 17 documentation: 24.1 Routine Vacuuming (the thresholds before version 18) (The PostgreSQL Global Development Group)
- PostgreSQL 18 release notes (The PostgreSQL Global Development Group)
- PostgreSQL 19 release notes (in preparation) (The PostgreSQL Global Development Group)
Report a problem with this lesson
Kept only in this browser. Your Learn progress