Saturday, 21 November 2026

Trusted News Discovery – Powered by AI and Verified by Humans

PostgreSQL 19’s new repack command explained: how it shrinks bloated tables online, and its limits

PostgreSQL 19's REPACK command rebuilds bloated tables while queries keep running, but it needs free disk for a full copy and has a 104 million row limit.

Produced with AI assistance and reviewed by the Badiyaa Editorial Desk. How we work.

Verified by humans. Checked by machines. Open to you.

Grok Fact CheckCheck it yourself in one tapVerify with Grok ↗Ask @grok on X ↗
The read and write head of a hard disk drive resting over a shiny platter, an illustrative image of data storage.
Image: Matthew Field, CC BY-SA 3.0 via Wikimedia Commons

PostgreSQL 19’s new REPACK command finally gives the popular open-source database a built-in way to shrink a bloated table while the application keeps reading and writing to it. But the official documentation also comes with a list of catches that anyone planning to run it on a busy production table should read first.

Badiyaa covered the overall release in our PostgreSQL 19 launch story. This piece zooms in on how REPACK actually works, how much disk it needs, and where it refuses to run.

Why Postgres tables bloat in the first place

Postgres never overwrites a row in place. As the PostgreSQL 19 manual’s chapter on routine vacuuming explains, an UPDATE or DELETE leaves the old row version behind, because another transaction might still need to see it. That design, known as multiversion concurrency control and first described in research from 1978 and 1981, is what lets readers and writers avoid blocking each other.

The leftovers are called dead tuples. Regular VACUUM, usually run automatically by the autovacuum daemon, marks their space as reusable, but it generally does not hand that space back to the operating system. So a table that once held a huge burst of updates can stay physically large long after the data has shrunk. Until now, the fix meant VACUUM FULL or CLUSTER, and both hold an ACCESS EXCLUSIVE lock that blocks every query on the table until they finish.

What REPACK does differently

According to the official REPACK reference page, the command copies the live rows into a brand-new file, builds fresh indexes, swaps the files and deletes the old ones. Add USING INDEX and it also sorts the rows by that index, which is the job CLUSTER used to do.

The headline trick is the CONCURRENTLY option. In that mode, most of the work runs under a weaker SHARE UPDATE EXCLUSIVE lock, so normal reads and writes continue. Changes made in the meantime are captured through logical decoding and replayed onto the new copy. The full exclusive lock is only taken at the very end, for the file swap, so it is typically held briefly.

In practice, an administrator would type REPACK (CONCURRENTLY) followed by the table name. Each running REPACK reports its progress in a new pg_stat_progress_repack view.

Share this story

The catches, in plain terms

  • You need room. REPACK builds a temporary copy of the table and every index, so free disk space must be at least the table size plus the index sizes. A 200 GB table with 80 GB of indexes needs roughly 280 GB free, and more if a USING INDEX sort spills to a temporary file.
  • There is a ceiling. In concurrent mode, if more than about 104 million rows are updated or deleted while REPACK runs, the command fails.
  • Old snapshots see nothing. The documentation warns that the concurrent mode is not MVCC-safe: a transaction using a snapshot taken before the repack began will see the table as empty after it commits.
  • Not every table qualifies. CONCURRENTLY is refused for partitioned tables, unlogged tables, materialized views, system catalogs, tables without a primary key or index-based replica identity, and runs inside a transaction block.
  • It is not a wraparound fix. The manual says VACUUM remains the right tool to prevent transaction ID wraparound, since REPACK rewrites everything and can fail late in the process.

When to reach for it

The vacuuming chapter makes the bigger point clearly: frequent ordinary VACUUM runs are the healthy default, and shrinking a table that will just grow again is pointless. REPACK is the tool for the exception, a table that ballooned after a mass delete or a runaway update job. The difference in PostgreSQL 19 is that fixing that exception no longer means scheduling downtime.

For another look at squeezing more out of existing hardware, see Badiyaa’s explainer on Linux memory compression with zswap and zram.

Sources

Image: Matthew Field, CC BY-SA 3.0 via Wikimedia Commons

This article was produced with AI assistance and reviewed by our Editorial Desk. How we work.

Spot an error? Help verify this story

Join our Reader Verifiers. Every report is read by an editor.

Grok Fact CheckCheck it yourself in one tapVerify with Grok ↗Ask @grok on X ↗

Verified by humans. Checked by machines. Open to you.