`VACUUM FULL` vs `pg_repack`: how to recover space without downtime

You reached the case where autovacuum isn't enough anymore. You have a table with severe bloat (60%+) accumulated by historical updates, and you need to recover the physical space. Normal autovacuum cleans up dead tuples but doesn't give the space back to the operating system — the table still weighs the same on disk. To reclaim the physical space there are two tools: VACUUM FULL (built-in, dangerous) and pg_repack (an extension, safe).

The difference between them determines whether your intervention is scheduled maintenance or an accidental downtime. VACUUM FULL takes an AccessExclusiveLock during the whole operation — it blocks all queries (reads and writes) on the table. On large tables this can take hours. In production this means real downtime for your users.

pg_repack does exactly the same work (physical compaction + space recovery) but online: it uses a temporary table in parallel, syncs changes via triggers, and at the end does an atomic swap. The original table keeps accepting queries during the whole process. The final swap lock lasts milliseconds.

In this capsule you're going to learn when each tool is appropriate (the short answer: almost always pg_repack in production), how to install and use pg_repack correctly, and what precautions to take before running it.

---

Why you need to reclaim physical space

Quick review of the problem (capsule 05): when UPDATEs create dead tuples and autovacuum cleans them up, the space inside the pages becomes reusable but the pages stay occupied. If your table grew to 100GB during a period of intense UPDATEs and then stabilized, the 100GB keep occupying disk even though the live content is only 30GB.

The reusable space gets consumed on its own only if new INSERTs keep arriving. But in many cases:

• The table has a "grew and now only updates" pattern — it never recovers the space.
• The table had a one-time event (a massive cleanup, a badly done migration) that generated bloat all at once.
• Disk space is a direct monetary cost (a cloud instance with storage charged by GB).
• The bloat affects performance via cache hit rate (more pages to cache).

In those cases you need to give the space back to the OS. Regular VACUUM doesn't do it. Only VACUUM FULL or pg_repack.

---

Why VACUUM FULL is almost always a bad idea

VACUUM FULL rewrites the whole table into new files and releases the old files. The internal process:

1. Takes an AccessExclusiveLock on the table (blocks everything).
2. Creates new files on disk.
3. Copies the live tuples in compact order.
4. Rebuilds the indexes.
5. Deletes the old files.
6. Releases the lock.

During the whole steps 1-5, the table is completely blocked. Any query (including a simple SELECT) stays waiting. Connected applications see timeouts, 500 errors, hung connections.

How long does it take? It depends on the size:

• 1GB table: ~1-3 minutes.
• 10GB table: ~10-30 minutes.
• 100GB table: ~2-6 hours.
• 1TB table: many hours, frequently failing due to a full disk (it needs 2x the space temporarily).

Cases where VACUUM FULL is acceptable:

• Scheduled maintenance with the app off.
• Small tables (<1GB) where the lock lasts seconds.
• Non-critical tables that can be offline for 30 minutes.
• Dev/staging databases without users.

Cases where it is NOT acceptable (most of them):

• Any production table with continuous traffic.
• Large tables (>10GB) where the lock lasts more than 10 minutes.
• Apps with a strict uptime SLA.

-- ⚠️  DANGER in production
VACUUM FULL orders;

-- Variant with verbose to see progress
VACUUM (FULL, VERBOSE) orders;

---

pg_repack: the right alternative

pg_repack (originally pg_reorg) is an extension that reorganizes tables online — without downtime, without long locks. The technique:

1. Creates a new (empty) table with the same structure.
2. Puts triggers on the original table to capture changes.
3. Copies the live tuples in compact order to the new table.
4. Applies the changes captured by the triggers to the new table.
5. Does an atomic swap (renames the original table → something else, renames the new one → the original). This lock lasts milliseconds.
6. Deletes the old table.

Result: the same compaction as VACUUM FULL, but the original table accepts queries during the whole process (except the milliseconds of the swap). In production this difference is what separates "routine maintenance" from an "incident."

Installation

pg_repack comes as an OS package + a PostgreSQL extension.

On Ubuntu/Debian:

sudo apt-get install postgresql-16-repack

On macOS with Homebrew:

brew install pg_repack

In Docker (official PostgreSQL):

FROM postgres:16
RUN apt-get update && apt-get install -y postgresql-16-repack

In cloud (RDS, Supabase, etc.):

Verify that your provider supports it. Most do (it's a very common extension). For RDS: it requires configuring shared_preload_libraries and rds.extensions.

After installing, enable the extension in each database:

CREATE EXTENSION pg_repack;

Basic usage

pg_repack runs as a command-line tool, not as SQL:

# Repack a specific table
pg_repack -d production_db -t orders

# Repack several tables
pg_repack -d production_db -t orders -t customers -t events

# Repack with verbose (recommended to understand what it does)
pg_repack -d production_db -t orders --verbose

# Repack the whole database (all tables with bloat)
pg_repack -d production_db --all

Typical output:

INFO: repacking table "public.orders"
INFO: created table "_pg_repack_orders" for repack
INFO: copied 2,453,892 tuples in 142.34 seconds
INFO: applied 1,234 deltas in 0.45 seconds
INFO: swapped tables in 23ms
INFO: dropped old table

Repacking indexes

pg_repack can also rebuild indexes online:

# Only rebuild a table's indexes
pg_repack -d production_db -t orders --only-indexes

# Rebuild a specific index
pg_repack -d production_db -i idx_orders_customer

This is useful when an index has bloat but the table is clean. Faster than REINDEX CONCURRENTLY and works the same.

---

Precautions before running pg_repack

pg_repack is safe but it has requirements.

1. The table needs a primary key or a unique index

pg_repack uses the PK to sync the changes captured by the triggers. Without a PK it can't do its work.

-- Check
SELECT
    tc.constraint_name,
    tc.constraint_type
FROM information_schema.table_constraints tc
WHERE tc.table_name = 'orders'
  AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE');

If there's no PK, add one first:

ALTER TABLE orders ADD PRIMARY KEY (id);

2. You need free disk space

pg_repack creates a parallel copy of the table. You need at least 2x the current size available on disk. For a 100GB table with 60% bloat, you need ~140GB free (the new table will weigh ~40GB but the old one stays until the end).

Check:

df -h /var/lib/postgresql/data

3. The original table's triggers stay active

pg_repack adds new triggers to capture changes, but the existing triggers keep working. If you have triggers that touch other tables, those keep executing. Generally not a problem.

4. Replication slots and standby

If you have logical or physical replication, pg_repack generates a lot of WAL temporarily. If your standby is near the limit or you have inactive replication slots, it can cause problems. Monitor during the repack.

5. Don't run it during peak load

Even though it's online, pg_repack consumes significant I/O. Running it during the day's peak degrades other queries. Schedule it for low-activity hours (typically 3-5am).

6. Tables with active LISTEN/NOTIFY in triggers

If you have triggers that fire NOTIFY, pg_repack generates extra notifications during the copy. If your app processes those notifications, prepare it (e.g.: filter out the notifications generated by the repack).

---

Side-by-side comparison

Aspect	VACUUM FULL	pg_repack
Recovers physical space	✅	✅
Reorders tuples physically	✅	✅
Rebuilds indexes	✅	✅
Lock during the operation	AccessExclusive (blocks everything)	Only milliseconds at the end
Allows SELECT during	❌	✅
Allows INSERT/UPDATE/DELETE	❌	✅
Time on a 100GB table	2-6 hours (of downtime)	2-6 hours (no downtime)
Extra space required	~2x (temporary)	~2x (temporary)
Built-in vs extension	Built-in	External extension
Risk of failure	Low	Low
Recommended in production	❌	✅

The operation takes the same amount of time. The difference is whether your app is down or not during those hours.

---

When to choose each one

Choose VACUUM FULL if:

• Scheduled maintenance with the app off.
• A very small table (<500MB) where the lock lasts seconds.
• No access to pg_repack (a restricted environment without permissions for extensions).
• Learning in a test environment without traffic.

Choose pg_repack if:

• A production table with active traffic.
• A large table (>5GB) where the lock would last minutes or hours.
• You want to reorganize indexes online.
• You want maintenance without coordinating downtime windows.

In the practice of a serious backend team: install pg_repack from day 1 on any production database. The probability of needing it eventually is high, and discovering it in the middle of an incident is worse.

---

Operational pattern: detect and repair

You're going to integrate what you learned in capsules 05 and 06.

Periodic detection (monthly cron or more frequent)

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
  AND ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 30
ORDER BY pg_relation_size(schemaname || '.' || relname) DESC;

Tables in this result are candidates for pg_repack.

Repair with verification

#!/bin/bash
# repack_table.sh

DB=$1
TABLE=$2

# Check available space
SIZE_BYTES=$(psql -d $DB -tAc "SELECT pg_total_relation_size('$TABLE')")
SIZE_GB=$((SIZE_BYTES / 1024 / 1024 / 1024))
FREE_GB=$(df -BG /var/lib/postgresql | tail -1 | awk '{print $4}' | sed 's/G//')

if [ $FREE_GB -lt $((SIZE_GB * 2 + 5)) ]; then
    echo "ERROR: insufficient disk space"
    echo "Need: $((SIZE_GB * 2 + 5))GB, have: ${FREE_GB}GB"
    exit 1
fi

# Check that it has a PK
HAS_PK=$(psql -d $DB -tAc "SELECT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE table_name = '$TABLE' AND constraint_type = 'PRIMARY KEY'
)")

if [ "$HAS_PK" != "t" ]; then
    echo "ERROR: table has no primary key"
    exit 1
fi

# Run the repack
echo "Repacking $TABLE in $DB..."
pg_repack -d $DB -t $TABLE --verbose

# Check afterward
psql -d $DB -c "
    SELECT
        relname,
        pg_size_pretty(pg_total_relation_size('$TABLE')) AS new_size,
        n_dead_tup
    FROM pg_stat_user_tables
    WHERE relname = '$TABLE';
"

This script has the minimum checks: disk space, PK exists. In real production you'd add more (alerts, coordination locks, metrics).

Monthly cron job

# /etc/cron.d/pg-maintenance
# Monthly repack of large tables with bloat
0 3 1 * * postgres /usr/local/bin/monthly_repack.sh production_db

Where monthly_repack.sh queries the tables with high bloat and processes them one by one with a gap between each one so as not to saturate I/O.

---

Traps and common mistakes

1. Running VACUUM FULL "because it's simpler" in production.

The operational simplicity of not installing an extension doesn't make up for the downtime. pg_repack is installed once and used many times. The investment of configuring the extension pays off dozens of times.

2. Forgetting to check space before the repack.

pg_repack needs ~2x the size temporarily. If you run out of disk halfway through, pg_repack fails and leaves artifacts (the _pg_repack_* table) that you have to clean up manually. Always check before.

3. Thinking that pg_repack fixes everything automatically.

pg_repack recovers space. It doesn't fix:

• Unnecessary indexes (it's still your decision which ones to keep).
• UPDATE patterns that generate bloat (capsule 05).
• Old stats (capsule 03).

It's a repair tool, not a prevention one. Prevention is still well-tuned autovacuum.

4. Running the repack during peak load.

Even though it's online, it consumes significant I/O. At the day's peak you degrade general latency. Schedule it for low-activity hours.

5. Not monitoring the repack while it runs.

Long processes can fail for various reasons (full disk, OOM kill, replication lag). Monitor the logs with tail -f. In production, have an alert if the process dies.

6. Repacking a table in the middle of DDL.

If you have an ALTER TABLE planned on a table, don't run pg_repack simultaneously. The atomic swaps can collide. Coordinate the timing.

7. Repacking to "physically order" instead of to reclaim space.

pg_repack reorders by PK by default. If you want to order by another column (e.g.: cluster by created_at to improve locality), use pg_repack -t orders --order-by created_at. But be clear that this is advanced usage — most cases just want to reclaim space, not reorder.

---

Exercise: simulate and resolve severe bloat

Setup:

DROP TABLE IF EXISTS bloat_repack_demo;
CREATE TABLE bloat_repack_demo (
    id SERIAL PRIMARY KEY,
    name TEXT,
    counter INTEGER DEFAULT 0,
    payload TEXT
);

CREATE INDEX idx_bloat_repack_name ON bloat_repack_demo(name);

-- Insert 50k rows with a medium payload
INSERT INTO bloat_repack_demo (name, payload)
SELECT
    'name_' || generate_series,
    repeat('x', 500)
FROM generate_series(1, 50000);

ANALYZE bloat_repack_demo;

Step 1: measure the initial size.

SELECT
    pg_size_pretty(pg_total_relation_size('bloat_repack_demo')) AS total_size,
    pg_size_pretty(pg_relation_size('bloat_repack_demo')) AS table_only;

Step 2: create severe bloat.

-- 20 rounds of UPDATE — should generate a lot of bloat
DO $$
BEGIN
    FOR i IN 1..20 LOOP
        UPDATE bloat_repack_demo SET counter = counter + 1;
    END LOOP;
END $$;

-- Measure after the bloat (without VACUUM)
SELECT
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_total_relation_size('bloat_repack_demo')) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'bloat_repack_demo';

Step 3: run a regular VACUUM and measure.

VACUUM bloat_repack_demo;

SELECT
    n_live_tup,
    n_dead_tup,
    pg_size_pretty(pg_total_relation_size('bloat_repack_demo')) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'bloat_repack_demo';

Question: did the dead tuples go down? Did the physical size go down?

Step 4: simulate the production dilemma.

If this were a production table with active traffic, what would you do?

a) VACUUM FULL bloat_repack_demo; b) pg_repack -t bloat_repack_demo c) Wait for autovacuum to reuse the space with future INSERTs

Justify your choice.

Step 5: run VACUUM FULL (it's safe in the demo) and measure.

VACUUM FULL bloat_repack_demo;

SELECT pg_size_pretty(pg_total_relation_size('bloat_repack_demo')) AS total_size;

By how much did it shrink?

See solution

Step 1: initial size ~30-40MB for 50k rows with a 500-byte payload.

Step 2: after 20 UPDATEs, dead_tup ~1M (50k × 20), physical size grows 5-10x.

Step 3: after a regular VACUUM:

• n_dead_tup → 0 (cleaned up).
• Physical size does NOT change (still the large one). Space within pages marked reusable.

Step 4: the right answer is (b) pg_repack if it were production.

• (a) VACUUM FULL would lock the table during the whole compaction. On a 30MB table it's fast, but in production at scale it's downtime.
• (b) pg_repack does the same thing online.
• (c) Waiting for INSERTs is slow and unpredictable — and if the table never receives new INSERTs, it never shrinks.

Step 5: after VACUUM FULL, physical size goes back to ~30-40MB (similar to the initial one). Space recovered.

Lesson: understanding that VACUUM and VACUUM FULL are distinct operations with distinct results is critical. And understanding why pg_repack exists (the online version of VACUUM FULL) is what differentiates a senior dev from a junior one when the moment comes to decide in production.

---

Summary and next step

What you learned:

• Regular VACUUM cleans up dead tuples but doesn't release physical space to the OS.
• VACUUM FULL releases space but locks the table during the whole operation. In production it's almost always a bad choice.
• pg_repack is the online alternative: the same compaction, without downtime (except the milliseconds of the final swap).
• pg_repack requirements: a PK or a unique index, ~2x disk space, installing the extension.
• Recommended operational pattern: a monthly cron that detects tables with high bloat and runs pg_repack.
• pg_repack also rebuilds indexes online (faster than REINDEX CONCURRENTLY).

Before moving on, you should be able to:

• Decide between VACUUM, VACUUM FULL, and pg_repack for a given scenario.
• Verify the prerequisites before running pg_repack.
• Identify candidate tables for a repack with pg_stat_user_tables.
• Justify to your team why pg_repack should be installed on any production database.

In the next capsule we close the module with two things. First, the cost parameters the planner uses: random_page_cost, effective_cache_size, and why the defaults are a legacy of the HDD era. Adjusting them correctly for SSD/NVMe is one of the highest-impact and lowest-risk tunings on any modern installation. And second, the module's mini-project: a bookstore with a planner that ignores an index, where you're going to apply everything from capsules 02-07 to diagnose and fix it.

---

Resources

1. pg_repack — Documentation — the project's official reference.
2. PostgreSQL Docs — VACUUM — reference including VACUUM FULL.
3. Crunchy Data — pg_repack tutorial — an operational guide with examples.
4. GitLab — How we use pg_repack — a real case of use at scale.
5. Heroku — pg_repack on Heroku Postgres — usage in managed cloud.
6. depesz — When VACUUM is not enough — deep analysis of the problem and solutions.
7. Citus Data — Bloat removal techniques — a comparison of approaches.

---

Capsule 07 of 08 — Module 7 — Database Performance & Query Tuning Guide