pg_repack vs VACUUM FULL: Reclaim Bloat Live

Your Database Is Getting Fat and Nobody Told You

It’s 2 AM and you’re staring at a Grafana panel that shows your biggest Postgres table at 87 GB. You know for a fact that the actual data in that table is maybe 25 GB. The rest? Dead rows nobody cleaned up. Welcome to bloat — MVCC’s dirty little secret.

Here’s the thing: Postgres doesn’t delete rows in place. When you UPDATE a row, it writes a new version and marks the old one dead. Same with DELETE. Those dead tuples stick around until autovacuum sweeps through and reclaims them. But autovacuum doesn’t give you the disk space back — it just marks the pages as reusable within the table. The OS still sees the full file size. To actually shrink the table, you need heavier machinery.

Two main tools in the toolbox: VACUUM FULL (nuclear, blocking) and pg_repack (surgical, online). Let’s talk about when you reach for each one, and how to not shoot yourself in the foot with either.

Why Bloat Happens in the First Place

Understanding the root cause saves you from cleaning up the same mess every six months.

MVCC overhead. Multi-Version Concurrency Control is why Postgres can serve reads without blocking writes. The trade-off is that every UPDATE or DELETE leaves a dead row behind. Autovacuum is supposed to clean these up, but it has limits.

Long-running transactions. If a transaction has been open for four hours, autovacuum cannot reclaim any dead tuples that were created after that transaction started. A single forgotten BEGIN with no COMMIT in your analytics job can turn your events table into a landfill.

Aggressive UPDATEs without HOT. Heap Only Tuple (HOT) updates let Postgres reuse space within the same page when the updated columns aren’t indexed. The moment you UPDATE an indexed column, you get a new row on a new page with an updated index entry. Do this millions of times on a high-traffic table and you’ve got Swiss cheese pages everywhere.

fillfactor too high. The default fillfactor is 100, meaning Postgres packs pages completely full on writes. There’s no room left for in-place HOT updates. Setting fillfactor=70 or 80 on write-heavy tables gives each page breathing room and dramatically reduces bloat over time.

Autovacuum falling behind. If your table is getting hit with thousands of writes per second, autovacuum might simply not be able to keep up. It’ll vacuum once and the table will have already grown 10% again by the time it finishes.

Detecting Bloat Before You’re In Crisis Mode

Don’t wait until queries slow to a crawl. Set up monitoring.

Quick sanity check via pg_class

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename)
    - pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

This tells you which tables are biggest. It doesn’t tell you how much is bloat.

Actual bloat estimation with pgstattuple

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  table_len,
  tuple_count,
  tuple_len,
  dead_tuple_count,
  dead_tuple_len,
  free_space,
  round((dead_tuple_len + free_space)::numeric / table_len * 100, 2) AS bloat_pct
FROM pgstattuple('public.events');

This does a full sequential scan, so don’t run it on a 50 GB table during peak hours. It’s accurate though — bloat_pct above 30-40% is your signal to act.

Faster approximation (no full scan)

SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

Less precise than pgstattuple but fast and safe on prod. Dead tuple percentage over 20% and a last_autovacuum timestamp from last week? That’s a problem.

VACUUM FULL: The Nuclear Option

VACUUM FULL rewrites the entire table into a new heap file, reclaims all dead space, and gives the OS back the disk. It’s the simplest solution. It’s also the most dangerous.

The lock situation: VACUUM FULL acquires an AccessExclusiveLock — the strongest lock in Postgres. Nothing can read or write the table while it’s running. Not your app. Not your monitoring queries. Not your analytics dashboards. Everything queues up behind it.

-- This will block all access to the events table until it finishes
VACUUM FULL ANALYZE public.events;

For a 50 GB table with 70% bloat, expect 30-60 minutes of total downtime depending on your hardware. During that time, your connection pool fills up with waiting queries and your app starts returning errors.

CLUSTER is similar but also reorders the rows physically by an index:

CLUSTER public.events USING events_created_at_idx;

Same exclusive lock, same downtime risk. The benefit is that sequential scans on the clustered column become faster afterward. Worth it on range-heavy queries. Not worth the downtime explanation to your boss.

When VACUUM FULL makes sense:

• You have a scheduled maintenance window and the table is small (under 5 GB)
• The table is infrequently accessed and the lock time is acceptable
• You’re on a standalone server with no replicas to worry about
• You need this done in 10 minutes and don’t have time to set up pg_repack

pg_repack: The Actually Useful One

pg_repack does the same thing as VACUUM FULL — rewrites the table, reclaims bloat — but does it without blocking reads or writes for the duration of the operation. It holds a brief exclusive lock only at the very end, for a matter of seconds, to swap the old table out for the new one.

The trick: pg_repack creates a shadow table, copies the data into it, installs triggers on the original table to capture ongoing changes, applies those changes to the shadow table in batches, and when it’s close enough to current, grabs a short lock to do the final swap and index rename.

It’s clever. It works. It’s been around since Postgres 9.x and the current 1.5.x line supports everything through Postgres 17 and 18.

Installation

On Ubuntu/Debian with the PGDG repo:

sudo apt install postgresql-17-repack

Inside Postgres:

CREATE EXTENSION pg_repack;

You need superuser (or at minimum pg_repack role on PG 14+) to use it.

Basic usage

# Repack a single table
pg_repack -h localhost -U postgres -d mydb -t public.events

# Repack with parallel index builds (PG 11+)
pg_repack -h localhost -U postgres -d mydb -t public.events --jobs 4

# Dry run — show what would be repacked without doing it
pg_repack -h localhost -U postgres -d mydb --dry-run

# Repack every table in the database
pg_repack -h localhost -U postgres -d mydb

Useful flags

# Skip the superuser check (useful in managed environments where you have repack role but not full superuser)
pg_repack -h localhost -U repack_user -d mydb -t public.events --no-superuser-check

# Control how many rows to apply from the trigger log at a time (default 1000)
# Lower this if you want less impact per batch
pg_repack -h localhost -U postgres -d mydb -t public.events --apply-count 500

# How long to wait for conflicting locks during final table swap (default 60s)
pg_repack -h localhost -U postgres -d mydb -t public.events --wait-timeout 30

What it looks like when running

INFO: repacking table "public"."events"
INFO: query: SELECT pg_repack.repack_table_with_params('1234567', '{"..."}')
INFO: progress: 5728000/45100000 rows (12.7%)
INFO: progress: 12100000/45100000 rows (26.8%)
INFO: progress: 24900000/45100000 rows (55.2%)
INFO: progress: 38700000/45100000 rows (85.8%)
INFO: progress: 45100000/45100000 rows (100%)
INFO: applying trigger log
INFO: finalizing
INFO: done repacking table "public"."events"

The disk space gotcha

pg_repack builds a full copy of the table before dropping the old one. If your events table is 50 GB with 70% bloat, the live data is about 15 GB. But pg_repack will temporarily hold both the old 50 GB file and the new 15 GB file at the same time. You need at least 65 GB of free space on the tablespace before you start. Check first:

SELECT pg_size_pretty(pg_database_size('mydb'));

-- Check tablespace free space
SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

Then df -h on that mount point. If you’re cutting it close, move the repack temp tables to a different tablespace with --tablespace:

pg_repack -h localhost -U postgres -d mydb -t public.events --tablespace pg_default

pg_squeeze: The Set-It-and-Forget-It Alternative

pg_squeeze is a newer extension that runs as a Postgres background worker. Instead of you invoking it manually, you configure schedules and it handles bloat automatically.

CREATE EXTENSION pg_squeeze;

-- Tell pg_squeeze to watch the events table
SELECT squeeze.add_table(
  'public', 'events',
  '01:00:00',  -- run at 1 AM
  30,          -- min free space percent to trigger
  50           -- min dead tuple percent to trigger
);

The trade-off: less control, more magic. pg_squeeze is great for set-and-forget environments where you don’t want to babysit bloat. It’s newer and has less production mileage than pg_repack. For a homelab or small team, pg_repack run monthly via cron is usually fine. pg_squeeze starts making sense when you have 50+ tables that all need watching.

When pg_repack Is Wrong for the Job

pg_repack isn’t magic. There are situations where it’ll either fail or cause you problems:

Tables without a primary key. pg_repack requires a PK or unique not-null index to track row identity during the copy phase. If your table has no PK, you’ll get an error. Add one, or use VACUUM FULL during a window.

Logical replication targets. If you’re using logical replication and the table being repacked is a publication member, the brief exclusive lock at the end of the swap can cause replication lag or slot invalidation depending on your replica setup. Test this in staging. Coordinate with your replica configuration before repacking published tables.

Very high write rate tables. pg_repack works by catching up with changes as it copies. If your table is getting 50,000 writes per second, the trigger log may grow faster than pg_repack can drain it. The operation will technically complete but it’ll take much longer than expected. Monitor the trigger log table size during the run.

Real Example: 50 GB Events Table, 70% Bloat

Here’s what both approaches actually look like on a real table:

Setup: public.events, 50 GB on disk, pgstattuple reports 70% bloat. Actual live data: ~15 GB. Postgres 17, 8-core server, NVMe storage.

VACUUM FULL route:

-- Lock acquired immediately, nothing can touch this table
VACUUM FULL ANALYZE public.events;
-- ... 45 minutes later
VACUUM
-- Table is now 15 GB, OS sees the freed space

Total downtime: 45 minutes. Every query against events during that window either errors or queues. Your connection pool probably hit max_connections around minute 3.

pg_repack route:

pg_repack -h localhost -U postgres -d mydb \
  -t public.events \
  --jobs 4 \
  --apply-count 1000 \
  --wait-timeout 60

Total wall time: 2.5 hours. During that time, reads and writes against events worked normally. There was a ~2 second pause at the final swap where pg_repack grabbed the exclusive lock, renamed tables, rebuilt index references, and released. Application saw it as a brief hiccup. Monitoring showed it as a 2-second spike in lock wait time.

The trade-off is clear: you’re trading 45 minutes of total outage for 2.5 hours of slightly elevated resource usage (CPU for index builds, extra disk IO for the shadow table copy) with zero application impact. That’s almost always the right trade.

Monitoring During the Operation

While VACUUM FULL is running, watch pg_stat_progress_vacuum:

SELECT
  pid,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  round(heap_blks_vacuumed::numeric / NULLIF(heap_blks_total, 0) * 100, 1) AS pct_done
FROM pg_stat_progress_vacuum;

While pg_repack is running, watch for lock conflicts:

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;

If you see a pile-up of lock waits during the final swap phase, that’s normal — they’ll all clear once the swap completes in a few seconds. If they’re happening during the copy phase, something else is wrong.

The Bottom Line

Use VACUUM FULL when:

• The table is small (under 5 GB) and you have a maintenance window
• You can tolerate the downtime and want simplicity
• The table has no primary key and pg_repack won’t work
• You’re on a standalone instance with no logical replicas

Use pg_repack when:

• The table is large enough that downtime is unacceptable
• Your app can’t afford even a 5-minute maintenance window
• You have a primary key on the table (required)
• You’re willing to trade extra disk space and 2-3x longer wall time for zero app impact

Whatever you pick, fix the root cause too. Check autovacuum settings for your write-heavy tables, look at your fillfactor, and hunt down any long-running transactions. Reclaiming bloat is a one-time fix. Not creating it in the first place is the actual solution.

-- Check if autovacuum is keeping up — tables with high dead tuple counts
SELECT
  schemaname,
  tablename,
  n_dead_tup,
  last_autovacuum,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

Your 2 AM self will appreciate having run this before it became a crisis.

Related Reading

• Adding NOT NULL on a Big Table Without Downtime
• ClickHouse vs DuckDB vs StarRocks: Light OLAP
• Dragonfly vs Redis: Single-Binary Performance
• DuckDB for Local Analytics: Pandas on Steroids
• KeyDB: A Redis Fork With Multi-Threading