PostgreSQL 19's REPACK CONCURRENTLY swaps a five-second table lock for one under a second

I deleted a million rows from a two-million-row table and the table stayed exactly the same size. That part isn't news; it's how VACUUM has always worked in Postgres, because it marks space reusable rather than giving it back to the filesystem. What changes in PostgreSQL 19 is what you do next. There's a new command called REPACK, and its CONCURRENTLY option is supposed to do the rewrite that used to need VACUUM FULL or the third-party pg_repack extension, without taking the table away from everyone else while it works.

I ran both against the same bloat on PostgreSQL 19 beta 4, with a client thread hammering reads and writes against the table the whole time, and measured how long that client actually sat waiting.

The setup

I built a table with 2,000,000 rows of 200-byte text each, deleted every even-numbered row, and left it unvacuumed-into-shrinkage:

postgres=# SELECT pg_total_relation_size('bloat_test');
 531 MB   (before)
 531 MB   (after deleting 1,000,000 of 2,000,000 rows, even a plain VACUUM)
 266 MB   (after either VACUUM FULL or REPACK)

Both commands end up at the same 266 MB. The question is what the rest of the system felt while getting there.

I ran a background client that did one UPDATE and one SELECT against a live row on a tight loop, with a 250ms threshold for counting a call as "blocked" rather than just unlucky scheduling. Then I ran VACUUM FULL bloat_test and REPACK (CONCURRENTLY) bloat_test against freshly rebuilt bloat, three times each.

trial	VACUUM FULL: client blocked, total	REPACK CONCURRENTLY: client blocked, total
1	4.74s	0.93s
2	4.35s	0.57s
3	5.31s	0.35s

The statement itself took roughly the same wall-clock time either way, 3-6 seconds depending on the run. The difference is where that time landed. Under VACUUM FULL, the client was blocked for essentially the entire statement, because that command takes an ACCESS EXCLUSIVE lock up front and holds it until the swap is done. Under REPACK CONCURRENTLY, the client kept running normally while Postgres copied the table in the background and used logical decoding to capture anything that changed during the copy; the lock only shows up right at the end, for the file swap.

Ten clients instead of one

A single background client is the easy case. I reran the same comparison with ten clients hammering the table at once, to see whether the "brief swap lock" claim in the documentation holds up once there's real contention for it.

	VACUUM FULL	REPACK CONCURRENTLY
worst single client's total blocked time	5.44s	0.28s
blocked time summed across all ten clients	54.4s	2.73s

The worst-case stall per client barely moved for REPACK CONCURRENTLY, 0.28s against 0.33s with one client. That matches what the docs say: the exclusive lock is only held for the swap, and the swap doesn't get much slower just because more clients are waiting on it. But the summed number is the more honest one to put in a capacity plan. Ten clients all catching that same brief swap window means ten short stalls landing at once, which is a different kind of production event than one client stalling once, even if each individual stall is small.

What it actually costs

The lock-time win isn't free. I reset pg_stat_wal before each run and read wal_bytes after.

	VACUUM FULL	REPACK CONCURRENTLY
WAL written for the same 531MB → 266MB rewrite	655-783MB	816MB (three runs, barely varied)

REPACK CONCURRENTLY wrote more WAL every time I measured it, by roughly 4% to 25% depending on how much VACUUM FULL varied between runs. That's the logical-decoding worker doing its job: it isn't just rewriting the table, it's also decoding and replaying every change that landed on the old copy while the rewrite was in flight, and that replay goes through WAL like any other write. If your bottleneck is WAL-shipping bandwidth to a replica rather than lock contention, the "fewer locks" version is the one that pushes more bytes down that pipe.

I also tracked data-directory size through each run, expecting CONCURRENTLY to need noticeably more temporary space, since the documentation specifically calls out an extra temp file for rows written to the old table during the copy. In my test it didn't show up: peak growth was 276MB for REPACK CONCURRENTLY against 279MB for VACUUM FULL, close enough to be noise. My write load was a few thousand single-row updates a second, which may simply not be enough to fill that temp file to a size that matters. I'd expect this to look different under a heavier write rate, and I didn't have time to push it that far.

What it refuses

The most interesting restriction in the documentation is one line: CONCURRENTLY needs a free slot, gated by a setting called max_repack_replication_slots, because each concurrent repack runs its own logical decoding session. The default is 5, which is generous enough that I couldn't trigger the limit with two tables under normal settings, no matter how I timed it.

So I restarted the container with -c max_repack_replication_slots=1 and tried two REPACK (CONCURRENTLY) calls on two different tables, started 150ms apart:

B-bloat_test2 ('error', 0.005s, 'all replication slots are in use
HINT:  Free one or increase "max_repack_replication_slots".
CONTEXT:  REPACK decoding worker
')
A-bloat_test ('ok', 5.02s, None)

The second call failed in 5 milliseconds with a clear, specific error, not a hang or a vague lock timeout. The first one ran to completion unaffected. That's a sane failure mode for a DBA to hit at 2am, but it means a maintenance window that runs several REPACK CONCURRENTLY jobs in parallel, which is exactly the kind of thing you'd want to do to clear bloat across a database quickly, needs the same sizing attention you'd give to max_wal_senders or max_replication_slots, both of which it borrows capacity from.

Watching it happen

There's a new view, pg_stat_progress_repack, that reports live progress. I polled it 0.8 seconds into a run on the 2-million-row table and got:

 phase              | heap_tuples_scanned | heap_tuples_inserted | heap_blks_total | heap_blks_scanned
---------------------+----------------------+-----------------------+------------------+--------------------
 seq scanning heap   |               270726 |                270726 |            62500 |              16921

That's enough to build a sane "is this going to finish soon" check into a runbook: compare heap_blks_scanned to heap_blks_total and you have a real completion percentage, something VACUUM FULL never gave you beyond watching the process not exit.

What I got wrong on the way

While reading up on this feature before testing it, I found design-era commentary describing a restriction that only one REPACK could run on the entire server at a time. My first concurrency test seemed to back that up: I polled pg_stat_activity every 100ms while running two REPACK CONCURRENTLY calls on different tables, and across fifty polls I only ever saw one of the two queries listed as active.

I nearly wrote that up as confirmed. But the two calls both returned success, with timings that didn't fit a strict queue, so I didn't trust the result and went looking for the actual gate instead of the one I'd half-expected to find. That's how I found max_repack_replication_slots, the setting that's actually checked, and it defaults to 5, not 1. My polling had just been too coarse and too unlucky to catch the second backend mid-flight; both were really running at once. Pinning the setting down to 1 and reproducing the error on purpose, rather than trusting an absence of evidence from a loose poll, is what actually nailed the restriction down.

Run it yourself

This needs Docker and Python with psycopg2-binary installed.

docker run -d --name pg19 -e POSTGRES_PASSWORD=postgres -p 15432:5432 postgres:19beta4

import psycopg2, time

DSN = "host=127.0.0.1 port=15432 dbname=postgres user=postgres password=postgres"
conn = psycopg2.connect(DSN); conn.autocommit = True
cur = conn.cursor()

cur.execute("CREATE TABLE bloat_test (id bigint primary key, payload text)")
cur.execute("INSERT INTO bloat_test SELECT g, repeat('x', 200) FROM generate_series(1, 2000000) g")
cur.execute("DELETE FROM bloat_test WHERE id % 2 = 0")
cur.execute("SELECT pg_total_relation_size('bloat_test')"); print(cur.fetchone())

t0 = time.monotonic()
cur.execute("REPACK (CONCURRENTLY, VERBOSE) bloat_test")   # or: VACUUM FULL bloat_test
print("elapsed", time.monotonic() - t0)

To see the refusal, restart the container with -c max_repack_replication_slots=1 and fire the same REPACK (CONCURRENTLY) statement against two different tables from two separate connections within a couple hundred milliseconds of each other.

To watch it live, open a second connection while the rewrite is running and run:

SELECT phase, heap_tuples_scanned, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_repack;

What to do with this

This is beta software; the syntax, defaults, and even the command name could still move before 19 ships. If you run a maintenance job that currently reaches for pg_repack or schedules VACUUM FULL in a quiet window, it's worth testing REPACK CONCURRENTLY against a copy of your actual bloated tables now, under your actual concurrent write rate, rather than waiting for GA. The lock-time win is real and it's large. The WAL cost is also real, and if you're paying for replica bandwidth or have a tight WAL retention window, size that before you turn this into a routine job rather than a one-off fix. And if you plan to run it against more than a handful of tables in the same maintenance window, check max_repack_replication_slots first, because the default of 5 is a wall you can hit sooner than it looks.