VACUUM FULL Locks Your Table the Whole Time. Postgres 19's REPACK CONCURRENTLY Doesn't

• Book: Database Playbook
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

You deleted 80% of a 200-million-row table and the disk didn't notice. Autovacuum ran. The file stayed the same size. The built-in way to hand that space back has been VACUUM FULL (or its sibling CLUSTER). It takes an ACCESS EXCLUSIVE lock and holds it for the entire rewrite, so every read and write on the table waits until it's done.

That exact scenario is what depesz ran against the PostgreSQL 19 development branch in April: 200 million rows, 80% deleted, then repack (verbose, analyze, concurrently). The table went from 19 GB to 4117 MB in about 66 seconds. An insert loop kept running the whole time. Average insert latency rose from 1.469 ms to 5.536 ms during the repack, and the slowest insert took 63 ms.

That's the new REPACK command in PostgreSQL 19 with its CONCURRENTLY option. It's still in beta, but the official Docker image already has a build you can run on your laptop today.

What REPACK replaces

PostgreSQL has had two commands that rewrite a table into a fresh file: VACUUM FULL (compact it) and CLUSTER (compact it and sort it by an index). The PostgreSQL 19 release notes put it bluntly: "The two former commands did similar things, but with confusing names, so unify them as REPACK. The old commands have been retained for compatibility." The VACUUM page now marks FULL as deprecated and says it "makes VACUUM behave like REPACK without a USING INDEX clause."

The REPACK reference has three options: VERBOSE, ANALYZE, and CONCURRENTLY. REPACK orders is the old VACUUM FULL orders. REPACK orders USING INDEX orders_pkey is the old CLUSTER.

Rewriting a table online has, until now, meant an extension. pg_repack does it "without holding an exclusive lock on the processed tables during processing," and, as CYBERTEC describes it, it does that "the traditional way by creating a set of triggers on the table." Their own extension, pg_squeeze, uses "logical decoding instead of triggers to capture concurrent changes." That design is what landed in core.

The commit that added CONCURRENTLY went in on April 6, 2026 (committed by Álvaro Herrera, mostly Antonin Houska's code). It builds the new copy under a SHARE UPDATE EXCLUSIVE lock and decodes the changes other sessions make in the meantime through a replication slot and a background worker. Those changes are replayed just before the files are swapped.

Build a bloated table in Docker

PostgreSQL 19 is still in beta (more on that at the end), and the official postgres image publishes a 19beta4 tag. Start it and open psql:

docker run -d --name pg19 \
  -e POSTGRES_PASSWORD=pg \
  postgres:19beta4

docker exec -it pg19 psql -U postgres

Create a table with a primary key. That key isn't decoration: REPACK CONCURRENTLY refuses tables without a primary key or an index-based replica identity.

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY
              PRIMARY KEY,
  customer_id int  NOT NULL,
  note        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, note)
SELECT (random() * 10000)::int, repeat('x', 200)
FROM generate_series(1, 1000000);

Write down the size, then delete nine rows out of every ten and run a plain VACUUM:

SELECT pg_size_pretty(pg_total_relation_size('orders'))
       AS total,
       pg_size_pretty(pg_relation_size('orders'))
       AS heap;

DELETE FROM orders WHERE id % 10 <> 0;
VACUUM orders;

Run the size query again. The numbers barely move. The docs on routine vacuuming explain why: plain VACUUM marks dead space reusable, but "it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained." Deleting by id % 10 leaves a live row on nearly every page, so almost no page at the end of the table ends up empty. You now have a table that's mostly air. pg_total_relation_size counts the heap plus indexes and TOAST, so it's the number your disk alert actually sees.

Watch VACUUM FULL build a lock queue

The damage comes from the queue that forms behind the lock. Open four psql sessions (docker exec -it pg19 psql -U postgres in four terminals).

One ACCESS EXCLUSIVE request at the front of the line, and everything behind it waits.

Session A plays the long-running report that touched the table and hasn't committed:

BEGIN;
SELECT count(*) FROM orders;
-- leave this transaction open

Session B asks for the rewrite. It hangs, waiting for A's lock:

VACUUM FULL orders;

Session C is an ordinary read from your app. It hangs too:

SELECT count(*) FROM orders;

Session D looks at the lock table:

SELECT l.pid, l.mode, l.granted,
       left(a.query, 40) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted DESC, l.pid;

Expect three rows. Session A holds an AccessShareLock with granted = t. Session B wants an AccessExclusiveLock and has granted = f. Session C wants an AccessShareLock, the weakest lock there is, and also has granted = f, because it conflicts with the exclusive request queued ahead of it. The explicit locking chapter lists VACUUM FULL under ACCESS EXCLUSIVE, which conflicts with every other mode.

That third row is what takes your app down: one open transaction plus one VACUUM FULL turns every new SELECT into a waiter. Type COMMIT; in session A. B runs the full rewrite, and C only returns once B finishes, so on a 19 GB table C waits for the entire rewrite.

Run REPACK CONCURRENTLY while inserts keep landing

The table only has about 100,000 live rows now, so a repack may finish before you can watch it. Rerun the INSERT ... generate_series with a few million rows if you want time to see the phases. Then re-bloat it. Updating every row leaves one dead version per live row:

UPDATE orders SET note = repeat('y', 200);

In a second terminal, start a writer that commits a row roughly every 100 ms, plus the docker exec overhead:

while true; do
  docker exec pg19 psql -U postgres -qc \
    "INSERT INTO orders (customer_id, note)
     VALUES (42, 'during repack')"
  sleep 0.1
done

Back in psql, run the repack. It has to be a top-level statement; the REPACK reference says CONCURRENTLY can't run inside a transaction block.

REPACK (VERBOSE, CONCURRENTLY) orders;

From another session, watch it move through its phases with the new pg_stat_progress_repack view:

SELECT phase, heap_blks_scanned, heap_blks_total,
       heap_tuples_inserted, heap_tuples_updated
FROM pg_stat_progress_repack \watch 1

The phases include seq scanning heap, writing new heap, catch-up ("processing the DML commands that other transactions executed during any of the preceding phases"), and swapping relation files. Rerun the pg_locks query during the copy: per the commit message, the repack backend should be holding ShareUpdateExclusiveLock rather than AccessExclusiveLock, and your writer's inserts keep committing.

The copy runs under a weak lock; the exclusive lock covers only the file swap.

When it finishes, stop the loop and check two things:

SELECT count(*) FROM orders
WHERE note = 'during repack';

SELECT pg_size_pretty(pg_total_relation_size('orders'))
       AS total;

Every row the loop inserted while the repack ran should be there, and the total should drop back toward the live data size. ComputingForGeeks ran a similar test on Beta 2: an insert issued two seconds into a concurrent repack "returned immediately, the REPACK completed after it, and the row survived the table swap," and the heap came back at half its bloated size.

Now repeat the session A trick: open a transaction, read orders, leave it open, and start REPACK (CONCURRENTLY). The copy and catch-up proceed, then the swap waits: as pgEdge points out, a transaction that touched the table before the repack started holds an ACCESS SHARE lock that blocks the swap until it finishes. While it waits, run session C's SELECT count(*) FROM orders; and the pg_locks query from session D, and check whether the read queues behind the repack's AccessExclusiveLock request the way it queued behind VACUUM FULL. The REPACK docs say the ACCESS EXCLUSIVE lock "is only acquired to swap the table and index files." It is still acquired, though, so a forgotten BEGIN in someone's psql can hold up the swap.

The limits, from the docs and the commit

The REPACK page lists when CONCURRENTLY refuses to run:

• The relation isn't a table (materialized views are out).
• The table is UNLOGGED.
• The table is partitioned.
• The table is a system catalog or a TOAST table.
• The table uses a non-heap access method.
• The table is marked user_catalog_table.
• The table has no primary key or index-based replica identity.
• You're inside a transaction block.
• max_repack_replication_slots doesn't allow for one more replication slot.

Each concurrent repack needs a replication slot. The original commit called slot scarcity a loose end, and a follow-up commit the next day added max_repack_replication_slots so repack gets its own pool. The default is 5, and it "can only be set at server start." You don't need to change wal_level either: for servers at wal_level = replica, the release notes add "automatic enablement of logical replication when needed," and the ComputingForGeeks test ran "at the stock wal_level = replica, no configuration change required."

Concurrency between repacks is still a question mark. The commit message said that, because of how the historic snapshot is set up, "only one REPACK process can be running at any one time on the whole system." CYBERTEC's later write-up says "only one REPACK CONCURRENTLY can be running per database at the moment." Neither the release notes nor the REPACK reference mention lifting either limit, so plan for one at a time and test it on the build you run. The same commit also flagged a deadlock risk in the final lock upgrade, which can abort the operation.

Four more from the docs. It isn't MVCC-safe: per the MVCC caveats page, a transaction whose snapshot predates the commit and that never touched the table may see it as empty, so its results can disagree with other tables. It needs extra temporary space, since changes made during the copy are spooled to a temp file, on top of the disk for the full new copy. And rows inserted after the repack started aren't sorted by the index, and DDL on the table during the run can make it fail.

Should you use it yet

Not in production today. As of October 15, 2026, the newest PostgreSQL 19 build on postgresql.org is Beta 4, released September 24. The Beta 4 announcement says a release candidate "should occur in early October" and GA "may also occur in October," and the roadmap lists October 2026. That same announcement lists "several fixes for the new REPACK command, including crashes," and it reverted several other features, including SQL/PGQ. REPACK stayed in, but it's still settling.

What you can do now: run the Docker test above against a copy of your most bloated table, check that every table you'd want to repack has a primary key, and look for anything that keeps transactions open for hours. On 18 and earlier, pg_repack is still the online option.

---

If this was useful

Bloat is a maintenance bill you sign up for the day you pick a store, and most teams only read it when the disk alert fires. I wrote the Database Playbook for the decision before that: choosing the right store for the system you're building, with its operational costs in view. If this post had you auditing your Postgres tables, it's the book for the next schema you design.