Recuperación de espacio con pg_repack

PostgreSQL uses MVCC, so an UPDATE or DELETE does not overwrite a row in place. It marks the old tuple dead and writes a new version. Over time, dead tuples accumulate as bloat in tables and indexes.

Regular VACUUM reclaims dead tuples for reuse but does not shrink the file on disk. To physically return space to the OS, you traditionally run VACUUM FULL — but that takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the entire rebuild.

On a busy production table, that lock is unacceptable. This is where pg_repack comes in: it rebuilds the table and reclaims space with almost no blocking.

Never repack blindly. First quantify the bloat so you know the rebuild is worthwhile. The simplest signal is dead tuples versus live tuples from pg_stat_user_tables.

• n_live_tup — estimated live rows
• n_dead_tup — estimated dead rows awaiting cleanup
• last_autovacuum — when autovacuum last ran

A high dead-to-live ratio plus a large on-disk size means a repack will reclaim real space.

Dead-tuple counts hint at bloat, but you also want the physical footprint. Use the object size functions to see how much space the table and its indexes occupy.

• pg_table_size — heap plus TOAST, excluding indexes
• pg_indexes_size — all indexes on the table
• pg_total_relation_size — everything combined

Record these numbers before the repack so you can prove how much space was reclaimed afterward.

pg_repack has two parts: a server-side extension and a client command-line tool. The extension must be installed in each database you intend to repack, and the binary must match the server version.

You create the extension as a superuser. It registers the helper functions pg_repack relies on to coordinate the rebuild.

After this, the standalone pg_repack client (run from the shell, not from SQL) drives the actual work.

Understanding the mechanism explains why it is online. For a full-table repack, pg_repack:

• Creates a new, empty log table and a trigger on the original table to capture every INSERT, UPDATE, and DELETE during the rebuild.
• Builds a fresh copy of the table by reading current rows in physical order, then builds new indexes on that copy.
• Replays the captured changes from the log table so the copy catches up to live data.
• Swaps the new table in for the old one, taking a brief ACCESS EXCLUSIVE lock only for the final swap.

So instead of locking for the whole rebuild, it locks for a fraction of a second at the very end.

The client is invoked from the shell. The most common operation is repacking one bloated table. You pass connection options like any libpq client, plus -t for the target table.

The --no-order flag (optional) skips clustering by the primary key, which is faster when you only care about reclaiming space rather than physical row ordering.

This runs online — readers and writers keep working throughout, except for the brief final swap.

pg_repack cannot repack just anything. A full-table rebuild requires the table to have a PRIMARY KEY or at least a non-partial, non-expression UNIQUE index on NOT NULL columns. It needs this to identify rows when replaying logged changes.

If your table lacks one, add a suitable key first:

• Tables without a usable unique identifier are rejected with an error.
• You must run as a user with rights to create temporary objects and to lock the table briefly.

Sometimes the heap is fine but indexes have bloated from heavy churn. You can rebuild just the indexes of a table with -x (index-only), or target a specific index with -i.

This is lighter than a full repack: it rebuilds index storage without copying the heap. It is a strong alternative to REINDEX when you cannot afford the locks that a plain REINDEX (without CONCURRENTLY) would take.

pg_repack builds a full second copy of the table and its indexes before swapping. That means you need free disk space roughly equal to the size of the table plus its indexes during the operation.

• Check available space before starting; running out mid-repack aborts and rolls back.
• Long-running transactions can block the final swap because it needs the brief exclusive lock; watch pg_stat_activity for blockers.
• If the repack is interrupted, it cleans up after itself, but verify no leftover log tables or triggers remain in the repack schema.

Before committing, you can preview what pg_repack would do with --dry-run: it reports the targets without changing anything.

To repack an entire database, omit -t and let it process all eligible tables. Combine it with monitoring so you can throttle if I/O spikes.

• --dry-run — list candidates, make no changes
• No -t — repack every eligible table in the database
• -j N — use N parallel workers for index builds

After the repack completes, re-measure to confirm the win. Compare the new sizes against the numbers you recorded earlier. A successful repack typically drops pg_total_relation_size substantially when bloat was high.

Also re-check pg_stat_user_tables: a freshly repacked table starts with near-zero dead tuples. Then keep autovacuum tuned so bloat does not silently rebuild.

Test your understanding of when and why to choose pg_repack.

You learned how to reclaim bloated space online with pg_repack:

• Diagnose first using pg_stat_user_tables dead/live ratios and pg_total_relation_size.
• Install the extension with CREATE EXTENSION pg_repack; drive the work from the shell client.
• Online mechanism: a log table plus trigger capture changes, a fresh copy is built and swapped with only a brief ACCESS EXCLUSIVE lock — unlike VACUUM FULL which locks the whole time.
• Requirements: a primary key or usable unique index, plus free disk space roughly equal to table + index size.
• Options: -t for one table, -x/-i for indexes only, --dry-run to preview, -j for parallel index builds.
• Verify the reclaimed space and keep autovacuum tuned so bloat does not return.