PostgreSQL 19 REPACK: VACUUM FULL Without the Lock

Every Postgres operator has a story about VACUUM FULL: the table was bloated, the disk was filling, someone ran it, and the application went dark because the command takes an ACCESS EXCLUSIVE lock for its entire run. PostgreSQL 19, now at release candidate 1, finally ships the fix in core: a new REPACK command, and REPACK (CONCURRENTLY) to rewrite a table while it keeps serving reads and writes. This post covers what REPACK does, how to run it without surprises, and the limits in the documentation that you should read before trusting it with production.

Why VACUUM FULL hurt, and what REPACK is

A plain VACUUM marks dead tuples reusable but rarely hands space back to the operating system. To really shrink a table you had to rewrite it, which is what VACUUM FULL and CLUSTER do. The 19 release notes describe the new command bluntly: the two former commands did similar things, but with confusing names, so they are unified as REPACK. The old commands are retained for compatibility.

Per the REPACK reference page, it reclaims space occupied by dead tuples by rewriting the whole table into a new file with no extra space, apart from what the fillfactor storage parameter guarantees. Add USING INDEX and the rows are also physically reordered by an index, which is the old CLUSTER behaviour.

-- rewrite one table, reclaim space (like VACUUM FULL)
REPACK (VERBOSE, ANALYZE) orders;

-- rewrite and physically order rows by an index (like CLUSTER)
REPACK orders USING INDEX orders_created_at_idx;

-- rewrite while other sessions keep using the table
REPACK (CONCURRENTLY, VERBOSE) orders;

How CONCURRENTLY works

Without CONCURRENTLY, REPACK takes an ACCESS EXCLUSIVE lock at the start and holds it throughout. With CONCURRENTLY, the bulk of the work runs under SHARE UPDATE EXCLUSIVE, which allows normal reads and writes. Changes that happen while the new copy is being built are captured with logical decoding and applied before the final step. Only then does REPACK request the ACCESS EXCLUSIVE lock to swap the table and index files, and the docs say the lock is typically held for a short time, depending on how many changes piled up while it waited for the lock.

Hands-on: a safe runbook

Start by measuring. The query below uses only standard catalog functions and shows your biggest tables and their index weight. REPACK needs free disk of at least the table size plus index sizes, since it builds a full temporary copy of both.

SELECT c.relname,
       pg_size_pretty(pg_table_size(c.oid))   AS table_size,
       pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
       s.n_live_tup, s.n_dead_tup
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;

Then run the repack and watch it from a second session. Each backend running REPACK reports progress in the pg_stat_progress_repack view, which the release notes say also covers CLUSTER and VACUUM FULL.

-- session 1
SET maintenance_work_mem = '2GB';   -- large, but no more than the RAM you can dedicate
REPACK (CONCURRENTLY, VERBOSE, ANALYZE) orders;

-- session 2
SELECT * FROM pg_stat_progress_repack;
SELECT pid, backend_xid, state FROM pg_stat_activity
WHERE query ILIKE 'REPACK%';

The docs advise setting maintenance_work_mem to a reasonably large value before repacking, and recommend watching backend_xid in pg_stat_activity for very large tables. For wraparound purposes REPACK counts as a single long-running transaction, so it holds back VACUUM from cleaning dead rows in other tables while it runs. Use ANALYZE (or run ANALYZE afterwards) because the planner records ordering statistics.

Limits and gotchas from the docs

CONCURRENTLY is not a free lunch. The reference page lists cases where it cannot be used: the relation is not a table (for example a materialized view), the table is UNLOGGED or partitioned, it lacks a primary key and an index-based replica identity, it is a system catalog or TOAST table, its access method is not heap, or REPACK runs inside a transaction block. It also needs a replication slot, so it fails if max_repack_replication_slots does not allow another one.

• max_repack_replication_slots is a new server variable; the default is 5 and it can only be set at server start.
• REPACK (CONCURRENTLY) is not MVCC-safe: after it commits, the table appears empty to concurrent transactions that use a snapshot taken before the repack started.
• It can fail if other transactions run DDL on the table during the repack.
• Concurrent updates and deletes need a small fixed amount of memory each; if more than about 104 million rows are concurrently updated or deleted, the command fails.
• With USING INDEX, rows inserted after the repack started are not ordered.
• REPACK refuses to process a table that has an invalid index; drop or reindex it first.

REPACK versus the autovacuum improvements

REPACK is for a one-off rewrite. The same release also improves day-to-day bloat control: autovacuum can now use parallel workers, controlled by the autovacuum_max_parallel_workers server variable and the per-table autovacuum_parallel_workers storage parameter, and a scoring system now decides the order in which autovacuum processes tables. A new pg_stat_autovacuum_scores view reports per-table details. The sensible plan is to let tuned autovacuum prevent bloat and keep REPACK for the cleanup you cannot avoid.

Takeaway

PostgreSQL 19 is still in the release candidate stage as I write, so test REPACK on a staging copy of your data first, check free disk, confirm every target table has a primary key, and size max_repack_replication_slots before you need it, since changing it requires a restart. If the docs match your workload, the days of scheduling downtime for VACUUM FULL are numbered.