REPACK and REPACK CONCURRENTLY: online debloating

Plain VACUUM makes dead space reusable inside a relation but almost never returns it to the operating system. The two classic ways to actually shrink a bloated table — VACUUM FULL and CLUSTER — both rewrite the table and its indexes under an ACCESS EXCLUSIVE lock held for the entire rewrite, which is why teams either schedule maintenance windows or install the third-party pg_repack extension. PostgreSQL 19 adds a built-in third option: REPACK, with a CONCURRENTLY mode that keeps the table readable and writable while it is rebuilt.

The lock problem with VACUUM FULL and CLUSTER

VACUUM FULL rewrites the live tuples of a table into a new file and rebuilds every index; CLUSTER does the same but sorts rows by an index. Both need ACCESS EXCLUSIVE from start to finish, so every read and write on the table queues behind them. On a large table the rewrite takes minutes to hours, and the lock wait alone can pile up enough blocked sessions to take an application down. The mechanics of measuring bloat and choosing a target table are covered in autovacuum and table bloat; this page is about the rebuild step.

REPACK: one command, two semantics

REPACK folds the VACUUM FULL and CLUSTER behaviors into a single statement:

REPACK [ ( option [, ...] ) ] [ table_and_columns [ USING INDEX [ index_name ] ] ]
REPACK [ ( option [, ...] ) ] USING INDEX

Options are VERBOSE, ANALYZE, and CONCURRENTLY:

• REPACK t; — plain rewrite that reclaims disk, the VACUUM FULL equivalent.
• REPACK t USING INDEX i; — additionally reorders rows by the index, the CLUSTER equivalent. Without an index name it uses the index previously set with ALTER TABLE ... CLUSTER ON.
• REPACK; — every table and materialized view in the current database that you hold the MAINTAIN privilege on. This form cannot run inside a transaction block and cannot be combined with CONCURRENTLY.
• REPACK (ANALYZE) t; — runs ANALYZE after the rewrite. Currently only supported for a single, non-partitioned table; the planner statistics reset after a rewrite makes this worth doing either way.

You need the MAINTAIN privilege on the table. Without CONCURRENTLY, REPACK still holds ACCESS EXCLUSIVE for the whole operation — the lock semantics are unchanged; only the command surface is unified.

How CONCURRENTLY works

With CONCURRENTLY, REPACK copies the live tuples into a new file (plus a new file for each index) while normal reads and writes continue against the old files. Changes made during the copy are captured through logical decoding and applied to the new files; only then does the command take a brief ACCESS EXCLUSIVE lock to swap old and new files and drop the old ones. The lock is typically held just for the swap — but if many changes accumulated, they must be replayed while the lock is held, so a hot table can still see a noticeable blocking window at the end.

Two behaviors from the documentation are worth internalizing:

• Rows inserted after the repack started are not ordered, even with USING INDEX — clustering is a one-time physical reorder.
• REPACK CONCURRENTLY can fail if other sessions run DDL on the table while it works. Keep migrations away from an in-flight repack.

Hard limits

CONCURRENTLY is refused in all of these cases:

• the table is UNLOGGED;
• the table is partitioned (plain REPACK on a partitioned table works — it repacks each partition — but not concurrently, and not inside a transaction block);
• the table has no primary key and no index-based replica identity — logical decoding needs a way to identify rows;
• the table is a system catalog or a TOAST table;
• REPACK runs inside a transaction block;
• max_repack_replication_slots has no free slot (see below).

Disk is the other hard limit, and it applies with or without CONCURRENTLY: the rewrite needs a temporary copy of the table plus every index, so free space of at least table size + index sizes. On the sequential-scan-and-sort path a temporary sort file can push peak usage toward double the table size plus indexes (you can force the index-scan path by setting enable_sort = off for the session). CONCURRENTLY adds more: changes made during the copy are buffered in a temporary file until they can be applied. Give the session a generous maintenance_work_mem before starting.

max_repack_replication_slots and slot isolation

CONCURRENTLY needs a replication slot for logical decoding. PostgreSQL 19 gives REPACK its own pool for this: max_repack_replication_slots (default 5, settable only at server start) reserves slots exclusively for REPACK, on top of max_replication_slots. The isolation cuts both ways:

• a repack job can never consume the slots your logical replication publications and subscriptions depend on;
• subscribers can never starve repack jobs either;
• but only 5 REPACK CONCURRENTLY operations can run at once by default — a sixth fails outright.

Operationally: serialize repack jobs (one or two at a time is usually right for I/O reasons anyway), and if you legitimately need more parallelism, raise max_repack_replication_slots with a planned restart. Do not try to "fix" a failing repack by raising max_replication_slots — that pool is not the one being exhausted.

Core REPACK vs the pg_repack extension

The built-in command and the pg_repack extension are two separate implementations that solve the same problem. Choosing between them:

On PostgreSQL 19 the core command removes the extension dependency, the version-matching burden, and the trigger-based log table. On anything older, pg_repack remains the tool — the core command does not exist there.

Runbook

A deliberate repack, end to end:

-- 1. confirm the table is actually bloated (estimates from pg_stat_user_tables are not enough)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('app.events');
-- look at dead_tuple_percent and free_percent

-- 2. confirm CONCURRENTLY is possible: replica identity must be default-with-PK or an index
SELECT relreplident FROM pg_class WHERE oid = 'app.events'::regclass;
-- 'd' (default, needs a PK) or 'i' (replica identity index) are OK; 'n' and 'f' are not

-- 3. confirm disk: free space >= table + index sizes
SELECT pg_size_pretty(pg_total_relation_size('app.events'));

-- 4. run it, in its own session
SET maintenance_work_mem = '1GB';
REPACK (CONCURRENTLY, ANALYZE, VERBOSE) app.events;

-- 5. watch progress from another session
SELECT pid, datname, relid::regclass AS relation, command, phase,
       heap_blks_scanned, heap_blks_total,
       heap_tuples_scanned, heap_tuples_inserted,
       index_rebuild_count
FROM pg_stat_progress_repack;

Phases run from initializing through the heap scan/copy (seq scanning heap / index scanning heap, sorting tuples, writing new heap), then catch-up (applying the buffered changes), swapping relation files (the brief exclusive lock), rebuilding index, and performing final cleanup. A long catch-up phase means the table is hot enough that the final lock window will be noticeable — consider rerunning in a quieter window. The same view also tracks CLUSTER and VACUUM FULL, distinguished by the command column.

If the repack fails partway through, the temporary files and the replication slot are cleaned up by the command; a failed attempt leaves the original table untouched. Retry after fixing the cause — most commonly a conflicting DDL, a missing replica identity, or an exhausted slot pool.

Prompt: plan a debloating campaign

Plan an online debloating campaign using PostgreSQL 19 REPACK CONCURRENTLY.

Context:
- Database size <GB>, the <N> most bloated tables are: <names with approx. sizes>
- Write rate on the hottest table: <rows/s>; quiet window: <time range>
- max_repack_replication_slots: 5 (default); logical replication slots currently in use: <n>
- Free disk on the data volume: <GB>

Output:
1. An ordering of the tables to repack (impact vs risk), with the exact REPACK statements.
2. Pre-flight checks as SQL: bloat confirmation (pgstattuple), replica identity, disk headroom.
3. A concurrency plan that respects max_repack_replication_slots and I/O limits.
4. Monitoring queries on pg_stat_progress_repack and abort criteria (lag, lock waits, catch-up duration).
5. A rollback/abort plan for a repack that misbehaves mid-run.

Command reference: PostgreSQL 19 REPACK and the progress reporting view. See also PostgreSQL 19 overview. Independent field reports: depesz's walkthrough and digoal on slot isolation (Chinese).