pg_repack rebuilds bloated PostgreSQL tables and indexes online, without the exclusive lock that VACUUM FULL requires. It is the right tool for reclaiming wasted space in production.
Why Not VACUUM FULL?
VACUUM FULLacquires an exclusive lock for the entire duration- For a 100 GB table, that can mean hours of table unavailability
pg_repackdoes the same work while the table remains readable and writable
Installation
bash
# Ubuntu/Debian
apt-get install postgresql-15-repack
# Install the extension in your database
CREATE EXTENSION pg_repack;Basic Usage
bash
# 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_atCheck Bloat Before Running
sql
-- 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;How pg_repack Works
text
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 triggersThrottling
bash
# Limit I/O impact with wait timeout between chunks
pg_repack -h localhost -U postgres -d mydb \
--table orders \
--wait-timeout=60 \
--no-kill-backendNote: pg_repack requires approximately 2x the table size in free disk space during the operation. Check available disk space before running on large tables.
Key Takeaways
- Use pg_repack instead of
VACUUM FULLfor production — no exclusive lock - Check available disk space first — pg_repack needs ~2x table size free
- Use
--only-indexesto compact just indexes, which is faster and less risky - Schedule during low traffic — the final table swap takes a brief exclusive lock
JusDB Can Help
Table bloat silently wastes disk and slows queries. JusDB can schedule and execute pg_repack operations as part of routine PostgreSQL maintenance.