Mustafa ERBAY

Posted on Sep 9 Originally published at mustafaerbay.com.tr

Reclaiming Bloat Without the Lock: PostgreSQL 19 and REPACK

#postgres #database #operations #performance

Everyone who runs PostgreSQL eventually faces the same strange decision: you know the table has bloated, you want the disk space back, and the only built-in tool you have locks the table from end to end. VACUUM FULL really does return the space — in exchange, nobody reads from or writes to that table until it finishes. So "I'll clean up that 400 GB table" translates, in practice, to "nobody places an order on Saturday night."

The escape hatch has been the same for years: install the pg_repack extension and have the table rewritten online. It works, it's good, but it lives outside the core — you track version compatibility, you talk someone into granting install permission, and if you're on a managed cloud database you plead with your provider's extension list.

PostgreSQL 19 brings this inside. The new command is called REPACK and it ships with a CONCURRENTLY option. Below I'll go through what we gain, which fine print you must not skip, and why writing this into your maintenance plan today is premature — for that last point I have evidence from this very week.

First, the naming mess ends

The release note is unambiguous: the REPACK command replaces VACUUM FULL and CLUSTER. The reasoning is stated openly — the two commands did similar things but under confusing names, so they were unified as REPACK. Are the old ones removed? No; they're retained for compatibility.

That detail matters, because it answers the question "will my maintenance scripts break when I move to PostgreSQL 19?" They won't. Your cron job that says VACUUM FULL keeps working. But now you work knowing both were two faces of the same act: rewriting the table into a new file from scratch. CLUSTER's "order by an index" behaviour isn't lost either — it moves into REPACK as the USING INDEX clause.

REPACK employees;
REPACK employees USING INDEX employees_ind;
REPACK (CONCURRENTLY) employees USING INDEX;
REPACK (ANALYZE, VERBOSE) cases (district, case_nr);

The syntax already feels familiar. The real news is the option on the third line.

CONCURRENTLY: the lock doesn't disappear, it shrinks

A common misreading needs cutting off right away. CONCURRENTLY does not mean "no lock." The documentation says so plainly: the ACCESS EXCLUSIVE lock is only acquired to swap the table and index files, so it is typically held only for the time needed to swap them — "pretty short," in the documentation's words.

On the classic path the lock is held while the entire table is rewritten; on a 400 GB table that means hours. With CONCURRENTLY the rewriting happens while traffic keeps flowing, and the lock only lands at the very end, when the files are put in place.

But "short" is not a guarantee, and the documentation says so immediately afterwards: if too many data changes were made to the table while REPACK was waiting for the lock, those changes must be processed just before the files are swapped — while the ACCESS EXCLUSIVE lock is held — and the time may become noticeable. So lock duration isn't a fixed property; it's a function of your table's write volume and how long the command waited for the lock. On a write-heavy table, don't plan around "it'll take a second anyway."

Meanwhile the changes still arriving from production must not be lost; that's where the machinery lives. REPACK uses a replication slot for this — max_repack_replication_slots controls how many it may use, the default is 5, and it can only be set at server start. So if you want to change that parameter you need a restart; don't discover this on the night you first try the command.

You don't have to guess whether the process really goes through these stages; PostgreSQL 19 shows you. The pg_stat_progress_repack view holds a row for every running REPACK and reports the phases by name: initializing, seq scanning heap, index scanning heap, sorting tuples, writing new heap, catch-up, swapping relation files, rebuilding index, performing final cleanup. The older pg_stat_progress_cluster keeps being populated for compatibility.

Being able to see which phase a maintenance command sits in looks like a small detail, but half the diagnosis lives there. The documentation defines catch-up precisely: in that phase REPACK CONCURRENTLY is processing the DML commands other transactions executed during any of the preceding phases. So a REPACK lingering there is telling you that write volume is keeping pace with the copy. A long-lived transaction's lock, by contrast, stalls you somewhere else — in the swapping relation files phase.

Two different diagnoses, two different interventions. Confusing them and hunting for long queries in pg_stat_activity during catch-up is the shortest path to spending half the night in the wrong place.

The fine print: what "not MVCC-safe" means

The documentation drops a one-sentence warning about CONCURRENTLY, and that sentence is the single most important technical detail in this article: with that option, REPACK is not MVCC-safe.

The caveats section explains what that means. TRUNCATE, the table-rewriting forms of ALTER TABLE, and REPACK with CONCURRENTLY fall in this category; after the command commits, concurrent transactions running with a snapshot taken before that commit may see the table as empty.

I stopped and reread that sentence the first time. Empty. Not an error, not a lock wait — zero rows. Your report quietly says "no results" and you go hunting for the reason weeks later.

Before anyone panics, let me write down the protection too: a transaction that accessed the table before the command started holds at least an ACCESS SHARE lock, and that lock blocks REPACK from completing. So transactions that "touched the table first" are safe. The risk sits with long transactions that have taken their snapshot but haven't touched the table yet. A reporting transaction opened in repeatable read, working on other tables for a while and only then reaching for this one, fits that description exactly.

If it were up to me, the rule would be: REPACK ... CONCURRENTLY does not run in a window where long-lived analytical transactions are active. It doesn't abolish the midnight maintenance window; it downgrades it from "nobody can write" to "long reports pause." That's still a big win — it's just a different win.

Where it won't work

Here are the situations where CONCURRENTLY can't be used, as they currently stand in the source tree:

The relation is not a table (a materialized view, for instance).
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.
The table's access method is not heap.
The table is declared as a catalog table via the user_catalog_table storage parameter.
The command is running inside a transaction block.
max_repack_replication_slots doesn't allow another slot to be created.

When I first wrote this list it had six items, not nine, because the published beta documentation lists six. The remaining three — materialized views, non-heap access methods and user_catalog_table — landed in the tree on 8 and 9 September. The last of those was committed the morning of the day I wrote this. Further down I'll tell you not to make commitments based on beta documentation; this list is the evidence, because I made exactly that mistake myself.

In practice the two items that will hurt most are partitioned tables and materialized views. On a partitioned table REPACK processes each partition, but not with CONCURRENTLY. The tables that struggle most with bloat are usually the largest ones, and the largest ones are usually partitioned — so the online option is closed exactly where you assumed the new command would help most.

There's one more restriction that isn't in the documentation at all; you only find it by reading the source. wal_level must be at least replica, otherwise REPACK (CONCURRENTLY) errors out immediately. That makes sense, since the mechanism relies on logical decoding. But on bulk-load and ETL servers running with wal_level=minimal, it's one of the surprises waiting for you on the first attempt.

One permission note as well: to repack a table you need the MAINTAIN privilege on it. Good news for anyone trying to stop running maintenance as a superuser.

Don't forget the disk

Like every tool that rewrites a table, REPACK creates the new file while the old one is still there. And not just the table: the documentation notes that temporary copies of each index are created as well, and that at the end the old and new files for the table and all its indexes are swapped and the old ones deleted.

The docs give two scenarios: for an index scan, or a sequential scan without sorting, you need free disk space at least equal to the sum of the table size and the index sizes. For a sequential scan with a sort, the peak temporary space requirement is as much as double the table size, plus the index sizes again.

So cleaning up a 400 GB table means having free space equal to 400 GB plus the size of its indexes — and if the sorting path is chosen, a peak of 800 GB plus indexes. A team walking into bloat cleanup because the disk filled up, only to learn there that the cleanup itself wants serious free space, is a classic bind.

The documentation does leave an escape hatch here: the sorting method is often faster, but if its disk requirement is intolerable you can disable that choice by temporarily setting enable_sort to off. The same paragraph adds a piece of advice — set maintenance_work_mem to a reasonably large value before repacking, though not more than the RAM you can dedicate to the operation. Together those two can close the gap between "my disk isn't big enough" and "I can't do this at all."

Catching bloat before it grows is still the first line of defence, though; I wrote up the reasoning behind my autovacuum settings in the PostgreSQL VACUUM and bloat article, and version 19 invalidates none of it. It just makes the last resort hurt less.

Don't forget ANALYZE afterwards

This is the step the documentation warns about explicitly and the one most often skipped in conversation. Because the planner records statistics about the ordering of tables, it is advisable to run ANALYZE on the newly repacked table; otherwise the planner might make poor choices of query plans.

So performance dropping after a REPACK isn't a bug — it's the expected behaviour if you didn't refresh the statistics. The command's ANALYZE option exists for exactly this:

REPACK (CONCURRENTLY, ANALYZE) orders;

There's a catch, though: the ANALYZE option is currently only supported when a single, non-partitioned table is specified. If you're repacking a partitioned table you'll need to run ANALYZE yourself. A change on 8 September also made REPACK (ANALYZE) rejected inside a transaction block.

Clearing the bloat and wrecking the plans is one of the harder things to explain at four in the morning. Make this the last line of your maintenance script.

Ordering is a one-time act — use it knowing that

The USING INDEX clause inherited from CLUSTER looks tempting: order the table by an index and range queries read fewer pages. True, but the documentation underlines a limit — clustering is a one-time operation. When the table is subsequently updated the changes are not clustered; no attempt is made to store new or updated rows in index order.

With CONCURRENTLY this goes one step further: in that mode REPACK does not try to order rows inserted into the table after the repacking started. So the trade you accept when running online isn't only about lock duration; when it finishes you also hold an unsorted tail as long as the traffic that flowed during the operation.

In practice that means if you treat ordering as a performance strategy, you need to plan on repeating REPACK periodically. The documentation suggests a mitigation too: setting the table's fillfactor below 100% helps preserve the ordering during updates, since updated rows stay on the same page when there's enough space there.

Even so, I generally find ordering not worth the trouble outside tables with low write traffic whose read pattern genuinely leans on range scans. How fast the ordering degrades on a write-heavy table, and how much of your maintenance window repeated repacking consumes, is something to measure on your own table before deciding — it isn't a balance you can settle with a rule of thumb.

Two quiet changes on the autovacuum side

Version 19's maintenance story isn't only REPACK. There are two more additions.

First, autovacuum can now use parallel workers. The ceiling is set by autovacuum_max_parallel_workers and tuned per table with autovacuum_parallel_workers. The critical point: the default is 0, meaning off. You won't get faster on your own the day you upgrade; this is a feature you turn on deliberately, and it's further limited by max_parallel_workers.

Second, a scoring system now controls the order in which autovacuum processes tables. The weights are set with autovacuum_freeze_score_weight, autovacuum_multixact_freeze_score_weight, autovacuum_vacuum_score_weight, autovacuum_vacuum_insert_score_weight and autovacuum_analyze_score_weight; all default to 1.0.

I think the second one is a quiet but real win. Letting autovacuum say "this one first" among hundreds of queued tables reduces the risk that a table whose XID age is entering dangerous territory sits waiting in line — I wrote about what stands at the end of that queue in the XID wraparound article, and the unstoppability of that clock hasn't changed. The side racing it has simply been given a say.

And now the real warning: all of this is still beta

If you've read this far you're probably thinking of writing REPACK into your next maintenance plan. Hold on a moment.

PostgreSQL 19 is not released as these lines are written. The project roadmap plans the release for September 2026; as of 13 August 2026 what we have is Beta 3. The documents I quoted above say it in their own header: "PostgreSQL 19beta3 Documentation".

If you're inclined to say "beta is practically final," let me offer an example from this week. On 7 September 2026, SQL/PGQ property graph support was reverted out of 19. A single commit — but the list of commits it reverts runs 49 lines long and deletes roughly 15,900 lines: the feature plus every fix layered on it since the first commit on 16 March. The revert commit itself gives no reason; it lists the commits and links to the pgsql-hackers discussion.

To find the reasoning you have to look at the other revert in the same pre-RC clearing-out: when ALTER TABLE ... MERGE/SPLIT PARTITION(S) support was removed on 26 August, the commit message stated that the feature was being reverted due to multiple design issues that were too late to address in this release cycle. Different developers, twelve days apart, the same calendar pressure.

The most instructive part is this: at the moment of the revert, the published beta documentation still described property graphs, because those docs had been built from the beta3 snapshot. In other words, a feature standing in the official documentation may not be in the tree at that moment.

The rule I take from it: beta documentation is a first-class source for learning, not for making commitments. There's no sign REPACK is going anywhere — the command's code sits in the tree and isn't on the reverted list. But the difference between "it isn't going away" and "it's definitely shipping, I've planned around it" is one you can ask the graph projects that were waiting on PGQ this week.

A practical decision frame

What to do today:

Take an inventory of your bloat. Which table has swollen how much, which ones are partitioned, which lack a primary key — that list is useful before 19 arrives, and turns straight into an actionable plan once it does.
Compute your free disk space now. If you don't have room equal to your largest table plus its indexes, REPACK being online won't save you.
Know your long transactions. If you're going to use CONCURRENTLY, the definition of your maintenance window changes: write traffic doesn't stop, long reports do.
Check your wal_level. If you're running minimal, the online option will fail on the very first command.
Put ANALYZE at the end of the maintenance script. On a partitioned table the command's own ANALYZE option won't work; you'll be running it by hand.
Try it on Beta 3 in a test environment, don't write it into production. It's the right time to get to know the command; too early to promise anything.
Reread the release notes when the release lands. Don't assume every item you saw in beta survives to GA — the restriction list above is a fresh example.

What actually changed

REPACK doesn't technically bring a new capability; pg_repack has done this job for years. What changed is where the capability lives. Online bloat cleanup now sits somewhere you can reach without discussing install permissions, extension versions and provider lists — inside the core.

Its real value, I think, is neither for large teams nor for the one-person shop that needs nobody's approval to install an extension. It's for the mid-sized organisations that have been sentenced to midnight VACUUM FULL for years because they couldn't install extensions. For them, this is a maintenance night struck off the calendar.

Just wait for the release to actually ship before you put it on the calendar.

DEV Community