Bloat & vacuum health
This PostgreSQL 18.6 Scenario observes estimated table statistics before and after manual VACUUM. It does not measure production scan speed, free bytes, autovacuum scheduling, or wraparound prevention. Compare it with the page-level tuple lesson and the queue's occupied-slot measurements.
A> INSERT INTO inventory SELECT g, 10 FROM generate_series(1, 5) g;
INSERT 0 5
A> UPDATE inventory SET qty = qty + 1 WHERE id <= 3;
UPDATE 3
A> SELECT pg_stat_force_next_flush(); -- stats reach the views lazily; force it for the demo
pg_stat_force_next_flush
--------------------------
(1 row)These are statistics estimates, not an exact heap scan. The small run reports five live and three dead rows:
M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NULL AS no_vacuum_timestamp
FROM pg_stat_user_tables WHERE relname = 'inventory';
relname | n_live_tup | n_dead_tup | no_vacuum_timestamp
-----------+------------+------------+---------------------
inventory | 5 | 3 | t
(1 row)After manual VACUUM, the estimate is zero dead rows and last_vacuum is present:
A> VACUUM inventory;
VACUUM
M> SELECT relname, n_live_tup::int, n_dead_tup::int, last_vacuum IS NOT NULL AS has_vacuum_timestamp
FROM pg_stat_user_tables WHERE relname = 'inventory';
relname | n_live_tup | n_dead_tup | has_vacuum_timestamp
-----------+------------+------------+----------------------
inventory | 5 | 0 | t
(1 row)Compare database xid age with the configured autovacuum freeze-launch threshold. Below it is not proof that freezing will finish in time:
M> SELECT age(datfrozenxid) < current_setting('autovacuum_freeze_max_age')::int AS below_freeze_threshold
FROM pg_database WHERE datname = current_database();
below_freeze_threshold
------------------------
t
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
Reading the dashboard
The statistics manual defines n_dead_tup as "Estimated number of dead rows". Here the forced statistics flush is followed by asserted estimates of five live and three dead rows. After manual VACUUM the view reports five live, zero dead, and a non-null last_vacuum. Estimates are not exact heap counts, and accumulated statistics can lag or remain cached within a monitoring transaction.
last_vacuum records manual vacuum activity; last_autovacuum records automatic activity. Neither timestamp proves that every old version was removable. This run checks the manual field only. An old automatic timestamp can reflect workload thresholds, configuration, scheduling, or lock interference; a retained horizon can limit removal even when vacuum runs. A timestamp alone does not identify the cause.
Documented scope: PostgreSQL 18's freeze policy uses per-table relfrozenxid; database datfrozenxid is the minimum across tables. autovacuum_freeze_max_age is a launch threshold, 200 million by default, not the actual xid-wraparound boundary. Anti-wraparound vacuum can be invoked even with ordinary autovacuum disabled. Starting it does not prove completion.
The Scenario asserts only that the current database age is below that configured threshold. Alerting at half the threshold is an example policy, not an engine guarantee or proof that forced vacuum can never occur. Track age trends and table-level ages too; a boolean alone does not show how rapidly the remaining margin is consumed. No threshold crossing or xid exhaustion is executed.
Operational advice: use dead/live estimates, vacuum timestamps, table sizes, and workload trends as investigation signals, not a guaranteed scan-performance diagnosis. Check old transactions, prepared work, and replication-slot horizons when removal is constrained. These are candidates, not a claim that vacuum tuning is never needed. The pgstattuple extension can inspect a relation for more detailed tuple and free-space statistics, with scan cost and concurrent-activity limits; it is not executed here.