Reorganize a bloated table without long locks (pg_repack)

Command

-- Once per target database (the pg_repack package must be installed on the server)
CREATE EXTENSION pg_repack;

# Dry run: show what would be done
pg_repack -h localhost -U postgres -d mydb --table=public.orders --dry-run

# Rewrite the table and its indexes: 2 parallel index builds, wait at most 60 s for locks
pg_repack -h localhost -U postgres -d mydb --table=public.orders --jobs=2 --wait-timeout=60 --no-kill-backend

# Rebuild indexes only
pg_repack -h localhost -U postgres -d mydb --table=public.orders --only-indexes

What it does

Builds a compacted copy of the table in the background, captures concurrent changes into a log table via a trigger, and swaps the tables at the end under a brief ACCESS EXCLUSIVE lock; unlike VACUUM FULL it does not block reads/writes for the duration.

When to use it

When severe bloat that VACUUM cannot return (verified with pgstattuple) must be reclaimed on a live system.

Caution

The table must have a PRIMARY KEY or a UNIQUE index on NOT NULL columns. It temporarily needs extra disk space equal to the table plus its indexes and generates heavy WAL (replica lag, archive volume). ACCESS EXCLUSIVE locks are needed at the start and at the end; when --wait-timeout expires the default is to cancel conflicting queries and, if needed, terminate their sessions, while --no-kill-backend skips the table instead. Do not run DDL on the target table while it runs. Client and extension versions must match. If interrupted, clean up leftovers with DROP EXTENSION pg_repack CASCADE; CREATE EXTENSION pg_repack;