Postgres 19 adds REPACK as a first-class SQL command. Its CONCURRENTLY mode rebuilds a bloated table while it stays readable and writable. The ACCESS EXCLUSIVE lock is held only briefly at the very end. Craig Kerstiens at Snowflake calls it his favorite Postgres 19 feature and "the one that's going to matter most if you're running a production database." I agree it's the upgrade reason for anyone fighting bloat.
What you used to do about bloat
VACUUM marks dead tuples reusable, but it doesn't shrink the file or return space to the OS. To get disk back you had to rebuild the table. The options before 19:
- VACUUM FULL: built in, but locks the table for the whole run.
- dump/restore: fail over to a clean snapshot.
- pg_repack: an extension plus an external client binary. It uses triggers and a log table to capture writes, builds a shadow table, then swaps.
- pg_squeeze: a background-worker extension using logical decoding. It still needs
wal_level = logical, a replication slot, and an entry inshared_preload_libraries.
The article describes pg_squeeze as "the prototype" for the built-in version. REPACK CONCURRENTLY inherits the logical decoding approach but needs no extension, no shared_preload_libraries and no configuration tables.
The three forms
-- Like VACUUM FULL: ACCESS EXCLUSIVE lock
REPACK my_table;
-- Online, brief lock at the end
REPACK (CONCURRENTLY) my_table;
-- Online, and reorder rows by an index
REPACK (CONCURRENTLY) my_table USING INDEX my_index;
VERBOSE prints progress and ANALYZE refreshes planner statistics afterwards. USING INDEX replaces the old CLUSTER workflow, which also took a lock. The article's example is range queries on created_at: after REPACK orders USING INDEX idx_orders_created_at, a date range scan reads pages in sequence. The catch is that later updates are not automatically clustered, so measure on your own queries.
What the concurrency costs
The author's test used a 493 MB table with 1M live rows left after deleting 1M of 2M. The system was constrained: 1 CPU, 512 MB RAM, 4 MB maintenance_work_mem.
| Method | Time | Result | Table accessible? |
|---|---|---|---|
| VACUUM FULL | 2.7s | 228 MB | No |
| REPACK | 5.9s | 228 MB | No |
| REPACK (CONCURRENTLY) | 9.4s | 228 MB | Yes, brief lock at end |
The concurrent mode is slower because it does logical decoding and WAL replay on top of the rebuild. The author puts the overhead at roughly 1.5x to 2x basic REPACK, and says the ratio should hold on faster hardware. For 100+ GB tables he expects minutes to tens of minutes.
Restrictions to check before you plan around it
This is where I'd spend my time. REPACK CONCURRENTLY does not work on every table:
- You need 2x the space for the table and its indexes while two copies exist.
- The table must be WAL-logged, not
UNLOGGED. - The table cannot be partitioned.
- The table must have an indexable primary key.
- No system catalogs or TOAST tables.
- Only one concurrent REPACK per database at a time.
- Non-heap access methods are not supported.
The partitioned-table and primary-key limits will rule out some of your worst offenders. Audit for those first.
The MVCC caveat
The source is explicit that REPACK CONCURRENTLY is not fully MVCC-safe. During the final swap, a transaction using a snapshot taken before the repack began can see the table as empty. The author says pg_repack had the same limitation and that it is documented in the Postgres 19 release notes. If you run long transactions with strict consistency requirements, read that section before scheduling anything.
Monitoring and when to use what
Progress is exposed in pg_stat_progress_repack, with phases from initializing through seq scanning heap, catch-up (CONCURRENTLY only), swapping relation files and rebuilding index:
SELECT pid, relid::regclass AS table_name, command, phase,
heap_blks_scanned, heap_blks_total, index_rebuild_count
FROM pg_stat_progress_repack;
The article's rule of thumb: autovacuum for routine maintenance, tuning autovacuum_vacuum_scale_factor for large busy tables, REPACK (CONCURRENTLY) when you need space back with the table online, and plain REPACK or VACUUM FULL if you have a maintenance window. If only an index is bloated, use REINDEX CONCURRENTLY.
What I would do
Run the article's pg_stat_user_tables dead-tuple query to find your worst tables. Then check each against the restriction list. Tables that pass can move off pg_repack or pg_squeeze once you're on 19. Keep the extension for the ones that don't. The source says REPACK removes the need for pg_repack "in most cases", not all.
One timing note: the article is dated Nov 2 and described Postgres 19 as at RC1, with GA expected in November or December 2026. Check the current release status before you plan a production upgrade. Until then, try it on a beta instance against your most bloated tables, as the author suggests.
Sources:
- Craig Kerstiens, Snowflake: https://www.snowflake.com/en/blog/engineering/postgres-19-repack-table-bloat/
- PostgreSQL 19 release notes (referenced by the article): https://www.postgresql.org/docs/release/19.0/