pg_repack
pg_repack — the only safe way to fully eliminate bloat in a table or index on a live prod without long blocking. ide99 runs pg_repack through UI with progress and safety checks.
Why
After mass UPDATE or DELETE, a Postgres table accumulates "holes" — old row versions (dead tuples). VACUUM marks them free but doesn't return space to disk. VACUUM FULL does, but takes an AccessExclusiveLock — the table is fully blocked. Often unacceptable on prod.
pg_repack works around it: creates a copy of the table next to it, copies the data, syncs through a trigger, and at the end atomically swaps — the lock is held for fractions of a second.
Install
CREATE EXTENSION pg_repack;
The extension requires:
superuser to install- The
pg_repack CLI utility on the same machine where you run it
- On managed Postgres (Supabase, Neon, RDS) typically unavailable — use the alternative: migrate to a partitioned table via pg_partman.
Use through ide99
- Open Health screen → Bloat section.
- Find a table or index with high bloat (typically > 30%).
- Repack button on the row.
A wizard opens:
- Target: the whole table, a single index, or several indexes
- Order by: optionally sort during repack by a key (cluster effect — related rows lay together, cache works better)
- Jobs: parallelism level (default 1, increase if you have free CPU)
- Without dropping old: keep the old copy for rollback if anything goes wrong
Command preview:
pg_repack \
-h db.example.com -p 5432 -U postgres -d mydb \
--table public.events \
--order-by 'event_at DESC' \
--jobs 2
The Run repack button starts. ide99 shows live progress:
[1/4] Creating temp tables and triggers... ✓ 2s
[2/4] Copying tuples... 42% — 18M / 42M rows
[3/4] Creating indexes...
[4/4] Swapping tables...
You can cancel — pg_repack cleanly rolls back its temp table.
When to use
| Situation |
Solution |
| Table < 10 GB, can be blocked |
VACUUM FULL (simpler and faster) |
| Table with active writes on prod |
pg_repack |
| Often only the index is bloated |
REINDEX CONCURRENTLY (also non-blocking) |
| Data keeps growing, old data is deleted |
Partitioning via pg_partman |
Requirements
- The table must have a Primary Key or Unique Index (NOT NULL columns) — without it,
pg_repack can't track changes via trigger.
- ~× 2 the table size in free disk (a copy is created).
ide99 verifies both before running and warns if something's off.
Tip: scheduling
Don't run repack at peak — even without locking, CPU and I/O load grows. Good windows:
- Off-hours for your audience
- Weekends
- Right after a release, when load typically dips
Next