Does VACUUM FULL lock the table in PostgreSQL?

Short answer

VACUUM FULL orders writes a fresh, compact copy of the table and all its indexes, then swaps it in. For the whole copy it holds an ACCESS EXCLUSIVE lock: no SELECT, no INSERT, nothing. On our test table (3 million live rows, three indexes) that was 6 to 7 seconds; on a large production table it can be hours. It also needs enough free disk for the new copy while the old one still exists.

CLUSTER orders USING orders_created_at_idx is the same operation with the rows sorted by an index: same lock, same rewrite.

Plain VACUUM orders is different: it takes a SHARE UPDATE EXCLUSIVE lock, reads and writes carry on, and it makes dead rows’ space reusable inside the table. It doesn’t shrink the file. Usually that’s all you need.

What it locks

VACUUM FULL in a transaction fails with ERROR: VACUUM cannot run inside a transaction block. CLUSTER on a single table can run in one, which keeps the lock until COMMIT.

Like any ACCESS EXCLUSIVE request, VACUUM FULL first waits for every query using the table, and queries arriving meanwhile queue behind it.

Plain VACUUM has one exception: if it finds empty pages at the end of the table, it briefly takes ACCESS EXCLUSIVE to cut them off (per the documentation). VACUUM (TRUNCATE false) or the vacuum_truncate table setting turns that off.

Does it rewrite the table?

Yes. The table gets a new data file (pg_relation_filenode changed on every version) and every index is rebuilt. On a 3-million-row table with two thirds of its rows deleted and then a plain VACUUM:

before: table 219 MB, with indexes 283 MB
after: table 73 MB, with indexes 94 MB

The “before” figure is after the plain VACUUM: it left the table at its full original size. That space isn’t lost; later inserts and updates reuse it.

Run it safely

First ask whether you need it. Bloat that plain VACUUM can reuse often isn’t a problem. If the table keeps growing, look at why autovacuum isn’t keeping up (long-running transactions are the usual reason) before rewriting it.

If you need the space back on a table that must stay online, the pg_repack extension rebuilds a table while writes continue. Its documentation says it holds ACCESS EXCLUSIVE only briefly at the start and at the final swap, needs a primary key (or a unique index on a NOT NULL column), and needs free disk of about twice the table and its indexes. We didn’t test it here, and it has to be installed on the server.

If you run VACUUM FULL or CLUSTER:

1. Check you have free disk for a full copy of the table and its indexes:

   SELECT pg_size_pretty(pg_total_relation_size('orders'));
   

2. Run it in a maintenance window, with lock_timeout so it doesn’t sit in the lock queue:

   SET lock_timeout = '5s';
   VACUUM (FULL, VERBOSE) orders;
   

   On ERROR: canceling statement due to lock timeout, nothing has happened yet; retry later. See lock timeout. Once it has the lock, lock_timeout no longer matters: it keeps the lock until it finishes.

3. Watch progress from another session:

   SELECT pid, command, phase, heap_blks_scanned, heap_blks_total
   FROM pg_stat_progress_cluster;
   

Do one table at a time. VACUUM FULL with no table name rewrites every table in the database.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac). Session A ran VACUUM FULL on a 4-million-row table with three indexes after deleting a quarter of the rows; 0.3 s in, session B read pg_stat_progress_cluster and A’s locks, then ran a SELECT and an INSERT with lock_timeout = '300ms':

CREATE TABLE seo_mig.vf (id bigint PRIMARY KEY, n int, s text);
INSERT INTO seo_mig.vf SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 4000000) g;
CREATE INDEX ON seo_mig.vf (s);
CREATE INDEX ON seo_mig.vf (n);
DELETE FROM seo_mig.vf WHERE id % 4 = 0;
VACUUM seo_mig.vf;

VACUUM FULL seo_mig.vf;   -- session A

CLUSTER was checked inside a rolled-back transaction:

BEGIN;
CLUSTER seo_mig.vf USING vf_pkey;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
ROLLBACK;

On all five versions it held AccessExclusiveLock (and ShareLock) on the table and AccessExclusiveLock (and AccessShareLock) on vf_pkey, and the table got a new data file. CLUSTER of the 3-million-row seo_mig.big by an index on n took 2.4 to 3.1 s (median of three). The size figures came from pg_relation_size and pg_total_relation_size on a second, 3-million-row copy with two thirds of its rows deleted; plain VACUUM on a table with half its rows deleted took 0.4 to 1.2 s.

In Inlet

While a VACUUM FULL or CLUSTER runs, the Activity monitor shows which session blocks which, so you can see what’s queued behind it, and cancel it if it’s taking too long.