VACUUM vs VACUUM FULL vs pg_repack: Getting Disk Back, Holding Off Wraparound, and Keeping the Planner Honest

TL;DR: A table that only grows is not "postgres doing something weird" — it's MVCC, working as designed. VACUUM frees space inside the file but almost never shrinks it. VACUUM FULL shrinks it because it rewrites the whole table — but it holds ACCESS EXCLUSIVE for the entire rewrite, which is an outage. pg_repack performs the same rewrite and holds ACCESS EXCLUSIVE for seconds at the start and end, letting writes continue during the copy. The decision isn't "which tool" — it's "can this run online or not", and everything else is locking. This post covers the physics, how to measure bloat cheaply (heap and TOAST separately), the capacity math so you don't fill the disk mid-copy, and the operational rules I paid for with a real incident. Two of VACUUM's four jobs never make it into the "which tool" arguments, and they matter more: the anti-wraparound FREEZE that protects the xid horizon, and the ANALYZE statistics pass that keeps the planner honest. Both get their own sections here, with the exact checks and commands.

Let me start with the scene: disk at 83% with 36 hours of retention. A workflow-history table had grown to 171 GB while holding maybe 30–60 GB of live data. The autovacuum log said pages: 0 removed every single night. The team was quietly confident that "retention would drain it." It never did — the file can't shrink on its own, and here's exactly why.

This is the post I wish I'd had before that week: the three ways to reclaim disk from PostgreSQL, what each one actually does to the bytes on disk, when to reach for each, and the operational rules that turn pg_repack from a scary tool into a scheduled, watched, low-risk job.

Part 1 — Why a Postgres file only grows

MVCC never edits in place

Every UPDATE writes a new version of the row and marks the old one dead. Every DELETE just marks its row dead. Nothing is physically removed at the moment of the statement — a reader with an older snapshot may still need the old version. That is the entire reason reads never block writers on Postgres, and it is also the origin of all bloat.

One nuance that matters when the row is mostly a large blob: an UPDATE that touches no indexed column produces a heap-only-tuple (HOT) chain in-page — no index entry, no new page, until chain pruning. It's the non-HOT update storms that grow files.

There's a second clock that never rewinds

Files aren't the only thing that only grows. Every transaction burns a transaction ID (an xid), PostgreSQL compares those IDs to decide what's visible, and the ID space is 32-bit. Old IDs have to stay comparable forever, so the counter can't be rewound — it wraps at 2^32 with a 2-billion-transaction safety horizon. This clock can only be reset by marking old tuples permanently visible, which is what freezing does: VACUUM (FREEZE)'s entire reason to exist, and it has nothing to do with disk. File it next to MVCC — it's the second rent you're paying — and Part 3 is where the collector for it lives.

VACUUM reclaims space inside the file, not from the file

(Auto)vacuum walks the table, removes dead tuple versions that no snapshot can see anymore, and records the freed bytes of each page in the Free Space Map (the _fsm fork). Future inserts consult the FSM and reuse that space. The file size does not change.

This is the sentence to understand before anything else:

A table with 64% free space and a table with 0% free space look identical to df.

The one case where the file shrinks

At the end of a vacuum, if the trailing pages of the file are completely empty — not one live tuple — vacuum takes a brief ACCESS EXCLUSIVE lock and truncates them (vacuum_truncate, on by default). Two gotchas:

• Any live tuple on the last page and nothing is truncated.
• Under load the truncation is skipped (lock conflict), not queued. So "0 removed" night after night can be a lock artifact, not proof of a non-empty tail.

Inserts are deliberately scattered

The FSM search isn't "lowest free page first". Each FSM page remembers the last slot it handed out, and the next search starts after it, so concurrent inserters don't fight over one page. In a table with steady insert+delete churn, live tuples end up everywhere, including the tail — so the truncation rule above never fires. This is why "wait and let retention drain it" does not work and never will.

TOAST is just another heap

A value whose row exceeds ~2 KB gets compressed and split into chunks stored in pg_toast.pg_toast_<oid>, with a btree on (chunk_id, chunk_seq). Rules 1–3 apply to the toast heap verbatim — and on a blob-heavy table the toast is usually where the bytes actually live. On the table in my incident, the toast was 162 GB of the 171 GB. Vacuum the main table all you want; the toast behaves like a second table that also needs measuring and rewriting.

Indexes bloat the same way, worse

A btree never gives pages back either. Delete-heavy tables leave half-empty leaf pages, and a "delete everything older than 36 hours" pattern is the worst case. In the same incident, one visibility table had 16.5 GB of indexes for 221 thousand rows — and part of that was structural (generated-column btree per search attribute, and Postgres btrees index NULLs, so every row physically occupies space in every attribute index whether the attribute is set or not), part of it was pure dead-entry bloat.

Definition used everywhere below: bloat = file size − live data. It costs disk, it costs cache (empty pages are still read by seq scans and by vacuum), and it costs locality.

Part 2 — The three tools, compared

The load-bearing realization: VACUUM FULL and pg_repack are mechanically the same operation — copy live rows into a fresh file, rebuild indexes, swap file identities, unlink the old bytes. The only difference is locking. VACUUM FULL refuses every other connection for the whole duration; pg_repack shrinks that window to seconds at the two edges and lets writes keep flowing through a trigger in the middle.

So "VACUUM vs VACUUM FULL vs pg_repack" is really two questions, asked in order:

1. Is this space stranded, or just in the wrong place? (Is the bloat inside the file that vacuum can reuse, or does the file need to be rewritten?)
2. Can the table take an outage? (If yes for a maintenance window, VACUUM FULL is fine. If no — i.e., production — pg_repack.)

A naming clarification: FREEZE and ANALYZE are options, not siblings

VACUUM (FREEZE) and VACUUM (ANALYZE) are not fourth and fifth tools in the same family as VACUUM FULL and pg_repack. They are options of the same command — they change what VACUUM does (freeze old xids, collect statistics), not how it locks or whether it rewrites the file. The two rows that return disk are the rewrite tools; the two options are the rest of VACUUM's job list, and Part 3 gives each of them a full section:

(Pure ANALYZE, the standalone command, truncates nothing at all — the tail rule only rides along when it's combined with a vacuum.)

Part 3 — VACUUM: the garbage collector that never shrinks your disk

VACUUM does four things, only one of which you usually mean when you run it:

1. Removes dead tuple versions and records freed bytes in the FSM.
2. Updates the visibility map, which is what makes index-only scans possible.
3. Freezes old transaction IDs — this is the anti-wraparound function, and it has nothing to do with disk.
4. Updates planner statistics (with ANALYZE; plain VACUUM does not).

None of this hands bytes back to the OS, except the truncated trailing tail — which almost never fires on a churned table, for the reason in Part 1.

The signal that says "you've been wasting your time"

The autovacuum log (only if you've set autovacuum_log_min_duration; the default is off) shows the truncation story:

automatic vacuum of table "db.pg_toast.pg_toast_16526": index scans: 0
    pages: 0 removed, 21181889 remain, ...

"0 removed" night after night means the vacuum is never truncating. Note two traps baked into that line:

• The pages: numbers cover truncation only — they say nothing about dead tuples inside the file. The dead-tuple number is the earlier x dead item identifiers in y pages line.
• The toast relation is named db.pg_toast.pg_toast_<oid> — a grep for "db.public.T" structurally misses it. Fetch the relid separately: select reltoastrelid from pg_class where relname='T'.

When vacuum is working correctly, this is genuinely enough. Dead tuples get recycled, inserts reuse the space, the file stays roughly at its high-water mark. The failure mode is when your live data shrinks (retention deletes) but the file doesn't follow — that's stranded space, and no amount of vacuuming changes it.

VACUUM (FREEZE): the anti-wraparound function, and why it isn't about disk

Every transaction consumes an xid, and PostgreSQL compares xids to decide visibility. A tuple whose insertion xid is "in the future" after a wrap is invisible to everyone — that's the data-loss event the whole freeze machinery exists to prevent. Freezing is the act of replacing an old xid with a permanent "always committed" marker (FrozenTransactionId), stored per tuple in the header's hint bits (HEAP_XMIN_FROZEN).

The safety rule is the interesting part. You may only freeze a tuple whose insertion xid is old enough that no still-running snapshot could need the original value — and the cutoff for that is anchored to the oldest snapshot's xmin. Every live transaction, plus the vacuum's own snapshot, reports an xmin; the lowest one (oldestXmin) is the boundary. A tuple older than oldestXmin is visible to every possible snapshot, by definition, so replacing its xid changes what no one can see. The freeze cutoff is oldestXmin − vacuum_freeze_min_age, and the gap is a deliberate buffer: it lets a routine vacuum freeze a bounded, cheap set now and leave the rest for later passes.

Three shortcuts keep this cheap in the common case:

• Hint bits. The first time anything (a read, a vacuum) checks the commit log (clog) for an xid's status, the answer is cached in the tuple header as HEAP_XMIN_COMMITTED / HEAP_XMIN_INVALID. Later passes skip the clog entirely. The frozen marker is the ultimate shortcut: no clog lookup, ever. (Commit timestamps — track_commit_timestamp — are a separate facility for "when did this xid commit" questions; freezing and visibility run on the clog + hint bits, not on timestamps.)
• The visibility map. Vacuum records two bits per page: all_visible and all_frozen. Index-only scans lean on all_visible to skip the heap visit entirely, which is why index-only scan quality quietly depends on vacuum having run at all. all_frozen is the freezer's fuel: an aggressive vacuum skips pages already marked all-frozen, which is what stops anti-wraparound runs from re-reading the whole table every time.
• Aggressive sweeps. A normal vacuum only visits pages with dead tuples or old tuples to freeze. When a table's relfrozenxid is older than vacuum_freeze_table_age, the vacuum flips to aggressive mode and walks every page not marked all-frozen.

The three GUCs are one story: vacuum_freeze_min_age (default 50,000,000) defers freezing to keep each run cheap; vacuum_freeze_table_age (default 150,000,000) forces the whole-table sweep when a table is falling behind; autovacuum_freeze_max_age (default 200,000,000) is the emergency floor — a table that old gets an automatic anti-wraparound vacuum even with zero dead tuples. The multixact counter has its own guard — autovacuum_multixact_freeze_max_age (default 400,000,000) — in case you inherit a table that accumulated heavy row-level lock traffic under older versions.

If all of that fails — autovacuum disabled, a table too big to finish the sweep, a long outage — the 2-billion-transaction horizon arrives, and the database stops accepting commands:

FATAL:  database is not accepting commands to avoid wraparound data loss

That is not a repack opportunity. That is an emergency freeze on a database that, for queries, is effectively down. The server logs "must be vacuumed within N transactions" warnings for a long time before this lands, which is the whole monitoring story: watch age(datfrozenxid), act long before the warnings stop being warnings.

VACUUM (FREEZE) is the manual, do-it-now version: shorthand for running with vacuum_freeze_min_age = 0 and vacuum_freeze_table_age = 0, i.e. freeze every tuple that can possibly be frozen and scan every page that isn't already all-frozen. Justified when:

• The horizon is genuinely aged — age(datfrozenxid) is a large fraction of 200M and you want the reset on your schedule, not autovacuum's.
• You just promoted a standby, restored from a physical backup (PITR), or cloned the cluster. The frozen horizon travels with the backup, so age(datfrozenxid) reflects the backup time, not today — and a standby only advances its horizons when the primary's vacuums arrive via WAL. One VACUUM (FREEZE) on the big tables resets both, before the week turns.
• A table is about to sit idle for months (archival / immutable data). Freeze it once while you're already writing it, not as a separate weekend job.

Dangerous when used as a habit: VACUUM (FREEZE) on a large healthy table is a full-file sweep that freezes every tuple — dead or alive — dirties every page (which means WAL for each), and returns zero bytes of disk. On my 171 GB table that's a 171 GB read plus writeback for nothing, at a time when the disk was already at 83% and the file was the wrong size anyway. And note the corner that makes it moot: if the table needs a rewrite at all, pg_repack/VACUUM FULL create a brand-new relfilenode whose relfrozenxid starts at now — the rewrite is also a freeze reset, for free.

So the point, stated plainly: freezing is about xid-horizon safety, not disk. A perfectly frozen 170 GB table still occupies 170 GB. It's the check that belongs in every maintenance log, alongside bloat:

-- the two horizon checks: database-wide, then per-table
SELECT datname, age(datfrozenxid),
       current_setting('autovacuum_freeze_max_age') AS freeze_max
FROM pg_database ORDER BY age(datfrozenxid) DESC;
 
SELECT relname, age(relfrozenxid)
FROM pg_class WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC LIMIT 5;
 
-- the wrapper when the numbers say so (VERBOSE shows the freeze counter)
VACUUM (FREEZE, VERBOSE) public.T;

VACUUM (ANALYZE): the statistics pass, and why it isn't free

ANALYZE is the fourth job: it samples rows, computes per-column statistics, and stores them for the planner. The default default_statistics_target = 100 sets the sample cap at 300 × statistics_target — 30,000 rows (small tables are fully scanned instead). The per-column output: null fraction, distinct-count estimate, most-common values with frequencies, histogram bounds, average width, and a correlation estimate; extended statistics (CREATE STATISTICS) land in pg_statistic_ext / pg_statistic_ext_data. The planner's selectivity guesses for joins, filters, and group sizes come straight from these numbers, and ANALYZE also refreshes reltuples/relpages in pg_class — the table-level sizes the planner multiplies against.

The cost math is where people get surprised. The sample is drawn at random across the whole relation: on a heap much larger than the sample, every sampled row tends to land on its own page, so the worst case is on the order of 30,000 scattered 8 KB reads — ~240 MB of page reads on a cold heap. Against the 171 GB file that's roughly 0.14% of a full read. Cheap by comparison, not free — and it competes with whatever else is hitting the disk. Remember the 19 GB repack pinned the disk at 98% within a minute; an ANALYZE of the same table in that window is measurable noise.

Note what ANALYZE does not do: it never touches dead tuples, never writes the FSM, never truncates, never shrinks anything. Like freezing, it exists to be invisible — and its failure mode is exactly that: invisibility.

When to combine, when to separate: VACUUM ANALYZE (or VACUUM (ANALYZE)) does both in one pass over the table — the right call when you're already paying for the visit (post-repack, post-restore). Separating them lets you pick different windows: vacuum on schedule, ANALYZE after the bulk load that changed the data's shape. Autovacuum treats them as independent arms with independent counters: the vacuum arm fires on dead-tuple counts (Part 9), the analyze arm on n_mod_since_analyze against autovacuum_analyze_threshold (default 50) + autovacuum_analyze_scale_factor (default 0.1) × reltuples — all visible in pg_stat_user_tables.

The failure story, because every "stale stats" lesson needs one. The instigator is always the same: a big, fast change in row count. The retention sweep deletes most of a table in an hour and n_mod_since_analyze spikes; autovacuum's analyze arm needs a quiet stretch to bother, and by then the query is being planned against the pre-sweep numbers. Or a pg_repack -Z run skipped its final ANALYZE, so the fresh table has no real numbers at all — a negative reltuples means "unknown" to the planner. I watched the classic shape after one such repack: a filter against the visibility table (221k rows, 16.5 GB of indexes) that used to be an index probe was planned as a hash join over the whole relation, because the estimate described the table the old stats knew. No error, no log line, no lock — the query just comes back slower by orders of magnitude. One command fixes it:

ANALYZE (VERBOSE) public.T;   -- prints the "scanned N of M pages / N rows in sample /
                              -- N estimated total rows" line per table

Verify afterward with the same view the planner reads:

-- pg_stats is the sanitized public view over pg_statistic
SELECT attname, null_frac, n_distinct, most_common_vals, histogram_bounds, correlation
FROM pg_stats WHERE tablename = 'T';
 
-- and the staleness counters, from the autovacuum analyze arm's point of view
SELECT relname, n_tup_ins, n_tup_del, n_mod_since_analyze,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'T';

Part 4 — VACUUM FULL: the same rewrite, but as an outage

VACUUM FULL creates a brand-new copy of the table: it reads live rows, writes them into a fresh heap, re-TOASTs the blobs, rebuilds the indexes, and swaps the new file in for the old one — atomically, so there's never "half a table". The old bytes are unlinked at the end. Space returns to the OS in one step.

The cost: it takes ACCESS EXCLUSIVE for the entire rewrite. Every read and every write on the table queues behind it for minutes to hours, depending on size. On a live service that's a downtime event, not a maintenance task.

The one thing this table is genuinely good for: a small table, fully offline, where space must come back right now and you have a real window. Even then, pg_repack does the identical job and leaves you a rollback story, so VACUUM FULL is almost never the better choice on a system that's serving traffic.

Why TOAST bloat can only be fixed by a rewrite, by the way: pg_repack/VACUUM FULL swap file identities atomically, and copying the row values re-TOASTs and compacts the toast relation too. Cheaper tricks like SET col = col rewrites (pgcompacttable, plain CREATE TABLE AS) preserve the existing TOAST pointers — no re-toast, no chunk churn, no shrink.

Part 5 — pg_repack: the same rewrite, with correct locking

Here's the mechanics, because the lock claims sound magical and they're not:

   original table ──AFTER trigger──> repack.log_<oid>   (every INSERT/UPDATE/DELETE while copying)
        │
        ├─ [ACCESS EXCLUSIVE, seconds]  create log table + trigger
        ├─ [SHARE UPDATE EXCLUSIVE, minutes]
        │      CREATE TABLE repack.table_<oid> AS SELECT ... FROM ONLY original   (one snapshot)
        │      CREATE INDEX ... on the new table (-j N in parallel)
        │      apply repack.log_<oid> in batches until nearly empty
        ├─ [ACCESS EXCLUSIVE, seconds]  apply last log rows; swap relfilenodes in pg_class
        └─ drop the old files; ANALYZE (skipped by -Z)

1. Setup. Two sessions: one holds the lock transaction for the whole run, one does the work. The log table + trigger need a primary key (or a unique NOT NULL index) — the log is replayed by key.
2. Copy. CREATE TABLE AS SELECT under a single snapshot. Only live rows are read (through the heap/TOAST index — empty pages cost nothing), so runtime is proportional to live data, not file size. I measured ~30 GB of live data copied out of a 171 GB file in ~574 s (~110 MB/s — about the disk's ceiling). Blobs get re-TOASTed, so the new TOAST is compact too. Applications keep writing; the trigger captures it. Since 1.5.3 the copy holds SHARE UPDATE EXCLUSIVE — writes proceed, but any ALTER TABLE/TRUNCATE/VACUUM FULL/another repack on the same table queues behind it.
3. Indexes are built fresh on the new table; -j N parallelizes, at the cost of more I/O on one disk.
4. Catch-up. Log rows are applied in batches. If the app writes faster than pg_repack applies, it never converges — this is the quantitative reason to run in a quiet window. Every write during replay is applied twice (once live, once replayed), so peak-hour WAL/I/O ≈ 2× steady state.
5. Swap. Waits up to --wait-timeout (default 60 s) for ACCESS EXCLUSIVE. The default when it can't get it: cancel the blocking queries, then terminate their backends. --no-kill-backend (-D) makes it give up instead — and you should always pass it (more below). Then it exchanges relfilenodes: the OIDs your application sees never change, so no code, no connection string, no index name changes.
6. Drop + ANALYZE. Old files are unlinked and df drops instantly. -Z skips the ANALYZE; if you use it, run ANALYZE yourself or the table will serve queries off stale statistics. (And the swap quietly resets the freeze horizon too: a brand-new relfilenode's relfrozenxid starts at now — a rewrite cures wrapping-age tables as a side effect, which is why a table that needs a rewrite never needs a separate VACUUM (FREEZE).)

Requirements, briefly

Superuser (or -k — required on managed Postgres where your admin isn't a superuser), a PK/unique-not-null index, client version == extension version (pg_repack --version vs select extversion from pg_extension), no concurrent DDL on the table, one repack at a time per table. It can't repack unlogged or temp tables. On Azure Database for PostgreSQL Flexible Server, allow-list pg_repack in azure.extensions, CREATE EXTENSION, and run the client from a VM with the same version and --no-superuser-check — you just can't see the server log file, so guard on pg_stat_activity + app metrics instead.

Part 6 — The decision framework

First, measure. Bloat isn't a feeling. The FSM gives you the real number for free — it reads only the ~1 byte-per-page map, not the file:

CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
 
-- heap
SELECT pg_size_pretty(pg_relation_size('public.T')) AS size,
       pg_size_pretty(sum(avail))                    AS free,
       round(100.0 * sum(avail) / pg_relation_size('public.T'), 1) AS pct_free
FROM pg_freespace('public.T');
 
-- toast: fetch its relid, then run the same query on it
SELECT reltoastrelid, pg_size_pretty(pg_relation_size(reltoastrelid))
FROM pg_class WHERE relname = 'T';

Then the rule of thumb I operate on:

And the ordering that cost the least drama, in every case:

1. Fix the config that let it happen — see Part 8.
2. REINDEX INDEX CONCURRENTLY, index by index. PG12+ rebuilds one index next to the old one under SHARE UPDATE EXCLUSIVE and swaps — safe even at peak on a cached heap. In my incident this gave back 15 GB with zero impact before any table repack ran. On failure, drop the leftover *_ccnew index (pg_index.indisvalid = false). Don't repack a table to fix index bloat; the btree rebuild prefers the index.
3. Tables + TOAST: pg_repack -t table, in a quiet window, watched (Part 7).
4. VACUUM FULL: only when the table can go fully offline and pg_repack isn't available (some managed setups) or the table is small enough that it's a genuine maintenance sleep.

For an append-only table with a TTL and heavy TOAST, skip all three: switch to time-based range partitions and DROP PARTITION. Partition drop returns space to the OS instantly, with zero bloat and zero vacuum debt. That's a design fix, not a maintenance round.

The other two dials: freeze horizon and statistics

The disk tools answer "is this space stranded". These two answer "is the database about to stop accepting commands" and "is the planner lying to itself". Same discipline: measure first, act on thresholds, log what you did.

One ordering rule that saves a wasted window: check the horizon before spending hours on a rewrite. If a table is heading for wraparound age, the repack fixes the horizon for free — skip the separate VACUUM (FREEZE) pass and spend the I/O once.

Part 7 — Operational rules (each one cost me something)

These are the rules I'd want on the wall before anyone runs pg_repack on production.

1. I/O headroom is the constraint, not live size. At peak write rate, a 19 GB copy pinned the disk at 98% utilization and the application started timing out within a minute; at ~1/10th the rate, a 30 GB copy ran nearly error-free. Know the disk's ceiling (the box in my incident was a Premium SSD P15: 125 MB/s, 1,100 IOPS) and know your real quiet hours. Don't guess them — derive them.
2. Always --no-kill-backend. The default behavior — killing application connections to grab the swap lock — is how you get an incident during the incident. Give up and retry instead.
3. Be deliberate about server-side timeouts. Run the client with PGOPTIONS='-c idle_in_transaction_session_timeout=0 -c statement_timeout=0' so it reaches every session pg_repack opens. The actual failure modes: the swap connection blocked on the lock still counts toward statement_timeout, and the lock-holder idles in a transaction between internal steps — a 5-minute server-side idle_in_transaction_session_timeout killed my run at ~10 minutes in with the copy 90% done (FATAL: terminating connection due to idle-in-transaction timeout). On the application role, do the opposite: set lock_timeout = '5s' so a blocked statement fails fast instead of silently saturating the pool — but do not set a short statement_timeout there, or the janitor's multi-thousand-row deletes will abort and trip pg_repack's abort path.
4. Pick the quiet window from write rate, not delete rate. The window that minimizes replay cost is where the target table's write volume — inserts + updates, not just the deletes that drive churn — is at its trough. Derive the curve from hourly deltas of n_tup_ins/n_tup_del in pg_stat_user_tables, and avoid the hour right after any scheduled sweep.
5. Watchdog the application and the database, not the log stream. Poll every ~30 s: app-side latency/error metrics and a DB-side probe (pg_stat_activity backends in a wait event against the target; WAL size vs the free margin). Baseline for ≥ 1 hour, not 2 minutes. Abort — SIGINT to the client, clean rollback, never kill -9 server backends — only on 3 consecutive 30 s samples at ≥ 5× baseline, on free disk < 2 GB, or on a wall-clock cap that ends before the next peak. Gate the start on the same probes, not just the abort.
6. Guards before, not judgement during. Time window, bloat ≥ 40% (else there's nothing to gain), free ≥ 2 × (live_heap + live_indexes) + 3 GB, an app baseline that's quiet, no other repack active, a lock file. Log one line per skip reason — a cron that explains why it did nothing is worth more than one that silently does nothing.
7. Containers: apt-get install postgresql-14-repack lands in the container's ephemeral layer — reinstall on recreate, idempotently, in the script. And the client version must equal the extension version, or the swap fails immediately.
8. Repack resets the high-water mark; it does not change the resident set. The file re-grows to whatever the workload keeps live and parks there. Compaction is maintenance; reducing what you write is the cure. And it shouldn't have needed an incident to be discovered — see Part 8.
9. Scope autovacuum; don't disable it globally. If you pause vacuum on the target during the rewrite (there's a documented toast-vacuum/rewrite race: "missing chunk for toast value"), do it per-table (ALTER TABLE T SET (autovacuum_enabled = false)), repack, then VACUUM (ANALYZE) and re-enable. autovacuum=off fleet-wide piles debt onto every other table while you're not looking.
10. Verify afterward. The post-repack table should be ≈ live_heap + rebuilt index sizes; the toast should be lean; zero non-internal triggers on the table; zero invalid indexes; zero rows left in the repack. schema (leave a DROP EXTENSION pg_repack CASCADE; CREATE EXTENSION pg_repack; in your pocket for cleanup of leftovers); then ANALYZE for fresh stats.

Part 8 — The cheap playbook (copy for your next host)

# 0. diagnose (all read-only)
psql -c "create extension if not exists pg_freespacemap"
psql -c "select pg_size_pretty(pg_relation_size('public.T')) f,
                 pg_size_pretty(sum(avail)) free,
                 round(100.0*sum(avail)/pg_relation_size('public.T'),1) pct
          from pg_freespace('public.T')"                                  # heap FSM
psql -c "select reltoastrelid, pg_size_pretty(pg_relation_size(reltoastrelid))
          from pg_class where relname='T'"                                # toast relid + size
psql -c "select pg_size_pretty(pg_relation_size(reltoastrelid)) f,
                 pg_size_pretty(sum(avail)) free,
                 round(100.0*sum(avail)/pg_relation_size(reltoastrelid),1) pct
          from pg_freespace(reltoastrelid), pg_class where relname='T'"   # toast FSM
psql -c "select indexrelname, pg_size_pretty(pg_relation_size(indexrelid)), idx_scan
          from pg_stat_user_indexes where relname='T' order by 2 desc"
grep -A1 'automatic vacuum of table "db.public.T"' postgres.log | grep pages: | tail -3   # "0 removed"?
psql -c "select datname, age(datfrozenxid) f, current_setting('autovacuum_freeze_max_age') max
          from pg_database order by 2 desc"                               # freeze horizon
psql -c "select relname, age(relfrozenxid) f from pg_class
          where relkind='r' order by 2 desc limit 5"                      # per-table horizon
psql -c "select relname, n_mod_since_analyze, last_analyze, last_autoanalyze
          from pg_stat_user_tables order by n_mod_since_analyze desc limit 5"  # stats staleness
 
# 1. cheap wins, any time of day, one at a time with a pause and an app-error guard
psql -c "reindex index concurrently idx_t_payload_gin"    # per index
psql -c "select indexrelname from pg_index i join pg_class c on c.oid=i.indexrelid
          where not i.indisvalid"                          # leftover *_ccnew? drop concurrently
 
# 2. capacity + window for the table rewrite
#    live_heap = relsize(T) - fsm_free(T);  live_toast = relsize(toast) - fsm_free(toast)
#    need = 2*(live_heap + live_indexes) + 3G;  free >= need?  quiet hours?  disk ceiling?
#    guards: bloat >= 40%, quiet baseline >= 1h, no other pg_repack, free >= 2G floor during run
 
# 3. install (once) and run, watched
apt-get install postgresql-14-repack && psql -c "create extension pg_repack"   # client == extension version
PGOPTIONS='-c idle_in_transaction_session_timeout=0 -c statement_timeout=0' \
  pg_repack -U user -d db -t T --no-kill-backend --wait-timeout 30 --elevel INFO
#    in another shell, every 30 s: app p99/errors, lock waits on T in pg_stat_activity,
#    WAL size vs margin, iostat -dx 5, df (expect it to RISE, then drop)
#    abort = 3 consecutive 30s samples >= 5x baseline, or free < 2G, or a wall-clock cap:
#      SIGINT the client, then: psql -c "drop extension pg_repack cascade; create extension pg_repack"
 
# 4. verify
psql -c "select pg_size_pretty(pg_total_relation_size('T'))"; df -h
psql -c "select pg_size_pretty(pg_relation_size(reltoastrelid)) from pg_class where relname='T'"
psql -c "select count(*) from pg_trigger where tgrelid='T'::regclass and not tgisinternal"  # 0
psql -c "select count(*) from pg_index where indrelid='T'::regclass and not indisvalid"      # 0
psql -c "select 1 from pg_class where relname like 'repack.%'"                                # 0 rows
psql -c "analyze T"
 
# 5. the other two jobs — only when the checks in step 0 fire
psql -c "vacuum (freeze, verbose) T"    # age(datfrozenxid) approaching ~100M, or post-promote/post-restore
psql -c "analyze (verbose) T"           # post-bulk-load, post-restore, or after a pg_repack -Z run
psql -c "select attname, n_distinct from pg_stats where tablename='T'"   # verify the planner's numbers

Two notes on the arithmetic. df rises during the run — old file and new copy coexist — then drops in one step at the swap. On my box: 78% → 88% → 17%. Tell whoever watches the disk, or they will page you mid-copy. And time ≈ live / bandwidth is a floor, not an estimate: add the 171 GB sequential read, the index-build CPU, log replay, and the shared checkpoint/WAL writes all on one disk — expect 2–3× the naive figure.

Part 9 — What actually cures it (recurrence prevention)

The rewrite is maintenance. This is the fix:

• Autovacuum was too lazy. Default autovacuum_vacuum_scale_factor = 0.2 on a 170 GB table means ~34 GB of dead tuples have to pile up before autovacuum fires. Set the scale factor per hot table:

  sqlCopy

  ALTER TABLE history_node SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000
  );

• Complete/timestamp long-lived rows as soon as the business allows. Retention machinery can only reach rows that are closed; an open long-running row is invisible to every cleaner.
• Cap row growth at the source. Budget continuation/compaction per workflow type so no single row can grow unbounded; move cold payloads out-of-band to object storage referenced by key; compress at the edge. This is what changes the daily resident set — compaction alone makes the file honest about the size it already wants to be.
• For append-only TOAST tables: time-range partitions and DROP PARTITION.

Anti-patterns to refuse: lowering retention "to free disk" (it moves the delete horizon; space returns only when sweep + vacuum/rewrite shrink the file, and shorter retention raises dead-tuple volume per hour); application-side batch deletes (they compete with retention for the same rows and same I/O); VACUUM FULL on a live service; running the rewrite at peak "because the live set is small"; shrinking the free-space margin; repacking/reindexing on a fixed schedule without measuring bloat first (a healthy table still pays full copy + 2× replay WAL for nothing); toggling autovacuum=off globally; VACUUM (FREEZE) on a schedule as if it were a disk tool (it is a full-file sweep with no space to show for it — the once-per-load pass is the only one that buys anything); and treating VACUUM (ANALYZE) as a free "just refresh stats" call on a blob-heavy file (its sample is drawn from random pages across the whole extent, so it is a burst of scattered reads every time — deliberate, not casual).

The lessons, condensed

1. Bloat is not a Postgres bug; it's MVCC's rent. A file only grows until something rewrites it. "Wait for retention" can't work because inserts are scattered on purpose and the trailing tail is never empty.
2. VACUUM is maintenance, not repair. It recycles space inside the file and freezes XIDs; the file size is unchanged. "0 removed" in the log is about truncation of an empty tail — possibly skipped under lock contention — and says nothing about dead tuples.
3. VACUUM FULL and pg_repack are the same rewrite; the difference is locking. ACCESS EXCLUSIVE for the whole run is an outage. Seconds of it at the start and end — pg_repack — is a maintenance job.
4. Measure heap and TOAST separately. On blob-heavy tables the toast is where the stranded bytes live, and the FSM is the free, exact measurement. Bloat ≥ 40% with churn means repack and then fix the churn.
5. Capacity math is a gate, not a suggestion. free ≥ 2 × (live_heap + live_indexes) + 3 GB; df rises then drops; running out of disk mid-copy is a Postgres PANIC, not a rollback.
6. It's a watched operation. --no-kill-backend, zeroed server-side timeouts, a quiet window derived from write rate, a watchdog on app metrics and DB probes, SIGINT-only aborts.
7. Fix the config, then the data, then the design. Autovacuum is a maintenance program with defaults that assume small tables; the file re-grows to the workload's resident set no matter how well you compact it. Partitions and smaller payloads are the only ways to stop paying the rent.
8. FREEZE and ANALYZE are VACUUM's other two jobs, and neither is a disk tool. FREEZE protects the 32-bit xid horizon — monitor age(datfrozenxid), act well before autovacuum_freeze_max_age, and remember a rewrite resets the horizon for free. ANALYZE keeps the planner honest with a 30,000-row sample — cheap but not free, and invisibly tragic when stale. Read the four-thing list before you run any of them.

Related posts from the same operating theme: a PostgreSQL 17 migration postmortem (WAL recycling, timeouts, and the false economy of conservative configs) and Docker PostgreSQL backup strategies (the restore side of the same database operations).

Find me on GitHub — questions, corrections, and bloat war stories welcome.