Postgres 19's REPACK rewrites a bloated table without locking out your writers

PostgreSQL 19 adds a command called REPACK. It reclaims the space VACUUM leaves behind by rewriting the table, which is what VACUUM FULL does, and it can rewrite in index order, which is what CLUSTER does. Two old commands folded into one.

The reason to care is the option on the end. REPACK (CONCURRENTLY) does that rewrite while the table stays readable and writable.

It is in beta 3 as I write this, so none of this is production advice yet. But it is worth knowing now, because one of the restrictions is something you would want to fix on your own schedule rather than discover during an upgrade.

What the lock actually costs

Everybody knows VACUUM FULL takes an ACCESS EXCLUSIVE lock. Far fewer people have watched what that looks like from the application side, so I built a bloated table and pointed traffic at it while the maintenance ran.

Two million live rows in 424 MB, eight connections updating rows as fast as they can, then the maintenance command from a ninth:

command	duration	writes committed	slowest write	size
REPACK (CONCURRENTLY)	4.32 s	3,988	125 ms	424 → 213 MB
VACUUM FULL	0.97 s	33	969 ms	424 → 212 MB

Thirty-three writes against nearly four thousand. Same table, same load, same space reclaimed.

It blocks reads too, which is the part I think people underestimate. A single connection running SELECT count(*) ... WHERE id = ? in a loop against a 1.1 GB table:

command	duration	reads served	slowest read
VACUUM FULL	2.27 s	1	2266 ms
REPACK (CONCURRENTLY)	3.94 s	384	16 ms

One read. ACCESS EXCLUSIVE means exactly what it says, and a dashboard querying that table during maintenance is not slow, it is stopped.

The number that argues the other way

Look at the durations again. VACUUM FULL finished in 0.97 seconds. REPACK (CONCURRENTLY) took 4.32, more than four times longer.

That is not a defect, it is the deal. VACUUM FULL is fast because it has the table entirely to itself and can rewrite straight through. REPACK (CONCURRENTLY) builds a new copy while the old one keeps accepting writes, captures everything that changed using logical decoding, replays it, and only then takes a brief lock to swap the files. Serving 3,988 writes during the rewrite is work, and the work costs time.

So the trade is total duration against availability, and for anything user-facing that is a trade worth making. Four seconds of normal service beats one second of downtime. But if you have a genuine maintenance window and nothing is running, plain REPACK is the faster tool and there is no reason to reach for CONCURRENTLY.

The slowest write during the concurrent run was 125 ms, not zero. That is the file swap, which still needs ACCESS EXCLUSIVE. It holds it for the swap rather than the rewrite.

You can watch it work

19 also ships pg_stat_progress_repack, which I found by accident and then used constantly. Polling it through a repack of a 1549 MB table:

phase                        heap blocks        tuples scanned   indexes rebuilt
initializing                       0 / 0                     0                 0
seq scanning heap            249 / 181,819                 4,092               2
rebuilding index         181,819 / 181,819             3,000,000               2
catch-up                 181,819 / 181,819             3,000,000               2
performing final cleanup 181,819 / 181,819             3,000,000               2

Those phase names are the mechanism, written down. It scans the heap into a new copy, rebuilds the indexes, then catches up on everything that changed while it was working, and only then does the final cleanup and swap. If you are running this on a large table and want to know whether it is nearly done or barely started, that view is the answer, and it is the difference between a ten-second operation you watch and a ten-minute one you worry about.

The obvious worry with any rewrite that replays a change stream is that the writers outrun the replay and it never converges. On this workload it is not close. Sixteen connections committing 1,120 updates a second for the entire rewrite of a 1549 MB table:

phase	time
initializing	0.14 s
seq scanning heap	13.46 s
rebuilding index	0.68 s
catch-up	0.19 s

16,228 writes landed during the rewrite and replaying them took two tenths of a second. Almost all of the cost is the sequential scan, which is the part that does not care how busy you are. That is the number that convinced me this is usable on a live table.

It also cancels cleanly. I sent pg_cancel_backend to a repack a second into a 1 GB table: the statement aborted, the table was untouched at 2,000,000 rows and 1033 MB, the database grew by nothing, and no replication slot was left behind.

The brief lock is only brief if nothing is holding the table

This is the caveat I would want to know before running it on anything I cared about, and it is not in the headline.

CONCURRENTLY still needs ACCESS EXCLUSIVE for the file swap. It asks for it at the end, and if another session is holding the table, it waits. While it waits, it is sitting in the lock queue with a pending ACCESS EXCLUSIVE request, and in Postgres everything that arrives afterwards queues behind that.

I ran the same repack on the same table twice, once with the table free and once with a slow reader on it:

situation	repack duration
nothing holding the table	0.54 s
one 8-second reader holding ACCESS SHARE	7.08 s

The query that matters is a third one: an ordinary SELECT count(*) from a different connection, arriving while the repack sat waiting. It took 5.08 seconds to come back. It did succeed, but five seconds is an eternity for a query that normally takes four milliseconds.

That query did nothing wrong. It was not competing with the long reader, which only holds ACCESS SHARE and would have let it straight through. It was stuck behind the repack's pending lock request. pg_locks during the pile-up shows it exactly:

 pid   granted  mode                        query
 1891  t        AccessShareLock             SELECT count(*) FROM q2      <- the long reader
 1894  t        ShareUpdateExclusiveLock    REPACK (CONCURRENTLY) q2
 1894  f        AccessExclusiveLock         REPACK (CONCURRENTLY) q2     <- waiting for the swap
 1897  f        AccessShareLock             SELECT count(*) FROM q2      <- queued behind the repack

So "concurrent" means the rewrite is concurrent. The swap is not, and one slow query anywhere on that table converts a half-second operation into a multi-second stall for everyone. It is still enormously better than VACUUM FULL, which blocks for the entire rewrite rather than the wait. But the honest version of the advice is: run it when your queries on that table are short, and if you have an analytics job that reads it for a minute, that minute is your outage.

A lock_timeout on the repacking session is the obvious guard. It gives up rather than holding the queue open, and you try again later.

It really does replace CLUSTER

REPACK ... USING INDEX is the CLUSTER half. Physical ordering, measured by the correlation statistic on the indexed column:

	correlation on the indexed column
before	-0.0061
after REPACK USING INDEX	1.0000
after CLUSTER	1.0000

Identical outcome. And REPACK (VERBOSE) tells you what it did:

INFO:  repacking "public.clu" in physical order
INFO:  "public.clu": found 0 removable, 100000 nonremovable row versions in 443 pages

The four tables it will refuse

Here is where the feature meets a real database. Every error below is verbatim from beta 3.

No primary key.

ERROR:  cannot execute REPACK (CONCURRENTLY) on relation "nopk"
HINT:  Relation "nopk" has no identity index.

Logical decoding has to identify rows, so it needs an identity index. Your oldest and most bloated table is the one most likely to lack one.

A unique index is not enough on its own, which surprised me. I had to check it twice:

CREATE UNIQUE INDEX ri_tbl_a ON ri_tbl (a);          -- still refused
ALTER TABLE ri_tbl REPLICA IDENTITY USING INDEX ri_tbl_a;   -- now accepted

That is the escape hatch if adding a primary key to a huge table is not something you fancy this quarter.

Unlogged tables.

HINT:  REPACK (CONCURRENTLY) is only allowed for permanent relations.

Inside a transaction block.

ERROR:  REPACK (CONCURRENTLY) cannot run inside a transaction block

Plain REPACK runs inside a transaction quite happily. Only the concurrent form cannot, which makes sense once you know it is doing logical decoding underneath.

Partitioned tables, and this is the one that will catch people.

ERROR:  REPACK (CONCURRENTLY) is not supported for partitioned tables
HINT:  Consider running the command on individual partitions.

Partitioned tables are the ones that grow to hundreds of gigabytes, which makes them exactly the ones you most want to rewrite without downtime, and the online path refuses them. The hint works, you go per partition, but you have to write that yourself:

SELECT format('REPACK (CONCURRENTLY) %I.%I;', n.nspname, c.relname)
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relispartition AND c.relkind = 'r';

Pipe that back into psql and it does work. A partition of 32 MB carrying 75% dead tuples came back at 8 MB, online, exactly as if the parent had been allowed.

One more practical thing, because disk headroom is what stops people running any of this. REPACK writes a new copy before dropping the old one, and the documentation asks for free space "at least equal to the sum of the table size and the index sizes". That is the worst case, not the usual one. Watching pg_database_size through a repack of a 517 MB table that was half dead tuples:

database before	2164 MB
peak during the rewrite	2423 MB
after	1907 MB

Peak overhead was 259 MB, about half the size of the table being repacked, because the new copy only holds the rows that survive. The more bloated the table, the less headroom you need relative to its current size, which is a pleasant inversion of the usual rule.

One thing the documentation mentions that did not bite: it says CONCURRENTLY requires available replication slots. I filled all ten, max_replication_slots included, and it ran anyway. Whatever it uses does not come out of that pool.

And a warning from the docs I would not skip past: REPACK with CONCURRENTLY is not MVCC-safe.

A tool, because checking by hand gets old

Four conditions, checked per table, is a catalogue query rather than an afternoon. pg-repack-plan reads pg_class and pg_stat_user_tables, works out what is bloated enough to be worth the trouble, and sorts your tables into the ones the online path will take and the ones it will not:

table                       size    dead    reclaim  repack online?
public.events             226 MB   66.7%     151 MB  yes
public.metrics_2026        32 MB   75.0%      24 MB  yes
public.sessions            22 MB   40.0%       9 MB  no identity index, add a primary k

Online, no maintenance window needed (2):
  -- the swap at the end still needs ACCESS EXCLUSIVE, so it queues
  -- behind a slow query and everything else queues behind it.
  SET lock_timeout = '5s';
  REPACK (CONCURRENTLY) public.events;
  REPACK (CONCURRENTLY) public.metrics_2026;

Needs a maintenance window, these hold ACCESS EXCLUSIVE throughout (1):
  REPACK public.sessions;   -- no identity index, add a primary key or ...

Roughly 184 MB reclaimable across 3 table(s).

The only interesting thing about it is how it is tested. A tool that predicts what a server will do is worthless if it drifts from the server, so the tests do not check the logic against itself. They build each awkward table, ask the planner for a verdict, then actually run REPACK (CONCURRENTLY) and compare. Ten tests, and the planner and the server agree on every one, including the partitioned parent, its own partition, the unlogged table, the table with no primary key, and the one rescued by REPLICA IDENTITY.

The mode I actually expect people to use is the one that runs against a server without REPACK at all. Managed Postgres offers 15 through 18 today, so I pointed it at a DigitalOcean managed cluster, db-amd-1vcpu-1gb in Frankfurt running 18.6, and seeded it with the same shape of bloat:

! This server is PostgreSQL 18. REPACK arrived in 19, so the plan below is
  what you could run after upgrading.

table                       size    dead    reclaim  repack online?
public.events              75 MB   66.7%      50 MB  yes
public.metrics_2026        16 MB   75.0%      12 MB  yes
public.sessions            11 MB   40.1%       4 MB  no identity index, add a primary k

That is the useful output right now. Not the command, the inventory: two tables ready for the online path, and one that needs a primary key before it ever will be. Finding that out today gives you months. Finding it out mid-upgrade gives you a decision to make at the worst possible moment.

What I got wrong on the way

My first run said VACUUM FULL blocked nothing. One write during the window, 1.4 ms, no stalls. I very nearly wrote a paragraph about the lock being less dramatic than its reputation.

The harness was lying, in a way I would not have spotted by reading it. Each write recorded its timestamp when it finished, and I counted writes whose timestamps fell inside the maintenance window. The write that blocked for the entire two seconds finished after the window closed, so the filter dropped it. The single event I was trying to measure was the single event I excluded.

Timestamping at the start of the write instead moved the result from "no stalls" to "one write, blocked 8762 ms". Same database, same command, opposite conclusion. If you take one thing from this post and it is not about Postgres, let it be that a measurement harness deserves the same suspicion as the thing it measures.

Run it yourself

The beta ships as a Docker image, so this is about ninety seconds of setup:

docker run -d --name pg19 -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo \
  -p 55439:5432 postgres:19beta3

docker exec -it pg19 psql -U postgres -d demo -c '\h REPACK'

The planner and the benchmark are both at
oceanforge/pg-repack-plan, MIT licensed.

git clone https://github.com/oceanforge/pg-repack-plan
cd pg-repack-plan && pip install -e .

export DATABASE_URL="postgres://user:pass@host:5432/dbname"
pg-repack-plan --min-size 10MB

It changes nothing. Catalogue reads on the way in, REPACK statements printed on the way out, and whether you run them stays your decision.

What I would take from this

REPACK (CONCURRENTLY) moves table maintenance out of the 3am window and into a Tuesday afternoon. It bills you a longer rewrite for the privilege, and on the evidence above that is a bargain.

It is not free of locks, though, and I would rather you took that away than the headline. The rewrite is concurrent; the swap at the end is not, and it will sit in the lock queue behind whatever slow query happens to be reading that table, with everything else stacking up behind it. Short queries, or a lock_timeout and a retry.

The part worth doing this week is duller. Go and find out which of your big tables have neither a primary key nor a replica identity, because that one fact decides whether any of this is available to you when 19 lands. It is a single query. And if the answer is your largest table, you have just given yourself months to fix it quietly, rather than meeting the problem halfway through an upgrade with everyone watching.