PostgreSQL VACUUM FULL When the Current Disk Has No Free Space

VACUUM FULL solves one storage problem by temporarily creating another one: PostgreSQL rewrites the relation into a new file and keeps the old file until the rewrite succeeds. If the volume that holds the table is nearly full, the command can fail before it returns a single gigabyte to the operating system. The practical escape hatch is an auxiliary volume registered as a temporary tablespace.

What “without disk space” really means

This procedure does not make a rewrite happen without storage. It means “without enough free space on the volume currently holding the table.” You still need an attached disk, SAN/NFS volume with suitable durability and latency, or another local filesystem with enough temporary capacity. PostgreSQL documents that VACUUM FULL takes an ACCESS EXCLUSIVE lock and needs extra disk because it writes a new table copy before releasing the old one.

A plain VACUUM (ANALYZE) should remain the default maintenance operation: it reclaims dead-tuple space for reuse inside the same table and normally allows reads and writes to continue. Use FULL only when you must return substantial space to the operating system and can accept a maintenance window.

Preflight: measure before moving anything

Replace public.orders with the relation you intend to rewrite. This query separates the heap/TOAST footprint from indexes and shows the current tablespace:

SELECT
  c.oid::regclass AS relation,
  COALESCE(t.spcname, 'pg_default') AS tablespace,
  pg_size_pretty(pg_table_size(c.oid)) AS table_and_toast,
  pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.oid = 'public.orders'::regclass;

For a conservative maintenance budget, give the auxiliary volume at least 2 × pg_total_relation_size plus 20%. The first copy is created while objects move into the tablespace; the second can exist while VACUUM FULL rewrites the table and rebuilds its indexes. Highly bloated tables may need less, but a production runbook should not depend on the optimistic case.

• Keep free space on the original PostgreSQL volume for pg_wal. Table moves and rewrites can generate substantial WAL.
• Confirm replicas and archiving can consume the burst; an inactive replication slot can retain WAL and fill the original disk.
• Estimate the maintenance window and test the full sequence on a restored copy first.
• Take and verify a recoverable backup. A tablespace move is maintenance, not a backup strategy.

Check dead tuples and lock blockers

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE relid = 'public.orders'::regclass;

SELECT pid, usename, state, xact_start, wait_event_type, wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Both ALTER TABLE ... SET TABLESPACE and VACUUM FULL need strong locks. A short lock_timeout prevents the move from waiting invisibly behind application traffic, but the correct fix is a controlled maintenance window with new sessions drained.

1. Prepare the auxiliary filesystem

As root, mount the extra volume and create an empty directory owned by the operating-system account that runs PostgreSQL:

install -d -o postgres -g postgres -m 700 /mnt/pg_vacuum_tmp/ts_vacuum
df -hT /mnt/pg_vacuum_tmp
findmnt /mnt/pg_vacuum_tmp

On SELinux-enabled distributions, label the mount before PostgreSQL accesses it:

semanage fcontext -a -t postgresql_db_t "/mnt/pg_vacuum_tmp(/.*)?"
restorecon -Rv /mnt/pg_vacuum_tmp

2. Register a temporary tablespace

Run this as a PostgreSQL superuser. CREATE TABLESPACE cannot run inside a transaction block:

CREATE TABLESPACE ts_vacuum_tmp
  LOCATION '/mnt/pg_vacuum_tmp/ts_vacuum';

SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace
WHERE spcname = 'ts_vacuum_tmp';

3. Move the indexes and table to the auxiliary disk

The table move does not move its indexes. In psql, the following sequence moves every valid index first, then the heap and TOAST data. Execute it in the database that owns the table:

 et ON_ERROR_STOP on
SET lock_timeout = '10s';

SELECT format(
         'ALTER INDEX %s SET TABLESPACE ts_vacuum_tmp;',
         indexrelid::regclass
       )
FROM pg_index
WHERE indrelid = 'public.orders'::regclass
  AND indisvalid
gexec

ALTER TABLE public.orders SET TABLESPACE ts_vacuum_tmp;

For a partitioned table, moving the parent does not move its partitions. Inventory and move each leaf partition explicitly. Also check separately managed materialized views or indexes that are not attached to the target relation.

4. Run VACUUM FULL on the auxiliary disk

Run the command outside an explicit transaction. Using vacuumdb makes the database and table target explicit:

vacuumdb --dbname=app_production   --table='public.orders'   --full --analyze --verbose

Watch all three storage paths while it runs: the auxiliary mount, the original data/WAL mount, and the archive destination. From another session you can follow progress on supported PostgreSQL versions:

SELECT pid, datname, relid::regclass AS relation,
       phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_cluster;

VACUUM FULL uses the same rewrite progress view as CLUSTER. An empty result after completion is normal.

5. Verify the result before moving back

SELECT
  c.oid::regclass AS relation,
  COALESCE(t.spcname, 'pg_default') AS tablespace,
  pg_size_pretty(pg_table_size(c.oid)) AS table_and_toast,
  pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.oid = 'public.orders'::regclass;

SELECT count(*) FROM public.orders;

Run application smoke tests and confirm replicas are caught up. If the compacted relation still does not fit safely on the original volume, stop here and keep it on a permanent, properly monitored tablespace instead of forcing the trip back.

6. Move the compacted objects back and clean up

 et ON_ERROR_STOP on
SET lock_timeout = '10s';

ALTER TABLE public.orders SET TABLESPACE pg_default;

SELECT format(
         'ALTER INDEX %s SET TABLESPACE pg_default;',
         indexrelid::regclass
       )
FROM pg_index
WHERE indrelid = 'public.orders'::regclass
  AND indisvalid
gexec

DROP TABLESPACE ts_vacuum_tmp;

DROP TABLESPACE succeeds only when no object remains there. After it succeeds, unmount and detach the temporary volume using your operating-system or cloud procedure. Never delete the tablespace directory while PostgreSQL still has it registered.

Failure plan and safer alternatives

Monitor the cause, not only the cleanup

A successful rewrite treats the accumulated bloat; it does not explain why it accumulated. PG Monitoring follows dead-tuple growth, autovacuum cadence, long transactions, table size, replication-slot retention, and disk forecasts continuously. That evidence tells the team whether the permanent fix is autovacuum tuning, transaction hygiene, partitioning, or capacity—not another emergency VACUUM FULL.

References: PostgreSQL VACUUM documentation, CREATE TABLESPACE, ALTER TABLE, and the original tablespace workaround that inspired this expanded runbook.