0Pricing
PostgreSQL Performance & Query Optimization · 강의

pg_repack으로 공간 회수하기

VACUUM FULL에 필요한 배타적 잠금 없이 온라인으로 팽창한 테이블과 인덱스를 재구축하는 방법을 배웁니다.

pg_repack으로 공간 회수하기은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“pg_repack으로 공간 회수하기”에서 뭘 배우나요?

VACUUM FULL에 필요한 배타적 잠금 없이 온라인으로 팽창한 테이블과 인덱스를 재구축하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“pg_repack으로 공간 회수하기” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

  1. 테이블 및 인덱스 팽창 정확하게 측정하기
  2. pg_repack으로 공간 회수하기
  3. UPDATE가 많은 테이블의 Fillfactor 조정
  4. TOAST 내부 구조와 대형 값 저장
← PostgreSQL Performance & Query Optimization(으)로 돌아가기