PostgreSQL 19's REPACK (CONCURRENTLY): Online Table Rewrites, and Where the Limits Are

7 Eki

5 dakikada okunur

Every PostgreSQL team eventually meets the same problem: a table that carries far more disk space than its live rows need. Plain VACUUM does not fix it, VACUUM FULL fixes it but locks the table, and the usual way out has been third-party tooling.

PostgreSQL 19 adds a built-in answer: a new REPACK command with a CONCURRENTLY option. This post walks through the problem on a reproducible example, then through what the official documentation says REPACK does, and, just as important, what it cannot do.

Status note: PostgreSQL 19 is still in beta (Beta 4 was released on September 24, 2026; a release candidate is planned for early October, and GA may follow in October). Details can still change, and beta releases are not for production. Early beta coverage also mentions features that were later reverted, so treat the official release notes and docs as the source of truth. Everything about REPACK below comes from the 19beta4 documentation.

The problem, reproduced

This works on any recent PostgreSQL version. Create a table, load it, then create bloat by rewriting every row twice and deleting 90% of them:

sql

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id int  NOT NULL,
    payload     text NOT NULL
);

INSERT INTO orders
SELECT i, (i % 10000)::int, repeat('x', 200)
FROM generate_series(1, 500000) AS i;

SELECT pg_size_pretty(pg_table_size('orders')) AS size_after_load;

UPDATE orders SET payload = repeat('y', 200);
UPDATE orders SET payload = repeat('z', 200);
DELETE FROM orders WHERE id % 10 <> 0;

VACUUM orders;
SELECT pg_size_pretty(pg_table_size('orders')) AS size_after_vacuum;

On PostgreSQL 16.15 (your numbers will differ), the table was 118 MB after loading and 355 MB after the updates, the delete, and a plain VACUUM, even though only 50,000 of the original 500,000 rows were still alive. VACUUM made the dead space reusable, but it did not give it back.

VACUUM FULL does give it back. On the same table it brought the size down to 12 MB. The price is its lock: while it ran, a concurrent query failed.

text

SET lock_timeout = '100ms';
SELECT count(*) FROM orders;

ERROR:  canceling statement due to lock timeout

Without the lock_timeout, that SELECT would simply have waited, and so would every other query on the table, behind an ACCESS EXCLUSIVE lock held for the whole rewrite. That wait is how maintenance turns into an incident.

What PostgreSQL 19 adds

According to the release notes, REPACK is a new command that "replaces" VACUUM FULL and CLUSTER by unifying them under one name. The two old commands are retained for compatibility. The synopsis from the beta documentation:

REPACK [ ( option [, ...] ) ] [ table_and_columns [ USING INDEX [ index_name ] ] ]
REPACK [ ( option [, ...] ) ] USING INDEX

-- option: VERBOSE [ boolean ] | ANALYZE [ boolean ] | CONCURRENTLY [ boolean ]

Without USING INDEX, REPACK rewrites the table in physical order, which is the VACUUM FULL use case. With USING INDEX, it also reorders the rows by that index, which is the CLUSTER use case. A few examples, taken from the docs:

REPACK employees;                                  -- plain rewrite (ACCESS EXCLUSIVE for the duration)
REPACK employees USING INDEX employees_ind;        -- rewrite + cluster by index
REPACK (CONCURRENTLY) employees USING INDEX;       -- concurrent mode, same index as before
REPACK (ANALYZE, VERBOSE) cases (district, case_nr);

Without CONCURRENTLY, behavior matches what you know: an ACCESS EXCLUSIVE lock on the table until the rewrite finishes.

How CONCURRENTLY works

The docs describe the mechanism in three steps:

REPACK copies the live tuples into a new file, and builds a new file for each index, while other transactions keep using the table.
Changes made during that copy are captured with logical decoding and applied to the new files.
Only to swap the old and new files does REPACK take the ACCESS EXCLUSIVE lock.

The docs say the lock is "typically held only for the time needed to swap the files, which should be pretty short", with one explicit caveat: if a lot of data changes accumulated while REPACK was waiting for the lock, those changes are processed while the lock is held, and "the time might still be noticeable". In other words, a very write-heavy table is the case to watch.

The limits that matter

This is the part worth reading before you plan around the feature. According to the beta docs, CONCURRENTLY cannot be used when:

the relation is not a table (for example a materialized view);
the table is UNLOGGED;
the table is partitioned;
the table has no primary key and no index-based replica identity;
the table is a system catalog or a TOAST table, or uses a non-heap access method;
you are inside a transaction block;
max_repack_replication_slots does not allow one more replication slot.

Three more points from the same page deserve their own mention:

It is not MVCC-safe. The docs carry an explicit warning and point to the MVCC caveats section (13.6) of the manual. Read that section before using it on a table where long-running snapshots matter.
It can fail. REPACK "might fail to complete due to DDL commands executed on the table by other transactions during the repacking."
It does not order rows inserted after it started, so a USING INDEX result is only fully ordered as of the start.

Also note the scope rules: the no-argument form (REPACK;, all tables) is not allowed together with CONCURRENTLY, and repacking a partitioned table without CONCURRENTLY repacks each of its partitions.

Planning the resources

A rewrite needs room for the new copy:

Free disk space of at least the table size plus its indexes. If the planner chooses a sequential scan followed by a sort, the peak can reach double the table size plus the indexes. Temporarily setting enable_sort = off disables that choice if the space is not available.
CONCURRENTLY can need a bit more temporary space, because changes made during the copy are stored in a temporary file until they can be applied.
A reasonably large maintenance_work_mem, as for other maintenance operations.
Because CONCURRENTLY relies on logical decoding, check your server settings for it (wal_level / effective_wal_level and max_repack_replication_slots in the beta docs).

For visibility, each REPACK reports progress in the new pg_stat_progress_repack view. Two other 19 changes help when you are watching for lock trouble: the release notes list a new pg_stat_lock view with per-lock-type statistics, and log_lock_waits is now enabled by default.

A checklist before you rely on it

Confirm the table is eligible: ordinary heap table, logged, not partitioned, with a primary key or index-based replica identity.
Check free disk against table size plus index sizes, and double the table size if a sort is likely.
Read the MVCC caveats section and decide whether they matter for your workload.
Plan for failure: DDL on the table during the repack can abort it, so schedule it away from migrations.
Test on a copy with realistic write traffic and watch lock waits, especially around the final swap.

What this post did not measure

The bloat numbers above come from PostgreSQL 16.15. I did not benchmark REPACK (CONCURRENTLY) itself, because it requires a PostgreSQL 19 beta build, and the lock-duration claims here are the documentation's, not measurements. If you run it on a beta, the most informative test is the one you design: concurrent writes against a table of your real shape, with lock waits observed during the final swap.

References

Disclosure: this article was drafted with AI assistance. The SQL setup and the PostgreSQL 16 measurements were run before publication, and the REPACK details were checked against the PostgreSQL 19beta4 documentation.