What REPACK (CONCURRENTLY) costs while it runs

Two years ago I compared pg_repack and pg_squeeze, the two extensions most of us reach for when a table has bloated beyond what autovacuum will ever give back and VACUUM FULL isn't an option.

PostgreSQL 19 changes the starting point. REPACK brings VACUUM FULL and CLUSTER together under one command, and REPACK (CONCURRENTLY) does the rewrite online. It needs no extension and no shared_preload_libraries, so it works on managed services too. That will bring online repacking to a much wider audience, so I spent the last few weeks measuring what it actually does to a running database, and how it compares with the two extensions.

How it works

Rewriting a table is easy 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. Nobody can read or write the table in the meantime, and on a big table that's minutes or hours.

The online version REPACK (CONCURRENTLY) does the same. Except you can use the table while it's being copied and hence it keeps changing while the process runs. A row copied in the first few seconds can get updated two minutes later. Or deleted. An online repack tool needs to do three things.

• Consistent starting point
• Record of everything that changed during the process
• And a way how to apply those changes

REPACK gets the first two steps for free from logical decoding. The mechanism logical replication is using. It starts by creating a temporary replication slot, which gives it a snapshot. Point in time for the initial copy. Because every change that happens during that phase is written to WAL anyway, a background worker simply reads the WAL and collects the changes to the table.

Then comes the I/O intensive part. REPACK copies the rows visible in that snapshot into a new file, leaving the dead ones behind, and builds all the indexes again. Meanwhile the worker keeps collecting changes. When the copy is ready, REPACK replays them onto it and catches up.

On a busy database catch-up never quite finishes. At the very end REPACK needs to have a point where it takes ACCESS EXCLUSIVE, replays the last changes, swaps the files and commits. This is the only time when the table is unavailable. All of this, from the snapshot to the swap, is a single transaction.

pg_squeeze works the same way. REPACK was actually derived from it. It uses the same slot, same snapshot, same single transaction. The difference is that it lives outside core, so it needs shared_preload_libraries and a restart, and its slot holds on to WAL until it gets around to decoding it.

pg_repack predates logical decoding and keeps its record with a trigger instead. Every insert, update and delete on your table also writes a row into a log table. It copies the table, then applies the log in small batches, each in its own transaction, and swaps. So every change is written twice, and there's something installed on your table for as long as it runs.

The numbers

With 3,000 single-row updates per second hitting the table the whole time:

For the measurements I chose a table with 90 million rows, three indexes, then every third row deleted and a regular VACUUM. I.e. 60 million live rows in 27.8 GB (22 GB of heap) giving a table you'd actually want to repack. Every run started from an identical copy of it.

pg_repack's WAL is the number to look at: 1.8 times as much goes through your archive, your backups and every replica, even with nothing writing to the table. Its copy is an INSERT … SELECT logged row by row, where the other tools log the new file page by page. When sizing disk for any online repack, count on a second copy of the table and its indexes, plus room for the WAL and the file of changes collected during the run.

VACUUM is held back

If you've ever attended a PostgreSQL conference talk about VACUUM, you might remember the advice: never stop autovacuum. Let it do its job. But this is exactly what happens when you run an online repack.

This isn't specifically about REPACK. Alongside the I/O load, it's the second thing to know before starting a five-hour repack on a Tuesday afternoon. The reason is MVCC. Online repacking needs to keep seeing the table as it looked when the operation started. PostgreSQL therefore can't remove row versions that the old snapshot might still need.

VACUUM still runs during the repack, but it can't remove those row versions. ANALYZE is different. It can continue updating planner statistics normally.

To demonstrate this, I wanted a repack that lasted a few minutes. I used a 5M-row table and made it rebuild an expression index that sleeps for 20 ms every thousandth row. While that was running, another table received 1,500 updates per second, and I ran VACUUM (VERBOSE) on it every five seconds.

After two minutes I got this result

A normal snapshot only matters inside its own database. A session connected to one database can't read another, so VACUUM there ignores it. A replication slot behaves differently. Slots belong to the whole server, not a single database. That's why pg_repack only held back tables in the database being repacked, while REPACK and pg_squeeze held back tables in every database on the cluster.

There's no option to turn this off. What you can do is keep the run short (see When the run is long below), watch n_dead_tup for your busiest tables while it runs, and avoid scheduling a long repack alongside a large batch job on another table.

The swap at the end

On a quiet table, getting ACCESS EXCLUSIVE for the swap takes milliseconds. It has to wait for every transaction that holds any lock on the table, though: an ETL job, a long SELECT for a report, a session left idle in transaction after reading from it. If this happens, REPACK waits behind the report, and every new query waits behind REPACK.

I held a 20 second read on a 200k row table while four clients did single-row lookups at 120,000 per second: REPACK and pg_squeeze dropped them to 0 for the whole 18 seconds and only recovered when the report ended, while pg_repack kept serving at 4 to 37 per second with about a second of latency. With lock_timeout = 3s, REPACK gives up at 3 seconds instead, with ERROR: canceling statement due to lock timeout, and the run is discarded.

Setting lock_timeout limits how long everyone waits. The catch is that REPACK tries only once. By the time it asks for the lock, the copy, the index builds and the catch-up are already done, and a timeout throws all of that away. On the 28 GB table that was two minutes of work, and 38 minutes for 100 GB on the cloud VM.

pg_repack handles this better. It asks for the lock with a short timeout, and if it doesn't get it, it lets go and tries again. Queries get through between the attempts, so the application sees slowness instead of a complete stop.

Writers wait a second time, because the last round of catch-up runs while REPACK holds ACCESS EXCLUSIVE. From the per-second pgbench log of the 3,000 updates/s runs:

Those 7 seconds aren't a fixed cost. It's the time to replay what came in during the previous round, so it grows with the write rate, the length of the run and the speed of the storage.

On the cloud VM that meant:

On the cloud VM, disk throughput is part of what you pay for. That instance size gets 240 MiB/s of reads and 240 MiB/s of writes, and the repack's copy uses most of it. With pg_repack the application's writes suffer during the whole run, not just at the swap. That was originally my motivation to switch to pg_squeeze, and it's why REPACK is the way forward for most of us.

To measure this, each tool got the same 500 updates per second, and I counted every update that took longer than a second:

pg_repack delayed twice as many updates, with its trigger adding a log-table insert to every one of them on top of a copy that writes 1.8 times the WAL. It can also fail to finish at all: when writes arrive faster than it applies its log, the log only grows. REPACK in the same situation finished, and paid for it with the 432 s stall above.

Old snapshots find an empty table

The documentation says REPACK (CONCURRENTLY) is not MVCC-safe. A REPEATABLE READ transaction takes its snapshot (by reading some other table, so it holds no lock on ours), the table gets repacked, then the transaction counts the rows:

Every row in the new copy was written by REPACK's transaction, which that old snapshot considers to be in the future. A long export in REPEATABLE READ that reads the table only after the swap gets nothing back, and nothing tells it why.

pg_repack's 10,000 doesn't mean it's safe. Before copying, it waits for every transaction open in the database, read-only ones included, so here it simply sat until the old transaction committed. Take the snapshot after that wait, while its INSERT ... SELECT copy is running, and after the swap that snapshot sees 0 rows there too: the copy is one statement in one transaction, still uncommitted when the snapshot was taken. The pg_squeeze README says as much: like pg_repack, it changes row visibility and allows the MVCC-unsafe behaviour from the docs. pg_repack only narrows the window, and the price is that a long export holds up the start of the repack.

Memory, and a hard ceiling on concurrent changes

For every row other sessions update or delete during the run, the REPACK backend keeps about 50 bytes in memory until it commits. Nothing like maintenance_work_mem bounds it, and the docs only mention extra disk. I held REPACK just before catch-up, piled up tens of millions of updates, then let it go, in a container with a memory cap and no swap:

The OOM kills take everyone down, not only REPACK:

LOG:  client backend (PID 41) was terminated by signal 9: Killed
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing

The 8 GB run didn't run out of memory. It hit a fixed limit: the structure holding those per-change entries doubles whenever it fills, and after 104,857,600 entries the next doubling asks for more than PostgreSQL allows in one allocation. No amount of RAM helps. So REPACK (CONCURRENTLY) can't finish if more than 105 million rows are updated or deleted during the run. It fails at the very end, and the original table is left untouched.

On PostgreSQL 19 it's arithmetic. What counts is every row updated or deleted on the table during the run. Rows, not statements, so one UPDATE touching 50 rows is 50. Plain inserts don't count.

max (updates + deletes) per second ≈ 29,000 / REPACK duration in hours

pg_stat_user_tables has the counters. Take two samples over a representative period (ideally including your batch jobs) in the same session:

CREATE TEMP TABLE chg_sample AS
SELECT relid, n_tup_upd + n_tup_del AS changes, now() AS sampled_at
FROM pg_stat_user_tables;

-- ... wait for it ...

SELECT s.relname,
       pg_size_pretty(pg_total_relation_size(s.relid)) AS total_size,
       round((s.n_tup_upd + s.n_tup_del - c.changes)
             / extract(epoch FROM now() - c.sampled_at)) AS changes_per_sec,
       round(104857600.0
             / nullif((s.n_tup_upd + s.n_tup_del - c.changes)
                      / extract(epoch FROM now() - c.sampled_at), 0)
             / 3600, 1) AS hours_until_ceiling
FROM pg_stat_user_tables s
JOIN chg_sample c USING (relid)
ORDER BY changes_per_sec DESC NULLS LAST
LIMIT 20;

hours_until_ceiling is how long REPACK can run on that table at that rate. Averages hide the real risk, though: one nightly job that updates 120 million rows ends the REPACK on its own.

When the run is long

The length of the run comes from the size of the table and the speed of the storage, and everything above gets worse with it.

The 28 GB table took 107 seconds on the Hetzner NVMe (0.9 TB/h) and 404 seconds on the 4 vCPU cloud VM; a 100 GB version took 1,412 seconds there, so a plain rewrite scales with size. The table extrapolates from those two rates; the multi-terabyte rows aren't measurements.

Extra indexes and heavy writes push the numbers further, so time a rewrite on your own hardware if you can. Partitioning is the real answer at that scale: REPACK (CONCURRENTLY) runs per partition, each with its own short run and budget, most of them on partitions nobody writes to anymore.

Requirements, and what happens when it fails

• The table needs a replica identity index: a primary key or REPLICA IDENTITY USING INDEX. A deferrable primary key doesn't count, and REPLICA IDENTITY FULL and NOTHING aren't supported.
• It doesn't run on a partitioned table's parent, only on individual partitions.
• It needs a free slot under max_repack_replication_slots (5 by default, counted separately from max_replication_slots) and a free background worker slot (max_worker_processes).
• wal_level = replica is enough. PostgreSQL 19 switches effective_wal_level to logical for the whole server as soon as any logical slot exists, REPACK's temporary one included, and only switches it back at the next checkpoint after the last one is dropped. Until then all your WAL carries the extra decoding information.

I cancelled it at every phase, killed its worker, killed the backend and let lock_timeout fire, and every time it cleaned up after itself, with nothing left behind in pg_replication_slots or the data directory. pg_squeeze cleaned up too. pg_repack is the one to watch. Kill its client mid-run (a dropped SSH session will do) and it leaves its log table, the half-built copy and its trigger on your table. The trigger keeps logging every change into the orphaned log table, and the next pg_repack run refuses to start until you drop it.

Before you run it

Most of this comes down to the length of the run, so start there. Multiply the table's update and delete rate by how long you expect REPACK to take, and keep the result well under 100 million rows. The query above gives you the rate; timing a plain rewrite on similar hardware gives you the duration.

Then a few checks I'd do every time:

• Look for long transactions and long queries in pg_stat_activity. They either stretch the vacuum hold or sit in front of the final lock.
• Make sure the disk can take a second copy of the table and its indexes, plus WAL.
• Decide on lock_timeout before you start. Without it, the swap can stall everyone for as long as the slowest query on the table. With it, the whole run can be thrown away at the last step.

While it runs, expect n_dead_tup to climb on your busy tables, in every database, and don't worry about it until it's done. And if someone runs long REPEATABLE READ jobs against that table, tell them first, or schedule around them. On a very busy table, also keep in mind the backend can need up to 5 GB of memory.

Which one to use

Once you get to PostgreSQL 19, REPACK (CONCURRENTLY) is the best option for most of us. It was the fastest online repack in every run, the lightest on WAL once writes were flowing, and the only one that left nothing behind when I broke it. Unlike the extensions, it will run on managed Postgres, where most of these tables live.