Darshan Turakhia reported that a high-traffic notifications table accumulated 11.2 million dead rows. In that case, the autovacuum threshold and I/O budget let new dead tuples accumulate faster than vacuum could clear them. The table plus indexes occupied 41 GB before pg_repack reduced them to 9.8 GB. More generally, ordinary VACUUM makes dead-tuple space reusable inside PostgreSQL but usually does not shrink the relation file.
PostgreSQL 19 introduces REPACK as a core SQL command to solve this exact gap.
TL;DR
- PostgreSQL 19 introduces REPACK, a built-in SQL command that reclaims disk space caused by table bloat by rewriting tables and rebuilding indexes. It replaces VACUUM FULL and CLUSTER as the primary table-rewrite command.
- VACUUM vs. REPACK: Regular VACUUM cleans up dead tuples and makes space reusable but generally doesn't reduce table file size. REPACK physically shrinks bloated tables and returns unused disk space to the operating system.
- REPACK supports two execution modes: Standard REPACK holds an ACCESS EXCLUSIVE lock throughout the operation, blocking reads and writes. REPACK (CONCURRENTLY) allows normal database activity during most of the rewrite but requires an exclusive lock for the final swap.
- Concurrent REPACK has important limitations: It requires a suitable primary key or replica identity, sufficient disk and WAL capacity, and careful handling of long-running transactions. It is not MVCC-safe, meaning older snapshots may temporarily see an empty table after the rewrite.
- Measure PostgreSQL table bloat before running REPACK. Use pgstattuple to analyze heap free space and inspect index sizes separately. Consider REPACK when substantial unused heap space won't be reused by future writes.
- Choose the right PostgreSQL bloat removal method: Tune autovacuum for routine dead-tuple cleanup, use REINDEX CONCURRENTLY for index-only bloat, and use REPACK when the table heap needs to shrink.
- For PostgreSQL 18 and earlier, extensions such as pg_repack and pg_squeeze provide alternatives for reclaiming table bloat. On PostgreSQL 19, pg_repack may still be preferable for its production history and documented cleanup procedures, subject to compatibility verification.
What is PostgreSQL 19 REPACK?
REPACK is a core SQL command introduced in PostgreSQL 19 that rewrites a table and its indexes into compact new files, reclaiming physical disk space that ordinary vacuum leaves allocated. In PostgreSQL 19, REPACK replaces VACUUM FULL and CLUSTER for table-rewrite work. Both older commands remain for compatibility.
REPACK writes a fresh copy of the table and rebuilds every index. With USING INDEX, it also physically reorders rows by that index. Clustering can reduce heap-page reads for index scans that retrieve ranges or multiple nearby rows, but it does not inherently make a full sequential scan read fewer heap pages. Check pg_stats.correlation for a column used in range predicates. A value near 0 suggests reordering may help. The ordering is a one-time operation and is not maintained by later writes. CLUSTER in PostgreSQL 19 is documented as equivalent to REPACK ... USING INDEX.
The command requires the MAINTAIN privilege, refuses to process a table with an invalid index, and supports VERBOSE, ANALYZE, and CONCURRENTLY. ANALYZE collects planner statistics after the rewrite but currently supports only a single nonpartitioned target table. Neither REPACK nor VACUUM FULL has a TABLESPACE option. Use ALTER TABLE ... SET TABLESPACE to move a table, and move its indexes separately if needed.
Why does PostgreSQL table bloat happen?
PostgreSQL's MVCC design keeps old row versions visible to concurrent transactions. An UPDATE or DELETE does not erase the old tuple immediately because other transactions may still need to see it. Once no transaction references those dead versions, VACUUM removes them and marks their space available for future tuples within the same relation. Vacuum runs alongside ordinary reads and writes.
Autovacuum uses a default update/delete vacuum threshold of 50 tuples plus 20% of the estimated live row count, capped at 100 million tuples. Inserts and wraparound protection can also trigger vacuuming. On a large, high-churn table, that threshold can allow millions of dead tuples before work starts. Turakhia's notifications table accumulated 11.2 million dead rows because the default 0.2 scale factor could not keep pace with mention-driven churn. Per-table tuning can reduce this lag but does not itself shrink the file.
VACUUM can truncate fully empty pages at the end of a table file, returning that trailing space to the OS, but this is a narrow case and may require a brief ACCESS EXCLUSIVE lock. Middle-of-file free space stays allocated. To reclaim it, a table rewrite is required.
How does PostgreSQL 19 REPACK work?
REPACK creates a new physical copy of the table and its indexes, then replaces the old files. The concurrent form uses logical decoding to capture changes made during the copy and applies them before swapping.
Nonconcurrent (default) mode
PostgreSQL acquires ACCESS EXCLUSIVE at the start and holds it for the entire rewrite, blocking all reads and writes.
Concurrent mode
REPACK (CONCURRENTLY) holds SHARE UPDATE EXCLUSIVE during the main copy, allowing ordinary reads and writes. PostgreSQL uses logical decoding to record concurrent changes. Logical decoding reads the write-ahead log and reconstructs row-level changes as a stream of inserts, updates, and deletes, which REPACK applies to the new table copy. It then requests ACCESS EXCLUSIVE, applies remaining captured changes, and swaps the files.
The final ACCESS EXCLUSIVE phase has no guaranteed maximum duration. Conflicting DDL or long-held locks can delay or fail the swap. The concurrent form cannot run inside a transaction block or from a function, procedure, or DO block. Concurrent REPACK advances the decoding slot to allow old WAL recycling during the copy, making WAL retention and slot capacity legitimate preflight concerns.
MVCC snapshot warning
Concurrent REPACK is not MVCC-safe: after the rewrite commits, a transaction using an older snapshot can see the table as empty. Before running REPACK (CONCURRENTLY) in production, query pg_stat_activity for long-running transactions and check for active logical-replication consumers or pg_dump sessions holding old snapshots.
Restrictions and preflight
The concurrent form requires a nondeferrable primary key or an eligible index-based replica identity. Replica identity tells PostgreSQL how to identify rows during logical decoding. By default, PostgreSQL uses the primary key. Otherwise, designate an eligible unique index with ALTER TABLE ... REPLICA IDENTITY USING INDEX.
Concurrent REPACK cannot target the partitioned parent. Run it on eligible individual partitions. Nonconcurrent REPACK can process a partitioned table through its partitions. The concurrent form is not allowed for unlogged tables, materialized views, or system catalogs, and is restricted to the heap access method. Nonconcurrent REPACK can process materialized views.
Before running, verify that disk capacity can hold the old table, new table, rebuilt indexes, and a possible sort file at the same time. Concurrent REPACK might fail to complete if another transaction runs DDL on the target table during the operation. Monitor the logical-decoding slot for WAL lag and archive/standby capacity. Drop or rebuild any invalid indexes first. pg_stat_progress_repack provides phase and progress information. For large tables, monitor the REPACK backend's backend_xid in pg_stat_activity to avoid holding back the transaction-ID horizon.
Alternatives to PostgreSQL 19 REPACK for table bloat
The right tool depends on which object is bloated, whether a maintenance window is available, and how much production history the tool has.
| Method | Object affected | Lock behavior | Key limits |
|---|---|---|---|
| VACUUM / autovacuum | Heap and indexes (dead tuples), without returning middle-of-file space to OS | Runs with ordinary DML. Trailing-page truncation may take brief ACCESS EXCLUSIVE | Routine maintenance only. Shrinks the heap only by truncating fully empty trailing pages |
| VACUUM FULL | Heap and indexes (full rewrite), returning space to OS | ACCESS EXCLUSIVE for entire rewrite | Blocks all access and needs disk for both copies. The FULL option is deprecated in PostgreSQL 19 but remains functional. REPACK is the intended replacement |
| REPACK (nonconcurrent) | Heap and indexes (full rewrite), returning space to OS | ACCESS EXCLUSIVE for entire rewrite | Same blocking as VACUUM FULL and equivalent to CLUSTER with USING INDEX. PG 19+ only |
| REPACK (CONCURRENTLY) | Heap and indexes (full rewrite), returning space to OS | SHARE UPDATE EXCLUSIVE during copy, then ACCESS EXCLUSIVE for final swap | Not MVCC-safe. Requires a nondeferrable PK or index-based replica identity, wal_level >= replica, and an available REPACK replication slot. Heap AM only. Not for unlogged or partitioned parents. Fails above about 104 million concurrent updates/deletes in one run. PG 19+ only |
| REINDEX CONCURRENTLY | Indexes only, returning index space to OS | Allows reads and writes. Waits for relevant transactions | Does not touch the heap. Not for system catalogs or exclusion-constraint indexes. PG 12+ |
| pg_repack extension | Heap and indexes, or indexes only, returning space to OS | Short ACCESS EXCLUSIVE at setup and swap. Copy-phase lock depends on the package version (see below) | Full-table repack requires a PK or unique total index on non-null columns. Trigger/log-table mechanism adds writes. pg_repack client binary and database extension versions must match. Has an old-snapshot visibility caveat |
| pg_squeeze extension | Heap and indexes, returning space to OS | Short ACCESS EXCLUSIVE for final swap | Requires a primary key or replica identity, shared_preload_libraries configuration, wal_level = logical, replication-slot capacity, and a restart. Also warns about MVCC snapshot visibility during the swap |
pg_repack
pg_repack creates a shadow table, uses triggers and a log table to capture concurrent changes, copies rows, applies logged changes, and swaps. Unlike core REPACK, pg_repack can rebuild individual indexes without rewriting the heap, offering an alternative to REINDEX CONCURRENTLY for index-only bloat. Upstream documentation describes SHARE UPDATE EXCLUSIVE during the copy phase, while AWS RDS documentation describes ACCESS SHARE. Verify the lock level against the deployed package version and provider.
pg_repack has years of production use and documented cleanup procedures for interrupted runs. A DBA Stack Exchange report describes a 3 TB table repack with roughly 30 minutes of standby replication lag while inserts continued on PostgreSQL 9.5. Core REPACK (CONCURRENTLY) is new in PostgreSQL 19. Its cancellation and cleanup behavior is not yet fully documented. Operators may prefer the extension until the core command accumulates broader production experience. For pg_repack 1.5.3, the documented PostgreSQL versions end at 18. Verify PostgreSQL 19 compatibility.
Verify that server extension and client binary versions match. On managed services, check the provider's extension catalog and privilege model.
pg_squeeze
pg_squeeze uses a transient table and logical decoding instead of triggers. It can schedule compaction through background workers but requires shared_preload_libraries, wal_level = logical, and a PostgreSQL restart, a significantly heavier configuration prerequisite than core REPACK. Its documentation also warns about MVCC snapshot visibility during the swap, similar to core REPACK (CONCURRENTLY).
REINDEX CONCURRENTLY
REINDEX CONCURRENTLY rebuilds indexes while allowing reads and writes. It does not rewrite or shrink the heap. Use it when pgstatindex shows low leaf density in an affected B-tree index, or when index-size trends and workload evidence point to index bloat while the heap remains stable.
Choosing the right approach
Measure first, then match the tool to the damaged object and the available maintenance window.
Measure bloat before deciding
Use pgstattuple on the heap to inspect table_len, tuple_len, and free_space after vacuum. Check index size with pg_indexes_size and table-plus-TOAST size with pg_table_size before choosing a table rewrite. pg_stat_user_tables.n_dead_tup is an estimated row count, not a live-data byte estimate. Substantial heap free space that the workload will not reuse can justify REPACK.
Decision framework
- Dead tuples accumulating, file size close to live-data estimate? Tune autovacuum: lower autovacuum_vacuum_scale_factor on high-churn tables, increase autovacuum_max_workers, and investigate long-running transactions that hold back cleanup. If the table's workload will reuse free space through new inserts or updates, a rewrite wastes I/O and lock time.
- Index bloat, heap stable? Run REINDEX CONCURRENTLY on the affected indexes. REPACK always rewrites the entire table and all its indexes. It cannot target a single bloated index without rewriting the heap.
- Heap file significantly larger than live data, and workload will not fill the gap? Use REPACK. Choose nonconcurrent mode with a maintenance window, or concurrent mode after verifying identity, snapshot safety, disk, and WAL capacity.
- PostgreSQL 18 or earlier? Use pg_repack or pg_squeeze after verifying extension version, provider support, and configuration requirements. On PostgreSQL 19, choose pg_repack when its documented cleanup procedures and longer production history are decisive, after verifying PostgreSQL 19 compatibility.
Frequently asked questions
Does REPACK replace VACUUM?
No. VACUUM removes dead tuples and prevents unbounded bloat accumulation as routine maintenance. REPACK is a table-rewrite operation for when the heap file itself must shrink. Run vacuum regularly. Plan a rewrite only when needed.
Can REPACK (CONCURRENTLY) run on a partitioned table?
Not on the partitioned parent. The concurrent form can process eligible individual partitions. Nonconcurrent REPACK can process a partitioned table by working through its partitions.
Does REPACK (CONCURRENTLY) require wal_level = logical?
REPACK (CONCURRENTLY) uses logical decoding. In PostgreSQL 19, it requires wal_level = replica or higher, plus capacity under max_repack_replication_slots. It does not require wal_level = logical. pg_squeeze does.
What happens if a long-running transaction is open during concurrent REPACK?
After the rewrite commits, a transaction whose snapshot predates the rewrite may see the table as empty. Check pg_stat_activity for old snapshots before starting.
What is the difference between REPACK and VACUUM FULL?
In PostgreSQL 19, REPACK replaces VACUUM FULL and CLUSTER as the intended table-rewrite command. While nonconcurrent REPACK behaves similarly to VACUUM FULL by holding an ACCESS EXCLUSIVE lock for the entire rewrite, REPACK (CONCURRENTLY) allows ordinary reads and writes during most of the copy.
How do I check for table bloat before using REPACK?
Inspect heap free space with pgstattuple after vacuum, and check index and TOAST sizes separately. pg_total_relation_size includes all three, while pg_stat_user_tables reports estimated row counts rather than live-data bytes. A substantial heap gap that future writes will not reuse is a reason to consider a table rewrite.


