PostgreSQL REPACK - reclaiming space without the lock

An UPDATE in PostgreSQL does not change a row in place - it writes a new version and marks the old one dead. VACUUM comes behind and marks that space as reusable within the table, but it never gives the space back to the operating system. A table under steady churn therefore grows to its peak size and stays there, even when the live data is a fraction of it. That is bloat, and the only way to shrink the file is to rewrite the table - which is what REPACK does.

The problem has never been what to do but how long the lock lasts. Rewriting a table takes ACCESS EXCLUSIVE, and on a large table held for the whole run, that is an outage. Everything below is about how PostgreSQL has kept that lock short, first by extension and now in core.

The three options, and what changed in 19

The interesting row is the last one. What changed in PG 19 is not the idea - copy live tuples to a new file, capture what changed meanwhile, swap, drop the old - it is the capture mechanism. The extension did it with a trigger and a log table. The core does it with logical decoding.

Before: pg_repack on PG 15-18

pg_repack is the extension that made online repacking possible before core had it. Its mechanism is a user-visible one - it installs a trigger on the table and a shadow log table to hold the changes:

Two things follow from doing it this way. The trigger means every write pays twice during a repack - once to the table and once to the log - and it means an interrupted run can leave the trigger and log table behind, needing manual cleanup (repack_trigger “already exists” is the signature). It also needs a primary key or unique index to identify rows in the log.

Now: REPACK in PostgreSQL 19

PostgreSQL 19 - in beta at the time of writing - makes repacking a SQL command. The plain form behaves like a friendlier VACUUM FULL; CONCURRENTLY is the flag that makes it the pg_repack-style online operation, and it swaps the capture mechanism:

-- repack one table, locking it fully for the duration:
REPACK employees;

-- the same, but let reads and writes continue during the copy:
REPACK (CONCURRENTLY) employees;

-- physically reorder the rows to match an index (clustering):
REPACK (CONCURRENTLY) employees USING INDEX employees_ind;

-- every table you hold MAINTAIN on - non-concurrent, not in a transaction:
REPACK;

The reason the capture mechanism matters: a trigger sees only what it is attached to and costs write overhead on every row; logical decoding reads the WAL after the fact, so the table carries no extra write cost while it is being copied. That is the same reason the progress reporting is built in - REPACK reports to pg_stat_progress_repack rather than leaving you to watch a log table.

The catch in CONCURRENTLY

CONCURRENTLY moves the lock, it does not remove it. The ACCESS EXCLUSIVE is still needed to swap the files, and the changes that arrived while it was waiting for that lock are applied during it - so on a write-hot table the final lock can be noticeably long even though the copy ran lock-free. The exclusions are also real:

• no materialized views, UNLOGGED, partitioned (as the target), system catalogue or TOAST tables
• the table needs a primary key or index-based replica identity - same requirement as pg_repack
• heap access method only, and not inside a transaction block
• needs a free replication slot under max_repack_replication_slots, which implies wal_level = logical for the decoding to work at all
• and it is not MVCC-safe - the same caveat CREATE INDEX CONCURRENTLY carries

Add USING INDEX and the rows are not just compacted but physically reordered into index order - the clustering CLUSTER does. It is a one-time ordering; later updates are not kept clustered.

The disk and memory bill

Repacking is a copy, so it needs the room for one. Free space needed is at least the table plus its indexes; if the planner picks the sequential-scan-then-sort path it can peak at double the table plus indexes, and CONCURRENTLY adds a bit more for the buffered changes. enable_sort = off disables the sort path if that is intolerable, and maintenance_work_mem should be raised to what the operation can actually use.

The dataflow, side by side

Where it breaks

• An interrupted pg_repack leaves debris - the trigger and log table stay on the table, and the next run fails on repack_trigger already exists until you clean them up.
• CONCURRENTLY can still lose the race on a hot table. Changes that arrive while it waits for the swap lock are applied during it, so the “short” final lock stretches with write rate.
• DDL during a concurrent repack fails it. A concurrent ALTER on the target aborts the repack.
• Disk-full mid-run. A repack is a second copy of the table - on a nearly-full volume the operation itself can be what fills the disk.
• It is new. PG 19 was at Beta 4 when this was written; the command is documented and real, but treat beta behaviour as movable until the GA release.

What this is not for

• Routine bloat between autovacuums - that is what VACUUM is for, and a repack on a table that is merely slowly growing is maintenance overhead for no benefit.
• A partitioned table with CONCURRENTLY - the concurrent form rejects partitioned targets; repack the partitions instead, or accept the full lock.
• A table you cannot give a replication slot - CONCURRENTLY needs wal_level = logical and a free max_repack_replication_slots entry, so it is not available everywhere.
• Anything older than PG 19 that must stay online - that remains pg_repack’s job, and it is not going away while 15-18 are supported.