Getting the Disk Back: VACUUM FULL, pg_repack and What They Really Lock

VACUUM FULL does not cost you the length of the rewrite. It costs you the length of the rewrite plus however long the query that was already running takes to finish — and during that first part the table is unavailable while VACUUM FULL does nothing at all.

The reason is one rule in the lock manager. VACUUM FULL needs an ACCESS EXCLUSIVE lock, the mode that conflicts with every other mode including the ACCESS SHARE that a plain SELECT takes. If a SELECT is already running, the request cannot be granted, so it joins the wait queue. And a request that conflicts with a request already waiting must wait too. So the reporting query that had four more minutes to run does not just delay your maintenance: it stops the whole table, for everyone, from the instant you typed the command.

That is the first thing people get wrong. The second is the fix: pg_repack is widely described as the online, lock-free alternative. It is neither. It takes ACCESS EXCLUSIVE twice, it resolves the wait by cancelling and then terminating your backends, and it needs about twice the size of the table in free disk — which, if you are reading this, is the resource you have run out of.

The panel below runs one bloated table — bloated because plain VACUUM frees dead rows for reuse without returning the space — through four strategies. Pick a strategy, then drag query already running on the table. That slider carries the lesson: it changes nothing about the work to be done and everything about what the work costs.

Start where the defaults are: a 50 GB relation, 60% of it dead space, and one SELECT that has five minutes left to run. VACUUM FULL reports a 16 min stall. Eleven of those minutes are the rewrite. The other five are pure waiting, and they are the expensive five, because nothing is happening: no progress, no rows copied, no entry in pg_stat_progress_cluster — that view has no row for your command until the lock is granted. Meanwhile 393,000 ordinary queries pile up behind a request that has not started work.

Now drag the query slider to 0. The stall drops to 11 minutes and 273,000 queries. That is the floor: even with a completely idle table, VACUUM FULL is an eleven-minute outage on this table. There is no setting that improves it, because the lock is held for the whole rewrite by design.

The rule that makes waiting contagious

Postgres grants a table-level lock only if the mode being asked for conflicts with nothing. "Nothing" has two parts, and the second is the one that surprises people. From lock.c in the server source:

/*
 * If lock requested conflicts with locks requested by waiters, must join
 * wait queue.  Otherwise, check for conflict with already-held locks.
 * (That's last because most complex check.)
 */
if (lockMethodTable->conflictTab[lockmode] & lock->waitMask)
    found_conflict = true;

waitMask is the union of the modes every waiter has asked for. So the test is not "does my lock conflict with what is held" but "does it conflict with what is held or with anything already in the queue". Without that rule an exclusive request on a busy table would starve forever behind an endless stream of readers. With it, one blocked exclusive request stops every conflicting request behind it, whether or not those requests conflict with each other.

Follow it through with the numbers from the panel. A reporting SELECT holds ACCESS SHARE. Your VACUUM FULL asks for ACCESS EXCLUSIVE, which conflicts with it, so it queues. The next ordinary SELECT to arrive also wants ACCESS SHARE — which does not conflict with the reporting query at all, and would have been granted instantly a second earlier. But it does conflict with the queued ACCESS EXCLUSIVE, so it queues as well. Two seconds later so has every other query, and the connection pool is full: at 400 queries a second, the max_connections default of 100 — "typically 100", says the documentation, since initdb lowers it if the kernel will not support it — is exhausted in about a quarter of a second and the application starts seeing FATAL: sorry, too many clients already — including on tables that have nothing to do with this one.

This is why "we ran it during the maintenance window and it still took the site down" is such a common story. The maintenance window was quiet in terms of load; it was not quiet in terms of long transactions, because the nightly analytics job runs then too. One 20-minute query and one VACUUM FULL in the same window is a 20-minute total outage on that table before the rewrite has copied a single row. Whether a session is still holding that lock depends on what it is doing and, for transactions in REPEATABLE READ or SERIALIZABLE, on the fact that it holds its locks until commit rather than until the statement ends.

Lock modes from the PostgreSQL 18 documentation's table of conflicting lock modes; the pg_repack and pg_squeeze rows from their own documentation and source. CLUSTER is VACUUM FULL plus an ordering pass — same lock, same rewrite, same cost, so anything this lesson says about one applies to the other.

What pg_repack actually does

pg_repack's documented full-table procedure is seven steps, and knowing them is the difference between using it well and being surprised by it:

1. create a log table to record changes made to the original table;
2. add a trigger to the original table that writes every INSERT, UPDATE and DELETE into that log table;
3. create a new table holding all the rows of the old one;
4. build the indexes on the new table;
5. apply everything that accumulated in the log table;
6. swap the tables, indexes and TOAST tables — where Postgres keeps oversized column values out of line — in the system catalogs;
7. drop the original.

Steps 1–2 and steps 6–7 run under ACCESS EXCLUSIVE. Everything between them runs under SHARE UPDATE EXCLUSIVE, which conflicts with schema changes and with vacuum but not with reads or writes. That is a genuinely good design, and it is why pg_repack turns an eleven-minute outage into two short ones. It is not a lock-free design, and the difference shows up in three places.

First, both exclusive windows queue like any other. Set the strategy to pg_repack and leave the running query at 300 s. The longest stall is 61 s, not two seconds: 60 of them are pg_repack waiting for the setup lock with the whole table stopped behind it, and it ends only because --wait-timeout expires and pg_repack cancels the query. Now drag --wait-timeout up to 300, the setting a careful person picks to avoid killing other people's work. The stall becomes five minutes. Being polite to the reporting query means being brutal to everything else, and the timeout is the dial between those two.

Second, the trigger has a price. Every write to the source table now also writes a row to the log table, so write-heavy tables pay double for the duration, in both time and write-ahead log volume. Worse, the log has to be drained faster than it fills. Push row writes per second to 3,000 and the outcome flips to never caught up: the log gains rows at least as fast as replay removes them, the swap is never reached, and twelve hours later pg_repack is still running with a log table of millions of rows. That is not a hypothetical — it is why pg_repack grew a --switch-threshold option, documented as a way "to avoid the inability to catchup with write-heavy tables".

Third, it needs more disk than VACUUM FULL, not less. The documentation asks for free space about twice the size of the table and its indexes — for a 50 GB relation, 100 GB free. VACUUM FULL on the same table needs only a copy of the live data, 20 GB. Drag free disk on the volume down to 60 GB and watch the two strategies swap places: the offline tool finishes, the online one fails partway and leaves a trigger and a log table behind for you to drop. When the reason you are doing this is that the volume is at 95%, that inversion is the whole decision.

One more constraint decides whether you can use it at all: the target table must have a primary key, or at least a unique index over columns that are all NOT NULL. Without one pg_repack refuses with ERROR: relation "..." must have a primary key or not-null unique keys, because the log replay has no way to identify the row a logged change refers to. pg_squeeze has the same requirement in a different spelling: it needs an identity index, which a primary key provides and which you otherwise set with ALTER TABLE ... REPLICA IDENTITY USING INDEX ....

Where each one breaks

pg_squeeze replaces the trigger with logical decoding — reading the committed changes out of the write-ahead log through a replication slot instead of capturing them with a trigger — and does the whole thing inside the server as a background worker. Writers pay nothing extra, and because it holds only ACCESS SHARE during the copy, autovacuum on the table keeps running. Set the strategy to pg_squeeze with the defaults and the longest stall is 120 ms.

Its boundary is at the other end. The final merge needs ACCESS EXCLUSIVE, and pg_squeeze cancels nobody — so it queues behind whatever is running at that moment, which is eleven minutes after you started and no longer under your eye. Drag the running query to 900 s with pg_squeeze selected: the stall reappears, now arriving unannounced near the end of the operation. The knob for this is squeeze.max_xlock_time, which bounds how long the lock may be held, not how long it waits. Turn the toggle on and set writes to 2,000, and it fails: pg_squeeze releases the lock, catches up, and retries the final stage four times before raising ERROR: "squeeze_max_xlock_time" prevented squeeze from completion. Four is not a setting: pg_squeeze.c retries for (i = 0; i < 4; i++) and then gives up. Left unset — the default is 0, meaning no bound — it would have held the lock for as long as the merge needed instead. There is no setting that gives you both. Push writes to 3,000 instead and you never reach the merge at all: at that rate the decoded changes arrive as fast as they can be applied and the copy never catches up.

It also needs wal_level = logical (on PostgreSQL 18 and earlier), a spare max_replication_slots entry and an entry in shared_preload_libraries, so adopting it costs a restart. And while it runs, its slot behaves like any other: see what a replication slot retains for the failure mode where a slot outlives the process that created it.

REINDEX CONCURRENTLY is the strategy people skip, and the panel shows why they should not. Select it: the longest stall is zero. It holds SHARE UPDATE EXCLUSIVE at session level for the whole rebuild and never takes ACCESS EXCLUSIVE on the table. It does wait for transactions that might be using the index, several times over the rebuild — the documented procedure waits around each of the two build passes and again before dropping the old index — but waiting on a mode that does not conflict with ACCESS SHARE or ROW EXCLUSIVE blocks nothing.

Its boundary is what it can reach. With indexes at 35% of the relation it returns 11 GB of the 30 GB of dead space and the heap keeps the rest. Drag the index share to 65% and it recovers 20 of 30. Drag it to 0 and it recovers nothing at all. So the question to answer before choosing a strategy is not "how bloated is this table" but "how much of the bloat is in the indexes" — and on an update-heavy table the answer is often most of it, because a B-tree never merges half-empty pages back together. That is the subject of index bloat, and it is worth checking first: the cheapest rewrite is the one you find out you do not need.

VACUUM FULL has a boundary people meet by accident. It is not resumable. Cancel it at minute ten of eleven and the new copy is discarded and the old file is untouched — you paid the entire outage and reclaimed nothing. The panel models this in the out-of-disk case: drag free disk to 15 GB and the run ends with ERROR: could not extend file after holding the table for 14 minutes — five waiting for the lock and nine writing a copy it then throws away — and returns zero bytes. There is no partial credit and no way to run it "for a while".

A boundary all three rewrites share: none of them can remove a dead row that is still visible to somebody. A rewrite copies the rows that are live according to the same visibility horizon that plain VACUUM uses, so if one old transaction or one inactive replication slot is holding that horizon back, your expensive rewrite copies the bloat into the new file and you end up where you started. Check the horizon before booking the outage, not after.

And the rewrite gives away something you may want back. VACUUM FULL packs pages to 100% full. A table that gets updates needs free space on the page for a new row version to land beside the old one, which is what a heap-only tuple update requires; pack it solid and the first update to touch each page must go elsewhere and write to every index. If you are rewriting an update-heavy table, set fillfactor — the percentage of each page a write is allowed to fill — to something like 90 first, so the rewrite leaves the room. pg_repack and pg_squeeze honour the table's fillfactor as well. And the space plain VACUUM would have kept for reuse is tracked in the free space map, which the rewrite resets along with everything else.

Doing it on a real system

Before anything else, find out whether you need a rewrite at all. This splits the bloat between heap and indexes, which decides between an outage and a free lunch:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(pg_relation_size('orders'))       AS heap,
       pg_size_pretty(pg_indexes_size('orders'))        AS indexes,
       (SELECT round(dead_tuple_percent + free_percent, 1)
          FROM pgstattuple('orders'))                   AS heap_waste_pct;

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid))     AS size,
       (pgstatindex(indexrelid)).avg_leaf_density       AS leaf_density
FROM pg_stat_user_indexes WHERE relname = 'orders';

pgstattuple scans the whole relation, so run it on a replica or off-hours. If leaf_density is well under 90 on the big indexes, REINDEX CONCURRENTLY gets you that space with no stall, and you may not need to touch the heap at all.

Second, find out what is running before you request the lock, because that query — not the size of the table — decides the length of the outage:

SELECT pid, state, now() - xact_start AS open_for, left(query, 60)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND xact_start IS NOT NULL
ORDER BY xact_start;

Third, never let the request queue unbounded. Setting lock_timeout in the same session converts "the site is down" into "the command failed, try again":

SET lock_timeout = '3s';
VACUUM (FULL, VERBOSE) orders;
-- ERROR:  canceling statement due to lock timeout

A three-second lock_timeout with a retry loop is the single most valuable habit in this entire lesson. It costs nothing when the table is quiet and it makes the catastrophic case impossible: the request either gets the lock almost immediately or gives up before anything has queued behind it. Note that statement_timeout does not do this — it bounds the statement once it is running, not the wait for the lock.

While a rewrite runs, this is the progress view — and its absence is informative:

SELECT pid, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_cluster;   -- VACUUM FULL and CLUSTER report here

If your VACUUM FULL has no row in that view, it has not started: it is still waiting for the lock. That is guaranteed by the order of two statements in cluster.c — cluster_rel() opens with Assert(CheckRelationLockedByMe(OldHeap, AccessExclusiveLock, false)) and only then calls pgstat_progress_start_command(). That is the diagnostic difference between "this is slow" and "this has not begun", and people routinely misread the second as the first. To see what it is waiting for, walk the queue — the blocked entry is the one with granted = false:

SELECT a.pid, a.state, l.mode, l.granted,
       now() - a.xact_start AS open_for,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(a.query, 50) AS query
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.xact_start;

Read that output top to bottom and you can see the mechanism directly: one granted AccessShareLock held by an old query, one ungranted AccessExclusiveLock, and then a growing pile of ungranted AccessShareLock rows that are waiting on nothing but the AccessExclusiveLock in front of them. pg_blocking_pids() on any of those returns the pid of the original reporting query, which is the fastest route to the one backend you need to end.

Finally, the strategy that beats all four: do not accumulate the bloat in a table you have to rewrite. If the dead space comes from deleting old rows on a schedule, make the table partitioned by time and DROP the old partition instead — an ACCESS EXCLUSIVE lock held for milliseconds on a table nobody is reading, and the space returns to the operating system immediately. If it comes from update churn, the answer is per-table autovacuum settings aggressive enough to hold a steady state, plus a lower fillfactor so updates stay on the page. A rewrite is what you do once, after you have fixed the thing that caused it.

Next: the reason the file grew in the first place, in MVCC, dead tuples and what VACUUM does not do, and the settings that stop it growing again, in autovacuum tuning.