PostgreSQL 19 introduces the native REPACK command, combining the capabilities of VACUUM FULL and CLUSTER while supporting CONCURRENTLY for reduced downtime.
It reorganizes tables, rebuilds indexes, and applies concurrent changes before a final metadata swap, helping reclaim bloat with minimal disruption.
This blog explores REPACK internals, compares it with VACUUM FULL and pg_repack, demonstrates performance testing, and covers monitoring, limitations, and production best practices.
PostgreSQL 18 vs 19
Until PostgreSQL 18 did not have the built-in REPACK & REPACK[CONCURRENTLY]command we have pg_repack as extension tool. In PostgreSQL 18 and older versions, table bloat cleanup without taking heavy exclusive locks relies on the pg_repack extension.
It requires careful setup for edge cases like foreign keys, triggers, and logical replication slots.
pg_repack creates a side-log table and temporary triggers on the target table. It bulk-copies existing live rows into a new table file, replays changes logged by the triggers, and briefly swaps catalog pointers (pg_class) to cut over.
The problem is that it pg_repack is an external extension. It requires extra installation, management, and permissions (SUPERUSER or specific extension privileges). Its reliance on triggers and side tables creates additional write amplification and WAL overhead.
To overcome the above problems with the pg_repack extension, PostgreSQL introduced a built-in command in 19 th version.
For physical table maintenance, PostgreSQL provides:
- VACUUM — removes dead tuples and makes space reusable.
- VACUUM FULL — physically rewrites and compacts the table.
- CLUSTER — physically reorganizes a table according to an index.
VACUUM FULL and CLUSTER involve table rewriting and can require strong locking.
For large, frequently accessed production tables, long blocking during maintenance can be a concern. To address this problem, PostgreSQL 19 released a native built-in command called REPACK.
We have the problem that PostgreSQL’s MVCC creates old row versions during UPDATE and DELETE, which can create as dead tuples and cause table bloat. Regular VACUUM makes this space reusable but generally does not return the space to the os. VACUUM FULL can reclaim the space by rewriting the table, but it requires an ACCESS EXCLUSIVE lock and can block application traffic.
REPACK addresses this by rebuilding the table with only live data and reclaiming physical disk space.With CONCURRENTLY, the reorganization can proceed while normal reads and writes continue, followed by a short final synchronization/swap phase.
REPACK can also rebuild indexes and optionally organize table data according to an index to improve storage efficiency and query performance.
REPACK[CONCURRENTLY] a method used in PostgreSQL to rebuild tables and indexes to reclaim physical disk space (bloat) without holding an exclusive lock on the table.
Unlike standard VACUUM FULL , which locks the table completely and blocks all concurrent reads and writes, a concurrent repack allows live SELECT, INSERT, UPDATE, and DELETE queries to run normally while the rebuild happens in the background.
Comparison Matrix pg_repack vs Repack
| Feature | REPACK | pg_repack |
| What is it? | Built-in PostgreSQL SQL command | External PostgreSQL extension/tool |
| Installation | No separate installation | Must install pg_repack package/extension |
| Command | REPACK … | pg_repack … |
| Example | REPACK employees; | pg_repack -d mydb -t employees |
| Runs from | psql / SQL | Linux/OS command line |
| PostgreSQL integration | Native PostgreSQL functionality | Extension + external client utility |
| Purpose | Reorganize/repack tables | Reorganize/repack tables and indexes with minimal locking |
| Concurrent option | REPACK CONCURRENTLY employees; | pg_repack is designed around online/concurrent operation |
| Progress | pg_stat_progress_repack | pg_stat_progress pg_repack-specific monitoring depending on version |
| Configuration | PostgreSQL SQL command/options | Command-line options |
| Best for | PostgreSQL’s native repacking feature | Traditional pg_repack extension/tool |
What’s New in PostgreSQL 19
- New built-in REPACK command — PostgreSQL 19 introduces REPACK as a native SQL command for physically rebuilding tables.
- Table bloat reduction — REPACK can rebuild a bloated table and reclaim unused physical space.
- REPACK CONCURRENTLY — A new concurrent option allows normal reads and writes to continue during most of the table-rewrite operation.
- Reduced application blocking — Unlike traditional table-rewrite approaches, the concurrent operation minimizes the period during which the application is blocked.
- Physical table reorganization — REPACK can reorganize the physical layout of a table.
- REPACK USING INDEX — Allows the table to be physically organized according to an existing index, similar to the physical-ordering use case of CLUSTER.
- Progress monitoring — PostgreSQL 19 provides pg_stat_progress_repack to monitor a running REPACK operation.
- Native PostgreSQL feature — Previously, online table repacking was commonly associated with external tools such as pg_repack; PostgreSQL 19 brings REPACK into the PostgreSQL server itself.
- Production-oriented maintenance — The main goal is to make large-table physical maintenance possible with less disruption to running workloads.
REPACK employees USING INDEX employees_ind;
REPACK [CONCURRENTLY]
REPACK (CONCURRENTLY) employees USING INDEX;
Use case:
Use REPACK CONCURRENTLY when a large production table has significant bloat and needs a physical rewrite, but taking the table offline for a long maintenance window is not acceptable.
Example: A 50 GB table grows to 70 GB due to bloat; REPACK can rebuild it and recover unused space.
Who benefits?
- DBAs: Manage and reduce database bloat.
- DevOps/SRE: Perform maintenance with minimal downtime.
- Developers: Can benefit from improved query performance.
- Application teams: Better availability during maintenance.
Caveats
Known limitations/edge cases
- Requires additional disk space while rebuilding the table and indexes.
- Large tables can take significant time to repack.
- Heavy workload can increase I/O and CPU usage during REPACK.
- Some objects, such as certain unsupported table types/configurations, may not be eligible for repacking.
REPACK CONCURRENTLYis not MVCC-safe,REPACK CONCURRENTLYcan affect old transactions/snapshots. If a transaction started beforeREPACK CONCURRENTLYand had not accessed the table, it may see the table as empty after the REPACK finishes. The table itself is not corrupted, but the transaction may see an inconsistent view between this table and other tables.- Hot Standby and
REPEATABLE READ,Hot Standby does not supportSERIALIZABLEtransactions. The highest isolation level supported on a standby isREPEATABLE READ.During WAL replay, aREPEATABLE READquery on the standby can temporarily see a state that doesn’t exactly match any single state that existed on the primary. - Newly Created Tables and System Catalogs,PostgreSQL handles system catalogs differently from normal table data. A transaction may see that a new table exists, but may not see the rows inside that table. In
REPEATABLE READorSERIALIZABLEdirectly querying system catalogs will not show objects created after the transaction’s snapshot.
Migration considerations
- No major PostgreSQL 19 application compatibility change is required just because you use REPACK.
- Existing pg_repack deployments should be checked for PostgreSQL 19 compatibility before upgrading.
- Ensure enough free disk space is available before running REPACK.
- Test REPACK on a non-production environment before using it on large production tables.
Interaction with PostgreSQL 19
- REPACK can be used alongside normal PostgreSQL VACUUM/ANALYZE maintenance.
- It complements VACUUM by physically rebuilding heavily bloated tables/indexes.
- It is an alternative to VACUUM FULL when reducing blocking is important.
- It does not replace normal autovacuum; autovacuum is still required for routine maintenance.
- PostgreSQL 19’s other maintenance/performance features can be used independently of REPACK.
Here is the YouTube video:
Conclusion
PostgreSQL 18 provided VACUUM FULL and CLUSTER for table-rewrite operations, but these operations can require strong locking and cause blocking on large production tables. PostgreSQL 19 introduces the built-in REPACK command and, more importantly, REPACK CONCURRENTLY. This allows PostgreSQL to rebuild and reorganize a table while normal reads and writes continue during most of the operation, reducing the operational impact of table maintenance.