An update can leave an older version
Under PostgreSQL MVCC, an update creates a new row version and an older version can remain necessary for a reader. The visible row count can stay constant while internal storage changes. This procedure updates twenty thousand rows once and separately observes statistics, VACUUM reporting and relation size. It is a PostgreSQL 18 reproduction procedure, not an executed measurement transcript. Use a disposable local database. Do not apply the statements as an unreviewed cleanup or storage-diagnosis recipe against a production table. The intended lesson is to distinguish several observations that are often incorrectly described as one measure of “deleted rows.”
Limit the observation with a temporary table
Create a temporary table in one session and initialize every value to zero. Fillfactor can leave room useful for updates, but it does not guarantee that every update is HOT or that a fixed number of pages will be allocated. Row width, free page space and indexes influence those outcomes. Because the table is temporary, another session is not retaining an old snapshot of this particular table. Reproducing snapshot retention by a long transaction would require another procedure using an ordinary table and multiple sessions. Keep that separate experiment distinct rather than claiming this one demonstrates an unobserved blocking condition.
Collect size, estimates and logical contents
Read relation size, increment every value and inspect statistics after ANALYZE. Then execute VACUUM VERBOSE ANALYZE, inspect size again and verify current row contents. VACUUM must run outside an explicit BEGIN block. The final logical result should contain twenty thousand rows with both minimum and maximum value one, but the internal dead-tuple estimate and byte sizes are not fixed expected transcripts. If a statement fails, stop and record which operations completed rather than interpreting later output as the intended full procedure. Logical-result checks and storage observations answer different questions and should be reported separately.
-- PostgreSQL 18, disposable local database, autocommit enabled.
CREATE TEMP TABLE lab_vacuum(id integer, value integer) WITH (fillfactor=70);
INSERT INTO lab_vacuum SELECT i, 0 FROM generate_series(1, 20000) AS i;
ANALYZE lab_vacuum;
SELECT pg_relation_size('lab_vacuum') AS bytes_before;
UPDATE lab_vacuum SET value=value+1;
ANALYZE lab_vacuum;
SELECT n_live_tup, n_dead_tup FROM pg_stat_all_tables
WHERE relid='lab_vacuum'::regclass;
VACUUM (VERBOSE, ANALYZE) lab_vacuum;
SELECT pg_relation_size('lab_vacuum') AS bytes_after;
SELECT count(*), min(value), max(value) FROM lab_vacuum;
Treat dead-tuple statistics as estimates
The live- and dead-tuple fields in pg_stat_all_tables are estimates. Statistics update time, observation snapshots and collection timing can affect whether a just-completed operation is immediately reflected. Do not equate the number of updated rows with an exact increase in n_dead_tup. Read the verbose vacuum report alongside the statistics and identify the meanings of removable versions, remaining versions and processed pages. A small estimate alone is not proof that no older version exists. Preserve the collection sequence in a report so apparent changes are not attributed to cleanup when they instead reflect refreshed statistics.
Separate reuse from returning bytes to the OS
Ordinary VACUUM can make space reusable without necessarily shrinking the relation file. Tail truncation has particular conditions and is not equivalent to compacting every internal free area. VACUUM FULL rewrites the table and introduces different locking and additional-space requirements; it is not part of this procedure. Unchanged file size alone therefore does not establish that ordinary vacuuming failed. Describe reclaimed reusable space separately from bytes returned to the operating system. Otherwise, a reader can incorrectly escalate to a much more disruptive rewrite merely because one relation-size measurement did not decrease.
Add conditions before applying the result
For ordinary tables, long transactions, replication-related retention, update rates and autovacuum configuration also matter. Transaction-ID freezing has a purpose distinct from, though related to, removal of obsolete versions. One update experiment cannot determine a universal vacuum interval or threshold. After testing, close the connection containing the temporary table and retain version, row count, settings, estimates and verbose reporting. In diagnosis, distinguish a large table, a high dead-version estimate and inhibited cleanup. They can be related without being identical observations, and each can require different evidence before an operational change is justified.