VACUUM FULL vs pg_repack: Shrink a Bloated PostgreSQL Table Without Downtime

VACUUM FULL vs pg_repack is the choice you face when a PostgreSQL table has grown far beyond the data it holds and you need the disk space back. Both rewrite the table into a compact new file. VACUUM FULL is built in but locks the table for the entire rewrite. pg_repack is an extension that does the same job while keeping the table available for reads and writes most of the time. And PostgreSQL 19, currently at release candidate stage, adds a built-in REPACK (CONCURRENTLY) that works on the same principle.

First: do you really need a rewrite?

The VACUUM documentation is clear about the difference. Plain VACUUM reclaims space and makes it available for reuse within the same table, without an exclusive lock, but in most cases does not return it to the operating system. The exception, per the routine vacuuming chapter, is when pages at the very end of the table become entirely free and an exclusive lock can be obtained easily: then those pages are cut off.

So a table that is 60% empty is not automatically a problem. If it will fill up again with new rows, reusable space is fine. The same chapter says there is not much point in shrinking a table that will just grow again, and that moderately frequent standard VACUUM runs are better than infrequent VACUUM FULL runs. A rewrite makes sense when:

• you deleted or updated most of a table once (an archive purge, a backfill gone wrong) and it will not regrow;
• the disk is genuinely running out and the free space inside the table is the fastest thing to reclaim;
• sequential scans on the table are slow because they read mostly empty pages.

If bloat keeps coming back, a rewrite only buys time. The cause is almost always autovacuum not keeping up or a long transaction holding back cleanup, covered in our autovacuum tuning guide.

Measure before you decide

Start with sizes and dead-tuple counts from the statistics views:

SELECT relname,
       pg_size_pretty(pg_table_size(relid))   AS table_size,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
       n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

n_dead_tup is an estimate and says nothing about free space left behind by rows that vacuum has already cleaned. For a real number, use the pgstattuple extension. pgstattuple_approx is the documented faster alternative to pgstattuple that returns approximate results:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT table_len, approx_tuple_percent, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('public.orders'::regclass);

A high approx_free_percent on a large table that is not going to regrow is your candidate. By default these functions can only be executed by superusers and members of pg_stat_scan_tables. Even the approximate version reads part of the table, so run it outside peak hours on very large tables.

VACUUM FULL: simple, built in, and blocking

Per the documentation, VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. The cost:

• An ACCESS EXCLUSIVE lock for the whole run. That lock conflicts with everything, including plain SELECT. On a large table this means minutes or hours of the application waiting.
• Extra disk space. The new copy is written before the old one is released, so you temporarily need roughly the size of the table again, plus its indexes, which are rebuilt too. This is the classic trap: running VACUUM FULL because the disk is nearly full, then running out of disk halfway through.
• No parallelism. The PARALLEL option cannot be combined with FULL.

VACUUM (FULL, VERBOSE, ANALYZE) public.orders;

-- in another session
SELECT pid, command, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_cluster;

The documentation notes that backends running VACUUM FULL report progress in pg_stat_progress_cluster, not in pg_stat_progress_vacuum. Autovacuum, by the way, never runs VACUUM FULL.

VACUUM FULL is the right tool for small tables where a short lock does not matter, for maintenance windows, and when you cannot install extensions. For anything large and busy, look at the online options.

pg_repack: the online rewrite

pg_repack is an open-source extension that removes bloat from tables and indexes. Its documentation lists support for PostgreSQL 9.5 and newer, including 18. It can do an online CLUSTER (rows ordered by an index) or, with --no-order, an online VACUUM FULL. A full-table repack works like this:

1. Create a log table and a trigger that records every INSERT, UPDATE and DELETE on the original table.
2. Create a new table with all the rows from the old one and build its indexes.
3. Apply the changes accumulated in the log table.
4. Swap the tables, including indexes and TOAST, in the system catalogs, and drop the original.

An ACCESS EXCLUSIVE lock is held only briefly during setup and during the final swap-and-drop. The rest of the time pg_repack holds a SHARE UPDATE EXCLUSIVE lock (since version 1.5.3; earlier versions used ACCESS SHARE), so normal reads and writes continue.

-- once per database
CREATE EXTENSION pg_repack;

# see what would be processed, without doing anything
pg_repack --dry-run --no-order --table=public.orders mydb

# online "VACUUM FULL" of one table, give up instead of cancelling other queries
pg_repack --no-order --table=public.orders --wait-timeout=30 --no-kill-backend mydb

What pg_repack needs

• A primary key, or at least a unique index on a NOT NULL column. Tables without one cannot be repacked.
• Disk space about twice the size of the target tables and their indexes, according to its documentation. That is more than VACUUM FULL needs, because of the log table and the copy.
• Superuser or table owner. Owners must pass --no-superuser-check.
• Matching versions of the pg_repack client binary and the extension installed in the database; a mismatch is a common first error.

Where it can hurt

• The short locks still have to be acquired. If a long query holds a conflicting lock, pg_repack waits --wait-timeout seconds (default 60), then cancels the conflicting queries, and after twice the timeout terminates the remaining backends. On production we prefer --no-kill-backend, which skips the table instead, and running it at a quiet time.
• No DDL on the table while it runs. The documentation says DDL is blocked during a full-table repack.
• Write overhead. Every change during the run goes through the trigger into the log table. On a very write-heavy table this adds load and the final catch-up can take a while.
• Leftovers after an interruption. If a run is killed, it can leave a repack_trigger or a temporary index behind. The documentation explains how to remove them; check for them before the next attempt.

If only indexes are bloated, you do not need a table rewrite at all. pg_repack has --only-indexes, and since PostgreSQL 12 the built-in REINDEX CONCURRENTLY does the job without an extension.

PostgreSQL 19: REPACK and REPACK (CONCURRENTLY)

The PostgreSQL 19 release notes add a REPACK command that replaces VACUUM FULL and CLUSTER; the old commands are kept for compatibility, and the PostgreSQL 19 VACUUM page marks the FULL option as deprecated. More importantly, REPACK (CONCURRENTLY) rebuilds a table without holding an access-exclusive lock for the whole operation. As of today PostgreSQL 19 is at release candidate stage (RC1 was announced on 15 October 2026), so treat this as what is coming, not what you run now.

-- PostgreSQL 19
REPACK (CONCURRENTLY, VERBOSE, ANALYZE) public.orders;

SELECT * FROM pg_stat_progress_repack;

According to the REPACK documentation, the concurrent mode does most of the work under SHARE UPDATE EXCLUSIVE, captures concurrent changes with logical decoding, and takes ACCESS EXCLUSIVE only for the final swap. The documented limits are worth reading before you plan on it:

• It is not MVCC-safe: after it commits, transactions using a snapshot taken before the repack started will see the table as empty.
• It cannot be used on unlogged or partitioned tables, tables without a primary key or index-based replica identity, system catalogs, inside a transaction block, or when max_repack_replication_slots (default 5, set at server start) has no free slot.
• It fails if more than about 104 million rows are updated or deleted concurrently during the run, and it may fail if other transactions run DDL on the table.
• For wraparound purposes the whole run counts as one long transaction, so on very large tables it holds back cleanup elsewhere; the documentation suggests watching pg_stat_activity.backend_xid.

Which one to use

• Small table, or a maintenance window: VACUUM FULL. Nothing to install, nothing to clean up.
• Large, busy table on PostgreSQL 18 or older: pg_repack with --no-order, a dry run first, --no-kill-backend, and enough free disk.
• PostgreSQL 19 once you are on it: test REPACK (CONCURRENTLY) against your workload, especially the MVCC caveat for long-running reports.
• Only indexes are bloated: REINDEX CONCURRENTLY, no table rewrite needed.
• Bloat keeps coming back: none of the above. Fix autovacuum and long transactions first.