A couple of days ago I wrote that Postgres 19 is late and that three big features were dropped along the way. Fortunately, among what does make it there’s something many people who run Postgres have been waiting for for years: the REPACK command, which Craig Kerstiens explains with benchmarks on Snowflake’s engineering blog.
The problem: space that never comes back
I covered this in the article about DELETE in Postgres at scale: because of how MVCC works, every UPDATE and every DELETE leaves a dead tuple behind. Autovacuum cleans them up and makes that gap available for new rows, but it doesn’t shrink the file. If a 500 MB table loses half its rows, it still takes 500 MB on disk. That’s the well-known bloat.
To give that space back to the operating system you have to rewrite the table, and until now every option had a catch:
| Option | Catch |
|---|---|
VACUUM FULL | Locks the table completely (no reads or writes) for the whole run |
CLUSTER | Same lock, although it also orders the rows by an index |
pg_repack | Separate extension with its own client binary; captures changes with triggers, which add write overhead |
pg_squeeze | Extension that needs shared_preload_libraries, wal_level = logical and a replication slot |
On a small table, the VACUUM FULL lock doesn’t matter. On a production table with hundreds of gigabytes, it means downtime, so the usual approach was to reach for pg_repack and cross your fingers.
What REPACK brings
REPACK is a regular SQL command, no extensions needed, with three forms:
-- Rewrites the table with an exclusive lock, like VACUUM FULL
REPACK my_table;
-- Rewrites the table while it stays available for reads and writes
REPACK (CONCURRENTLY) my_table;
-- Same, but also physically orders the rows by an index
REPACK (CONCURRENTLY) my_table USING INDEX my_index;
The interesting one is the second. With CONCURRENTLY, Postgres copies the table into a new one while the application keeps working, collects the changes made during the copy through logical decoding (not triggers, as pg_repack does) and only at the end takes a brief lock to apply what’s pending and swap one table for the other. According to the article, much of this work comes from CYBERTEC, the team behind pg_squeeze, so it’s essentially that same idea brought into Postgres core.
You can add options such as VERBOSE and ANALYZE, and progress is tracked in the pg_stat_progress_repack view:
SELECT pid, relid::regclass, phase,
heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_repack;
The numbers
Kerstiens tests with a 2 million row table and deletes half of it. All three options leave the table at the same size, but at different costs:
| Method | Time | Final size | Table available? |
|---|---|---|---|
VACUUM FULL | 2.7 s | 228 MB (from 493 MB) | No |
REPACK | 5.9 s | 228 MB | No |
REPACK (CONCURRENTLY) | 9.4 s | 228 MB | Yes, except for a brief lock at the end |
The concurrent version is the slowest, which makes sense since it does extra work to capture and replay changes. But what matters in production isn’t how long it takes, it’s how long the table is unavailable, and there it wins by a wide margin. In the USING INDEX test, a 618 MB table went down to 320 MB in 15.2 seconds.
The limitations
It doesn’t work for everything. To use CONCURRENTLY, the table must:
- Have an indexable primary key.
- Not be partitioned.
- Be WAL-logged (it can’t be
UNLOGGED) and use the regular heap access method. - Not be a system catalog or a TOAST table.
It also needs roughly twice the space of the table and its indexes while it runs, and only one concurrent REPACK can run per database at a time. The space part matters: if you’re doing this precisely because you’re running out of disk, you might not have the headroom to do it.
And there’s one detail worth keeping firmly in mind: REPACK CONCURRENTLY is not fully MVCC-safe. A transaction that took its snapshot before the process started can see the table as empty after the swap. It’s the same limitation pg_repack already had and it’s documented in the release notes, but on a system with long-running transactions (reports, exports, slow logical replicas) it’s worth scheduling it when none are running.
My takeaway
REPACK doesn’t replace autovacuum, which is still what keeps a table healthy day to day. It’s a tool for specific cases: reclaiming disk space after a mass delete, or physically reordering a table so range queries read fewer pages.
What I like is that it turns into a standard command something that until now depended on external extensions, with their installation, versions and permissions. It’s one of those unglamorous improvements that never make a release’s headlines but end up being used a lot. If you already have pg_repack working there’s no rush to switch, but on new Postgres 19 installations you won’t need to add it.
Source: Postgres 19 REPACK: Faster, Built-In Table Bloat Cleanup, Craig Kerstiens, Snowflake.
