0Pricing
PostgreSQL Performance & Query Optimization · レッスン

pg_repackによる領域の再利用

VACUUM FULLで必要になる排他的ロックなしに、膨張したテーブルとインデックスをオンラインで再構築します。

「pg_repackによる領域の再利用」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

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.

よくある質問

「pg_repackによる領域の再利用」レッスンは無料ですか？

はい。「pg_repackによる領域の再利用」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「pg_repackによる領域の再利用」で何を学びますか？

VACUUM FULLで必要になる排他的ロックなしに、膨張したテーブルとインデックスをオンラインで再構築します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「pg_repackによる領域の再利用」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. テーブルとインデックスの膨張を正確に測定する
  2. pg_repackによる領域の再利用
  3. 更新の多いテーブルのFillfactorチューニング
  4. TOASTの内部構造と大きな値の格納
← PostgreSQL Performance & Query Optimizationに戻る