After a large deletion, a PostgreSQL table may retain a large file even when only a small fraction of its rows remain. Ordinary vacuum makes obsolete space reusable; VACUUM FULL rewrites the surviving contents into a new file and rebuilds indexes. That stronger reclamation has a different lock and capacity budget, so it belongs to planned maintenance rather than routine reaction to a large size number.

When it breaks

The rewrite takes an ACCESS EXCLUSIVE table lock. It conflicts with ordinary readers as well as writers, including a transaction that already finished its query but remains open while holding a table lock. Calling it an exclusive row lock, or assuming read traffic can continue, understates the outage risk. A queued rewrite can also affect later lock requests while it waits.

The old storage remains until replacement succeeds, so the operation needs additional disk space. Starting only when the filesystem is already full can remove the room needed to recover. Estimate the replacement table and indexes, I/O and WAL impact, and acceptable lock duration. Regular autovacuum does not automatically perform this rewrite.

Check it quickly

Open two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, in the same disposable PostgreSQL 18 database. Run vacuum commands outside explicit transaction blocks. B creates five thousand rows, deletes the first 4,500, and performs ordinary vacuum:

CREATE SCHEMA compact_demo;
CREATE TABLE compact_demo.items (id integer PRIMARY KEY,payload text)
  WITH (autovacuum_enabled=false);
INSERT INTO compact_demo.items
SELECT g,repeat(md5(g::text),10) FROM generate_series(1,5000) AS g;
CREATE TABLE compact_demo.baseline AS
SELECT pg_relation_size('compact_demo.items') AS heap_bytes,
       pg_relation_filenode('compact_demo.items') AS file_id;
DELETE FROM compact_demo.items WHERE id<=4500;
VACUUM (ANALYZE) compact_demo.items;
SELECT pg_relation_size('compact_demo.items')=heap_bytes AS same_heap_size,
       pg_relation_filenode('compact_demo.items')=file_id AS same_file
FROM compact_demo.baseline;

The two comparisons return true: heap bytes and the relation’s file identifier remain unchanged. The surviving rows occupy the tail, so ordinary vacuum cannot shrink this particular file by truncating an empty end. This does not establish that ordinary vacuum never shrinks files; empty trailing pages can be returned when truncation is possible.

In A, read the remaining five hundred rows and keep the transaction open:

BEGIN;
SELECT count(*) FROM compact_demo.items;

A has made no updates, but still holds its table lock. In B, give the rewrite a short acquisition budget so the demonstration does not wait indefinitely:

SET lock_timeout='100ms';
\set ON_ERROR_STOP off
VACUUM (FULL) compact_demo.items;
\echo full_state :SQLSTATE
\set ON_ERROR_STOP on
RESET lock_timeout;

Expect full_state 55P03. The existing reader prevents the stronger lock from being acquired. Temporarily allowing the expected error shows the result without skipping cleanup, and resetting the timeout restores B’s original session setting. This timeout is a laboratory value, not a suggested production maintenance budget.

End the reader transaction in A:

COMMIT;

Now B can perform the rewrite, inspect storage identity, and verify every retained payload:

VACUUM (FULL,ANALYZE) compact_demo.items;
SELECT pg_relation_size('compact_demo.items')<heap_bytes AS smaller_heap,
       pg_relation_filenode('compact_demo.items')<>file_id AS different_file
FROM compact_demo.baseline;
SELECT count(*),min(id),max(id),sum(id),
       bool_and(payload=repeat(md5(id::text),10)) AS payloads_preserved
FROM compact_demo.items;
DO $$ BEGIN
  IF (SELECT sum(id) FROM compact_demo.items)<>2375250
     OR (SELECT count(*) FROM compact_demo.items)<>500
     OR EXISTS (SELECT FROM compact_demo.items
                WHERE payload<>repeat(md5(id::text),10)) THEN
    RAISE EXCEPTION 'compaction changed application data';
  END IF;
END $$;
DROP SCHEMA compact_demo CASCADE;

Both storage comparisons are true: the heap is smaller and its file identifier has changed. The data check returns five hundred rows, identifiers 4501 through 5000, an identifier sum of 2,375,250, and true payload preservation. The assertion checks the row population and content; a smaller file alone would not prove correct compaction. Finally, the fixture schema is removed.

Decide whether compaction pays for itself

If future inserts will reuse the free space, keeping it inside the relation may be preferable to paying for a rewrite and later growing the file again. Measure persistent excess space and access cost after the workload settles. Distinguish the main heap from indexes and auxiliary storage, because a single table-size number may combine several objects with different causes of growth.

Schedule the operation where its exclusive lock is acceptable, and check for long-running readers before requesting it. A low-traffic period does not guarantee no open transaction exists. Set an intentional acquisition budget, observe failure rather than endlessly retrying, and verify data and storage afterward. Keep ordinary vacuum healthy so severe accumulation does not become the normal maintenance cycle.

The experiment demonstrates lock conflict, file replacement, and preserved rows on a small table. It does not predict rewrite duration or spare-space needs for a production table with large indexes, external values, or replication traffic. Those quantities require measurements from the actual object and a maintenance plan that can tolerate the resulting lock window.

Sources