Postgres 19's REPACK rewrites a bloated table without locking out your writers

PostgreSQL 19 introduces a new command called REPACK, which merges two previous commands: VACUUM FULL and CLUSTER. REPACK (CONCURRENTLY) allows the table to stay readable and writable while rewriting the table. This command is currently in beta, so it's not yet production-ready. The main benefit is that it reclaims space without locking out writers, unlike VACUUM FULL, which takes an ACCESS EXCLUSIVE lock.

REPACK (CONCURRENTLY) also builds a new copy of the table while the original one continues accepting writes, capturing all changes using logical decoding and replaying them before swapping the files. This process is slower than VACUUM FULL, but the trade-off is that it keeps the table available for use. The slowest write during a concurrent run was 125 milliseconds, which is still a significant file swap operation.

The pg_stat_progress_repack view provides real-time updates on the progress of the rewrite, helping users know whether it's nearly done or just starting. A notable feature of REPACK (CONCURRENTLY) is that it can handle a high write rate, as demonstrated by a workload where 16 connections committed 1,120 updates per second during the rewrite.

The command even cleans up cleanly, even if it's stopped mid-process. However, it's essential to note that REPACK (CONCURRENTLY) still requires ACCESS EXCLUSIVE for the final file swap, which means it might wait if another session holds the table. In such cases, queries running concurrently with the repack could be delayed for a significant amount of time, as seen when an ordinary SELECT count(*) query took five seconds to return while waiting for the ACCESS EXCLUSIVE lock held by the repack.