0Pricing
PostgreSQL Performance & Query Optimization · Leçon

Récupérer de l’espace avec pg_repack

Reconstruisez en ligne les tables et les index fragmentés sans les verrous exclusifs nécessaires à VACUUM FULL.

Récupérer de l’espace avec pg_repack est une leçon PostgreSQL Performance & Query Optimization gratuite sur CoddyKit. Ceci est la leçon 2 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage PostgreSQL Performance & Query Optimization, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Certaines parties de cette leçon n'ont pas encore été traduites et s'affichent en anglais.

Why Bloat Needs an Online Rebuild

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.

Measuring the Bloat First

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.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Confirming On-Disk Size

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.

SELECT pg_size_pretty(pg_table_size('orders'))          AS table_size,
       pg_size_pretty(pg_indexes_size('orders'))        AS indexes_size,
       pg_size_pretty(pg_total_relation_size('orders'))  AS total_size;

Installing the Extension

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.

CREATE EXTENSION IF NOT EXISTS pg_repack;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pg_repack';

How pg_repack Works Internally

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.

Repacking a Single Table

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.

-- Run from the shell, not inside psql:
-- pg_repack -h db.internal -U admin -d shop -t orders --no-order
--
-- Equivalent connection via PGOPTIONS is also fine.
-- Repacks the 'orders' table online in database 'shop'.

Requirements pg_repack Enforces

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.
-- pg_repack needs a usable unique identity. If missing, add one:
ALTER TABLE events
  ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;

ALTER TABLE events
  ADD CONSTRAINT events_pkey PRIMARY KEY (id);

Reclaiming Index Bloat Only

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.

-- Rebuild only the indexes on a table:
-- pg_repack -d shop -t orders -x
--
-- Or rebuild a single named index:
-- pg_repack -d shop -i orders_customer_id_idx

Disk Space and Safety Considerations

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.
-- Find long-running transactions that could block the final swap:
SELECT pid, state, wait_event_type,
       now() - xact_start AS xact_age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '1 minute'
ORDER BY xact_age DESC;

Dry Runs and Whole-Database Repacks

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
-- Preview targets without touching data:
-- pg_repack -d shop --dry-run
--
-- Repack the whole database, 4 parallel index builds:
-- pg_repack -d shop -j 4

Verifying Space Was Reclaimed

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.

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_after,
       n_live_tup,
       n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

Quick Check

Test your understanding of when and why to choose pg_repack.

Recap

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.

Questions Fréquemment Posées

La leçon « Récupérer de l’espace avec pg_repack » est-elle gratuite ?

Oui — le texte complet de « Récupérer de l’espace avec pg_repack » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours PostgreSQL Performance & Query Optimization, passe à CoddyKit PRO. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Récupérer de l’espace avec pg_repack » ?

Reconstruisez en ligne les tables et les index fragmentés sans les verrous exclusifs nécessaires à VACUUM FULL. Tu pratiques PostgreSQL Performance & Query Optimization avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer PostgreSQL Performance & Query Optimization ?

Aucune expérience préalable n'est requise. PostgreSQL Performance & Query Optimization sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 2 sur 4.

Combien de temps prend la leçon « Récupérer de l’espace avec pg_repack » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon PostgreSQL Performance & Query Optimization ?

Oui. Chaque leçon PostgreSQL Performance & Query Optimization inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Mesurer précisément la fragmentation des tables et des index
  2. Récupérer de l’espace avec pg_repack
  3. Régler le facteur de remplissage des tables fortement modifiées
  4. Fonctionnement interne de TOAST et stockage des grandes valeurs
← Retour à PostgreSQL Performance & Query Optimization