pg_repack: Online Table and Index Compaction Without Exclusive Locks

# Ubuntu/Debian
apt-get install postgresql-15-repack

# Install the extension in your database
CREATE EXTENSION pg_repack;

# Repack a specific table
pg_repack -h localhost -U postgres -d mydb --table orders

# Repack all tables in database
pg_repack -h localhost -U postgres -d mydb

# Repack only indexes (faster, less I/O)
pg_repack -h localhost -U postgres -d mydb --table orders --only-indexes

# Repack a specific index
pg_repack -h localhost -U postgres -d mydb --index idx_orders_created_at

-- Tables with significant bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_only,
       n_dead_tup,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 10;

1. Create new (empty) table with same schema
2. Create log table to capture changes during copy
3. Add trigger on original table to write changes to log
4. COPY all rows to new table (no lock)
5. Apply log table changes to new table (catch up)
6. Acquire brief lock, apply final delta, swap tables
7. Drop old table and triggers

# Limit I/O impact with wait timeout between chunks
pg_repack -h localhost -U postgres -d mydb \
  --table orders \
  --wait-timeout=60 \
  --no-kill-backend