Skip to main content
Postgres 19 REPACK vs pg_repack vs VACUUM FULL: Shrinking Bloated Tables Without Downtime

Postgres 19 REPACK vs pg_repack vs VACUUM FULL: Shrinking Bloated Tables Without Downtime

KU
· Founder & Chief Editor
• 7 min read•

PostgreSQL 19 adds REPACK, which replaces VACUUM FULL and CLUSTER, and its CONCURRENTLY option rewrites a bloated table while reads and writes continue. How it works, how to size and run it safely, and when pg_repack is still the right tool.

Key takeaways

  • PostgreSQL 19, released November 19, 2026, adds REPACK to replace VACUUM FULL and CLUSTER.
  • With CONCURRENTLY it rewrites a table under a SHARE UPDATE EXCLUSIVE lock, captures concurrent changes through logical decoding and takes ACCESS EXCLUSIVE only for the final swap.
  • It needs a primary key or index-based replica identity, a free repack slot and disk for a full copy.
  • Use it on 19; keep pg_repack on older versions.

On PostgreSQL 19, use REPACK (CONCURRENTLY) instead of pg_repack or VACUUM FULL to shrink a bloated table. It is built in, needs no extension or triggers, and blocks reads and writes only for the final file swap. On 18 and earlier, pg_repack stays the answer; its latest release, 1.5.3, does not list 19 as supported.

Verified against

PostgreSQL 19 documentation for REPACK, replication settings, WAL and progress reporting; the PostgreSQL 19 release notes and announcement of November 19, 2026; and the pg_repack documentation and 1.5.3 tag. Checked November 2026.

What REPACK replaces

Plain VACUUM marks dead rows as reusable, but it returns space to the operating system only by truncating empty pages at the end of the table. Getting the space back meant rewriting the table: VACUUM FULL or CLUSTER, both of which hold an ACCESS EXCLUSIVE lock for the whole run, so even SELECT waits. On a large table that is an outage.

PostgreSQL 19 folds both into one command. REPACK rewrites the table and its indexes into new files with no dead space, and REPACK ... USING INDEX also orders rows by an index, which is what CLUSTER did.

The headline is the CONCURRENTLY option. The copy runs under SHARE UPDATE EXCLUSIVE, which lets reads and writes continue but blocks DDL, VACUUM and ANALYZE on that table. Changes made during the copy are captured through logical decoding and replayed. ACCESS EXCLUSIVE is taken only at the end, to apply the last changes and swap the files, so the lock is typically held briefly.

Lock timeline: VACUUM FULL, CLUSTER and plain REPACK hold ACCESS EXCLUSIVE for the whole rewrite; REPACK (CONCURRENTLY) holds SHARE UPDATE EXCLUSIVE through initializing, copying and catch-up, replays concurrent changes captured by logical decoding, and takes ACCESS EXCLUSIVE only for the brief swap

Measure before you repack

Repack a table because it is measurably bloated, not on a schedule. The pgstattuple extension measures it:

SQL
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Exact dead-tuple stats, estimated free space
SELECT table_len, dead_tuple_percent, approx_free_percent
FROM pgstattuple_approx('public.orders');

-- Size the job: table plus all indexes
SELECT pg_size_pretty(pg_total_relation_size('public.orders'));

If dead and free space are a small share of the table, a rewrite buys little. If they are most of it and normal vacuums do not shrink it, it is a candidate. Fix the cause too: a table that bloats again in a month needs autovacuum tuning, not a monthly rewrite. If only the indexes are bloated, REINDEX CONCURRENTLY is cheaper; our guide to index bloat covers that path.

Running it safely

SQL
-- Session settings for a big rewrite
SET maintenance_work_mem = '2GB';
SET lock_timeout = '5s';

REPACK (CONCURRENTLY, VERBOSE, ANALYZE) public.orders;

-- From another session: watch progress
SELECT phase, heap_blks_scanned, heap_blks_total,
       heap_tuples_inserted, heap_tuples_updated, heap_tuples_deleted
FROM pg_stat_progress_repack;

Four requirements decide whether this runs at all:

  • Keys. The table needs a primary key or an index-based replica identity.
  • Slots. Each concurrent repack uses a replication slot from max_repack_replication_slots, which defaults to 5 and can only be set at server start. Logical decoding needs wal_level of at least replica; at replica, 19 raises effective_wal_level to logical automatically while a logical slot exists, so no restart is needed.
  • Disk. REPACK builds a full copy of the table and every index before dropping the old files, so free space must at least equal table plus index size. Captured concurrent changes are also stored on disk until applied.
  • Shape. CONCURRENTLY refuses partitioned, unlogged and non-heap tables, materialized views and system catalogs, and cannot run inside a transaction block. Repack partitions one at a time instead.

Set lock_timeout so the final swap does not sit in the lock queue behind a long transaction while every new query queues behind it. On timeout the statement aborts and the copy work is lost, so retry off-peak.

REPACK (CONCURRENTLY)pg_repackVACUUM FULL / CLUSTER
AvailablePostgreSQL 19+Extension; release 1.5.3 covers 9.5 to 18Built in, all versions
Lock during copySHARE UPDATE EXCLUSIVESHARE UPDATE EXCLUSIVE (1.5.3), brief ACCESS EXCLUSIVE at start and endACCESS EXCLUSIVE throughout
Change captureLogical decodingTrigger into a log tableNone, table locked
Key requirementPrimary key or index replica identityPrimary key or unique index on a NOT NULL columnNone
Partitioned tablesPer partitionVia --parent-tableYes, blocking
Extra diskTable plus indexesAbout twice table plus indexesTable plus indexes

Where it breaks

The docs warn that REPACK CONCURRENTLY is not MVCC-safe. A transaction with a snapshot older than the repack commit sees the table as empty, if it had not touched the table before the repack started. One that already read the table holds an ACCESS SHARE lock, which blocks the swap instead. pg_dump locks its tables at start, so a running dump delays the swap rather than reading zero rows. The exposure is repeatable-read reports that reach this table late.

It can stall early: the initializing phase waits for every transaction holding a transaction ID to end, including another concurrent repack. It can also fail late. DDL on the table from another session can stop it completing, and more than about 104 million rows updated or deleted during the run makes it fail. The docs advise against REPACK for wraparound protection: it takes much longer than VACUUM and can fail after most of the work. It also counts as one long transaction that stops VACUUM cleaning other tables, so watch pg_stat_activity.backend_xid on very large tables.

Questions people ask

Does REPACK replace pg_repack?

On PostgreSQL 19, for most tables, yes. It does the same job without an extension or triggers. pg_repack still matters on PostgreSQL 18 and earlier and on managed services that have not shipped 19. A table with only a unique NOT NULL index works with pg_repack; for REPACK, first set it as the replica identity with ALTER TABLE ... REPLICA IDENTITY USING INDEX.

Is REPACK CONCURRENTLY really online?

Mostly. Reads and writes continue during the copy, the final swap takes a brief ACCESS EXCLUSIVE lock, and it is not MVCC-safe.

Are VACUUM FULL and CLUSTER deprecated?

Both remain for compatibility. The release notes describe REPACK as replacing them, and the 19 VACUUM reference marks the FULL option deprecated. Progress for all three appears in pg_stat_progress_repack.

The decision, concretely

  • On 19, table has a primary key, disk headroom exists? REPACK (CONCURRENTLY) off-peak, with lock_timeout set.
  • On 18 or earlier, or managed Postgres without 19? pg_repack, with free space about twice the table plus indexes.
  • Only indexes bloated? REINDEX CONCURRENTLY, no table rewrite.
  • Small table, maintenance window available? Plain REPACK, which skips the slot and replica-identity requirements.
  • Table bloats again within weeks? Tune autovacuum first.

The call we'd make

Once you are on PostgreSQL 19, replace pg_repack in runbooks with REPACK (CONCURRENTLY) and drop the extension when nothing else needs it. Measure with pgstattuple_approx first, keep free space above table plus indexes, and set lock_timeout. The caveat: keep repeatable-read reports off the table around the swap, because an older snapshot that reads it afterwards sees it empty.

Sources

Explore topics:DevOps
React
Share this post
Cite this article
Plain text

Kiril Urbonas (2026). Postgres 19 REPACK vs pg_repack vs VACUUM FULL: Shrinking Bloated Tables Without Downtime. DevOpsNess. https://www.devopsness.com/blog/postgres-19-repack-vs-pg-repack-vacuum-full

Markdown

[Postgres 19 REPACK vs pg_repack vs VACUUM FULL: Shrinking Bloated Tables Without Downtime](https://www.devopsness.com/blog/postgres-19-repack-vs-pg-repack-vacuum-full) (Kiril Urbonas, DevOpsNess, 2026-11-20)

KU

Kiril Urbonas

Founder & Chief Editor

608 articles
View all articles by Kiril Urbonas

You might have missed

Evergreen posts worth revisiting.