Removing Table Bloat Online With pg_repack

A monitoring alert says your events table is 480 GB on disk, but SELECT count(*) and the row estimates suggest it holds barely 90 GB of live data. Sequential scans crawl, the cache hit ratio is falling, and autovacuum has been running for hours without shrinking the file. The obvious tool — VACUUM FULL — would compact it, but it takes an ACCESS EXCLUSIVE lock for the entire rewrite, blocking every read and write against a table your application depends on continuously. On a table this size that is hours of hard downtime nobody will sign off on. This is exactly the case pg_repack was built for: it rebuilds a bloated table and its indexes into a fresh, compact copy while the table stays fully readable and writable, taking a brief exclusive lock only at the very start and the very end. This page is for the DBA or platform engineer staring at a bloated relation who needs to reclaim the space online, and who needs to get the prerequisites, disk headroom, and monitoring right before kicking off a multi-hour rebuild on production.

Symptom / Error Signatures

You are on this page because one of these signals is true — the table is physically far larger than its live data, or a VACUUM FULL attempt is already hurting:

  • Physical size dwarfs live data: pg_total_relation_size('events') reports hundreds of gigabytes while the estimated live-tuple size is a fraction of that, and the ratio keeps climbing after UPDATE/DELETE-heavy workloads.
  • autovacuum reclaims dead tuples logically but the file never shrinks — space is reused for new rows but never returned to the operating system, so df never recovers.
  • A VACUUM FULL events; you started is now blocking traffic: pg_stat_activity shows the session holding AccessExclusiveLock, and other queries pile up in wait_event_type = Lock.
  • Application logs fill with canceling statement due to lock timeout or LOCK TIMEOUT because they queued behind the exclusive rebuild.
  • Index-only scans stopped helping and index sizes ballooned — index bloat tracks table bloat when the heap is churned by frequent updates.
  • A prior pg_repack run was interrupted and now aborts with WARNING: the table "public.events" already has a trigger or leaves a repack.log_* table behind.

Root Cause Analysis

PostgreSQL’s MVCC model never updates a row in place. An UPDATE writes a new tuple version and marks the old one dead; a DELETE just marks it dead. autovacuum later reclaims those dead tuples so their space can be reused within the table, but it does not return the freed pages to the operating system unless they happen to be at the physical end of the file. The result is bloat: a heap (and its indexes) physically far larger than the live data it stores, which slows scans, wastes cache, and inflates backups. Understanding that MVCC is doing this by design — not malfunctioning — is the same engine behavior explored across transactional versus non-transactional DDL, where PostgreSQL’s copy-on-write semantics shape every heavy operation.

The only way to truly return space is to physically rewrite the relation into a fresh, compact file. Native VACUUM FULL does exactly that but under an ACCESS EXCLUSIVE lock held for the whole rewrite. pg_repack achieves the same compaction online by building the new copy alongside the live one and capturing concurrent writes rather than blocking them. The two diverge sharply on lock behavior and cost:

Axis VACUUM FULL (native) pg_repack
Lock held ACCESS EXCLUSIVE for the entire rewrite Brief ACCESS EXCLUSIVE only at setup and swap
Table availability Fully blocked, reads and writes Readable and writable throughout
Extra disk needed ~1x table + new indexes ~1x table + indexes + change-log overhead
Mechanism In-place blocking rewrite Shadow copy + trigger-captured change log + relfilenode swap
Indexes Rebuilt under the same lock Rebuilt online, optionally in parallel (--jobs)
Ordering None Optional CLUSTER-order or --order-by
Interruptible Rolls back cleanly Leaves temp objects to clean with --drop

Mechanically, pg_repack creates a log table and installs a trigger on the target so every concurrent INSERT/UPDATE/DELETE is recorded. It then copies the live rows into a new heap, builds fresh indexes on that heap, replays the captured changes to catch up, and finally swaps the relation’s underlying files (the relfilenode) under a short ACCESS EXCLUSIVE lock — the same copy, sync, cutover shape the online schema change tools family shares. Because it swaps files rather than logical objects, foreign keys, views, and the table’s OID survive the operation untouched.

pg_repack rebuilds a bloated heap into a compact copy while capturing writes The bloated heap on the left mixes dead tuples and live tuples across many pages. pg_repack copies only live rows into a fresh compact heap on the right. During the copy, a trigger records every concurrent insert, update and delete into a change-log table that is replayed to catch up. Bloated heap (480 GB) live rows scattered among dead tuples live dead tuple copy live Compact heap (90 GB) live rows packed contiguously Concurrent writes during rebuild trigger on events change log replayed
MVCC leaves dead tuples interleaved with live rows; pg_repack copies only the live rows into a fresh contiguous heap while a trigger records concurrent writes into a change log that is replayed to catch up.

Immediate Mitigation

If a VACUUM FULL is blocking production right now, cancel it first, then rebuild online instead. If you are starting fresh, skip step 1.

1. Cancel a blocking VACUUM FULL. Find the session holding the exclusive lock and cancel it. The table is left consistent — VACUUM FULL rolls back cleanly — and traffic drains immediately.

-- PostgreSQL · run as a superuser or the table owner · releases the ACCESS EXCLUSIVE lock
-- Find the blocking VACUUM FULL, then cancel it (pg_terminate_backend if cancel won't take).
SELECT pid, state, wait_event, query
FROM pg_stat_activity
WHERE query ILIKE 'vacuum full%';
SELECT pg_cancel_backend(<pid>);   -- graceful; use pg_terminate_backend(<pid>) if it won't stop

2. Confirm the extension is installed and versions match. pg_repack is both a server extension and a client binary, and their versions must be identical or the client aborts.

-- PostgreSQL · run as a superuser in the TARGET database · one-time per database
-- Both the extension and the client binary must be the SAME version, or the run aborts.
CREATE EXTENSION IF NOT EXISTS pg_repack;
SELECT extversion FROM pg_extension WHERE extname = 'pg_repack';  -- compare to `pg_repack --version`

3. Verify disk headroom before you start. pg_repack builds a full second copy of the table plus its indexes plus the change log. Measure the target and confirm free space comfortably exceeds it — a run that exhausts disk mid-rebuild aborts and strands temporary objects.

-- PostgreSQL · read-only · run before every repack · plan for ~1x the reported size in free disk
SELECT pg_size_pretty(pg_total_relation_size('public.events')) AS total_on_disk,
       pg_size_pretty(pg_relation_size('public.events'))       AS heap_only,
       pg_size_pretty(pg_indexes_size('public.events'))        AS indexes;
-- Free space on the data volume must exceed total_on_disk with margin for concurrent WAL + change log.

4. Run the rebuild online. Invoke the client against the target table. It must run outside any explicit transaction and connect as a role with the privileges to install triggers and swap files. Parallelize index builds with --jobs.

# bash · run outside a transaction · connect as superuser or table owner · needs the extension present
# Run inside tmux/screen so a dropped SSH session cannot kill a multi-hour rebuild.
pg_repack --host=primary.db.internal --dbname=shop \
  --table=public.events \
  --jobs=4 \
  --no-order \
  --wait-timeout=60 \
  --elevel=INFO
# --jobs parallelizes index rebuilds; --no-order skips CLUSTER ordering for speed;
# --wait-timeout caps how long it waits for the brief locks before backing off.

5. Repack indexes only, when the heap is fine. If only indexes are bloated, rebuild them alone with --only-indexes — cheaper and faster than a full heap rewrite, and the online equivalent of REINDEX.

# bash · run outside a transaction · online index rebuild, avoids the REINDEX lock
pg_repack --dbname=shop --table=public.events --only-indexes --jobs=4
A pg_repack run holds ACCESS EXCLUSIVE only at the two ends The run begins with a brief exclusive lock to create the change log and install the trigger. The long middle phase copies rows, builds indexes and replays the change log while the table is fully readable and writable. It ends with a second brief exclusive lock to replay the final changes and swap the relfilenode. One pg_repack run over time time brief lock Online phase — table readable and writable copy rows · build indexes (--jobs) · replay change log the long, multi-hour part of the run brief lock create log + trigger replay tail + swap files ACCESS EXCLUSIVE (brief) table available
Unlike VACUUM FULL, pg_repack takes ACCESS EXCLUSIVE only in two brief bands at the start and end; the long copy-and-build phase between them leaves the table fully readable and writable.

Permanent Fix / Long-Term Pattern

Reclaiming bloat once is mitigation; keeping it from returning is the fix. Bloat is a symptom of autovacuum not keeping pace with the table’s churn, so tune the table’s autovacuum aggressiveness rather than repacking on a schedule. Lower autovacuum_vacuum_scale_factor for hot tables so vacuum triggers on a smaller fraction of dead tuples, raise autovacuum_vacuum_cost_limit so it does more work per cycle, and monitor n_dead_tup from pg_stat_user_tables as a first-class metric.

-- PostgreSQL · run as the table owner · per-table override, applied immediately
-- Make autovacuum trigger far earlier on a high-churn table so bloat never accumulates.
ALTER TABLE public.events SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% dead tuples, not the 20% default
  autovacuum_vacuum_cost_limit   = 2000    -- let each autovacuum do more work before yielding
);

Treat any manual pg_repack as a real change subject to the same discipline as any migration: rehearse it against a production-like copy first, per your environment parity baseline, and record it in your schema version control ledger so the operation is auditable rather than an untracked one-off. When bloat comes from a large DELETE backfill — purging old rows, backfilling a rewritten column — pair the repack with the chunked-delete tuning in backfill optimization so you are not creating the very bloat you then have to reclaim. For the full family of online rewrite techniques, including where pg_repack sits alongside the MySQL tools, see the parent online schema change tools guide.

One hard prerequisite deserves emphasis in the long term: pg_repack requires the table to have a PRIMARY KEY or a non-null UNIQUE index, because it uses that key to match rows in the change log against the new heap during replay. Tables without one — often the exact append-and-delete logging tables that bloat worst — cannot be repacked until you add a suitable key first, so build that constraint into the table’s design rather than discovering the gap during an incident.

Decision tree for repacking a bloated table safely Starting from a bloated table, check whether only the index is bloated (repack indexes only), whether the table has a primary key or non-null unique index (add one first if not), and whether there is enough free disk for a full copy (free space first if not). If all checks pass, repack the table. A side branch handles recurring bloat by tuning autovacuum. Table bloated? Only the index is bloated? yes pg_repack --only-indexes no Has PRIMARY KEY or non-null UNIQUE index? no add a key first yes Enough free disk for a full copy? no free space / add volume yes pg_repack --table Recurring bloat? it keeps coming back tune autovacuum scale_factor
Pick the cheapest action that fits: repack indexes only when just they are bloated, add a required key and free disk before a full repack, run pg_repack --table when the checks pass, and tune autovacuum so the bloat does not return.

Verification Checklist

-- PostgreSQL · read-only · confirm the rebuild left no locks and no orphaned trigger
SELECT pid, mode, granted FROM pg_locks
  JOIN pg_class ON pg_locks.relation = pg_class.oid
  WHERE pg_class.relname = 'events' AND NOT granted;      -- expect zero rows
SELECT tgname FROM pg_trigger
  WHERE tgrelid = 'public.events'::regclass AND tgname LIKE 'repack%';  -- expect none

Frequently Asked Questions

How much extra disk space does pg_repack need? Plan for roughly a full second copy of the table plus all its indexes, because pg_repack builds a complete new heap and fresh indexes alongside the original before swapping them in — on top of the change-log table and the extra WAL the concurrent writes generate. If the table plus indexes report 480 GB, you want well over 480 GB free on the data volume with margin. A run that runs the volume out of space aborts mid-rebuild and leaves temporary objects you must clean up with pg_repack --drop (or by dropping the repack.log_* table and its trigger), so always measure pg_total_relation_size and check free space before starting.

Does pg_repack lock the table at all? Yes, but only briefly and only twice: a short ACCESS EXCLUSIVE lock at the start to create the log table and install the capture trigger, and another short one at the end to replay the final changes and swap the relation files. In between — the long phase where it copies rows and builds indexes — the table is fully readable and writable. This is the entire point versus VACUUM FULL, which holds ACCESS EXCLUSIVE for the whole rewrite. If a long-running transaction is holding the table when pg_repack needs one of those brief locks, it waits up to --wait-timeout and then backs off, so set that timeout and run during a lower-write window so the swap lands cleanly.

Why did pg_repack refuse to run on my table? The most common reason is a missing key: pg_repack requires a PRIMARY KEY or a non-null UNIQUE index to match change-log rows against the new heap, and refuses tables without one. Other frequent causes are a version mismatch between the client binary and the server extension (they must be identical), the extension not being installed in the target database (CREATE EXTENSION pg_repack;), or connecting as a role without the privileges to install triggers and swap relfilenodes. Add the required key or fix the privilege/version issue rather than forcing it.

Up one level: Online Schema Change Tools

Related Articles