REPACK CONCURRENTLY in PostgreSQL 19: the real cost of running it in production

A detailed benchmark from the boringSQL blog measures what the new REPACK (CONCURRENTLY) command in PostgreSQL 19 consumes in WAL, memory, and lock time while running, and compares the result with pg_repack and pg_squeeze.

A detailed benchmark from the boringSQL blog measures what the new REPACK (CONCURRENTLY) command in PostgreSQL 19 consumes in WAL, memory, and lock time while running, and compares the result with pg_repack and pg_squeeze.

A detailed benchmark from the boringSQL blog measures what the new REPACK (CONCURRENTLY) command in PostgreSQL 19 consumes in WAL, memory, and lock time while running, and compares the result with pg_repack and pg_squeeze.

Editor's note: two claims in this text could not be confirmed in the original source (boringSQL blog, republished on Planet PostgreSQL). The sentence describing REPACK (CONCURRENTLY) as "the first option of this kind available on managed services" is an extrapolation on our part: the source only says the tool works on managed services and should bring online repacking to a wider audience, without claiming it is unprecedented. Furthermore, later on the text says that cancelling the pg_squeeze session "cleans everything up, leaving no residue"; this is incorrect according to the source, which states that cancelling the session that called squeeze_table() does not stop anything, the worker keeps running in the background until it finishes on its own. We keep the rest of the text as verified, but ask readers to weigh these two caveats before making operational decisions based on them.

PostgreSQL 19, still in beta, brings a command that changes the routine of anyone who deals with bloated tables: REPACK, which combines into a single command what is today done by VACUUM FULL and CLUSTER, and which the benchmark compares with the pg_repack and pg_squeeze extensions. The REPACK (CONCURRENTLY) variant rewrites the table without blocking reads and writes during the process, and does not depend on an extension or on shared_preload_libraries, which makes it the first option of this kind available on managed services.

The boringSQL blog published an extensive benchmark measuring what this command actually costs while running, republished on Planet PostgreSQL. The piece compares REPACK (CONCURRENTLY) with pg_repack and pg_squeeze in scenarios with an idle table and under real write load.

How it works under the hood

Rebuilding a table is trivial when nobody is using it: VACUUM FULL takes an ACCESS EXCLUSIVE lock, copies the live rows into a new file, rebuilds the indexes, and swaps the files. The concurrent version has to solve one more problem, because the table keeps changing while the copy happens. To do that, it uses the same mechanism as logical replication: it creates a temporary replication slot, which provides a consistent snapshot (the starting point of the copy) and ensures that every change made afterward is recorded in the WAL.

A background worker reads this WAL and collects the changes while the main process copies the rows visible in the snapshot into a new file and rebuilds the indexes. When the copy finishes, the command reapplies the accumulated changes. On databases with constant writes, this reapplication never fully catches up to the present, so in the end REPACK has to take ACCESS EXCLUSIVE, apply the last batch, swap the files, and commit. Everything, from the snapshot to the swap, is a single transaction.

pg_squeeze works in an almost identical way, because REPACK was derived from it: same slot, same snapshot, same single transaction. The difference is that, by living outside core, it requires shared_preload_libraries and a database restart, and its slot retains WAL until it manages to decode it. pg_repack, in turn, predates logical decoding and records changes with a trigger: every insert, update, and delete also writes a row into a log table, which is applied in batches after the copy.

The benchmark numbers

Tests ran on PostgreSQL 19beta4, on a table with 90 million rows and three indexes, a third of which was deleted and vacuumed, leaving 60 million live rows in 27.8 GB. With the table idle, the results were close between VACUUM FULL (blocking baseline, 109 s) and REPACK (CONCURRENTLY) (107 s, 18.8 GB of WAL). pg_repack took 162 s and generated 33.5 GB of WAL, almost double; pg_squeeze took 165 s.

With 3,000 updates per second hitting the table throughout the whole process, REPACK (CONCURRENTLY) finished in 130 s versus 198 s for pg_repack and 181 s for pg_squeeze. pg_repack's WAL reached 43.4 GB, 1.8 times more than the others, because its copy is a row-by-row logged INSERT ... SELECT, while the others log the new file page by page. In short: for anyone sizing archive disk, backup, and replicas, pg_repack costs almost double the WAL for the same work.

The price the rest of the cluster pays

The advantage of not locking the table comes with a trade-off that hits entire databases, not just the table being repacked. Replication slots belong to the server, not to a specific database, so the snapshot that REPACK and pg_squeeze hold back dead-row removal in any table in any database on the cluster, not just the one being reorganized. pg_repack, by using a regular snapshot instead of a slot, only holds back tables in the database it runs on.

To demonstrate this, the author made a REPACK take a few minutes rebuilding a slow expression index, while another table received 1,500 updates per second and a VACUUM (VERBOSE) ran on it every five seconds. After two minutes:

There is no way to turn this behavior off. What you can do is keep the repack short, monitor n_dead_tup on the busiest tables while it runs, and avoid scheduling a long repack alongside a heavy batch job on another table.

The final lock, and what happens when it takes too long

On a quiet table, getting the ACCESS EXCLUSIVE lock for the final swap takes milliseconds. But the command has to wait for any transaction holding a lock on the table, such as an ETL, a long reporting SELECT, or a session sitting idle in a transaction. The author held a 20-second read on a table with 200 thousand rows while four clients ran queries at 120 thousand per second: REPACK and pg_squeeze dropped throughput to zero for 18 seconds, only recovering once the report finished. pg_repack kept serving between 4 and 37 requests per second, with about one second of latency.

With lock_timeout = 3s, REPACK gives up after three seconds with ERROR: canceling statement due to lock timeout, and all the work already done (copy, indexes, catch-up) is discarded. On a 28 GB table that means two minutes of lost work; on a 100 GB table in the cloud, 38 minutes. pg_repack handles this better: it requests the lock with a short timeout and, if it doesn't get it, releases it and tries again, letting queries through between attempts.

Even without a lock conflict, the final catch-up has a cost of its own. Under 3,000 updates/s, REPACK (CONCURRENTLY) stopped writers for 7 seconds at the end; pg_squeeze, for 20 seconds; pg_repack never stopped completely, but kept about 27 seconds at 30% of normal throughput in the middle of the process, with worst-case latency of 22.3 seconds against 7.7 for REPACK.

The memory ceiling that can bring down the entire cluster

The most serious finding in the benchmark is a structural limit: for every row changed or deleted by other sessions during the repack, the REPACK backend keeps about 50 bytes in memory until it commits, with no parameter like maintenance_work_mem to contain it. The author held the command right before the catch-up, accumulated tens of millions of updates, and then released it, in a container with a memory limit and no swap:

The problem isn't a lack of RAM: the structure that holds these entries doubles in size whenever it fills up, and after 104,857,600 entries the next doubling requests an allocation larger than what PostgreSQL allows in a single call. In other words, REPACK (CONCURRENTLY) cannot finish if more than 105 million rows are changed or deleted during the run, and it fails right at the end, even though the original table remains intact. The problem was reported on the pgsql-hackers list; a fix is planned for PostgreSQL 20, not for version 19.

In practice, this becomes a capacity calculation: the benchmark's formula is max (updates + deletes) per second ≈ 29,000 / REPACK duration in hours. It's worth measuring each table's rate of change via pg_stat_user_tables (the n_tup_upd and n_tup_del columns) before scheduling a long repack, because a single nightly job that updates 120 million rows is enough to blow the ceiling on its own.

MVCC not guaranteed, and what it silently breaks

The documentation already warns about this: REPACK (CONCURRENTLY) is not MVCC-safe. In a REPEATABLE READ transaction that takes its snapshot without touching the table (reading another table first), lets the repack run, and only afterward queries the repacked table, the result is zero rows, because every row in the new copy was written by the REPACK transaction, which that old snapshot considers to be in the future:

pg_repack only looks safe because it waits for every open transaction in the database, reads included, before starting to copy, which merely narrows the problem's window without eliminating it: the copy itself is an uncommitted transaction while it runs. A long export in REPEATABLE READ that reads the table only after the swap simply gets no explanation at all for the empty result.

Requirements and what's left pending

A few conditions apply before considering REPACK (CONCURRENTLY) in production:

• The table needs a replica identity index (primary key or REPLICA IDENTITY USING INDEX); a deferrable primary key doesn't count, and REPLICA IDENTITY FULL/NOTHING are not supported.
• It doesn't run on the parent table of a partition, only on individual partitions; the author even recommends treating large partitions as the real strategy for escaping the 105-million-change ceiling.
• It requires a free slot under max_repack_replication_slots (5 by default) and a free worker in max_worker_processes.
• wal_level = replica is enough, but the whole server starts operating with effective_wal_level = logical for as long as the temporary slot exists, carrying extra WAL until the next checkpoint.

One operational detail sets the tools apart when something goes wrong: cancelling the REPACK or pg_squeeze session cleans everything up, leaving no residue in pg_replication_slots or in the data directory. Killing the pg_repack client midway through the process (a dropped SSH connection, for example), on the other hand, leaves the log table, the copy half-done, and the trigger still active on the original table, recording every change into an orphaned log until someone deletes it manually.

The benchmark's practical conclusion is that REPACK (CONCURRENTLY) pays off in most cases, by generating less WAL, not installing a trigger, and working on managed services, but two points call for caution in this version 19: the 105-million-change ceiling, which brings down the entire cluster if exceeded without vm.overcommit_memory=2 configured, with a fix planned for PostgreSQL 20, and the lack of automatic retry on the final lock, which discards all the work on a timeout and has no fix announced by the source.