When PostgreSQL Starts Eating Your Disk (and Why VACUUM Isn’t Enough)
A few years back, I was troubleshooting a slow PostgreSQL instance on a client’s production server. Queries that should have finished in milliseconds were crawling. The pg_relation_size() showed a 40 GB table — but the actual row count only justified about 8 GB. The remaining 32 GB was dead weight: bloat.
Having worked with MySQL, PostgreSQL, and MongoDB across different projects, each has its own strengths — but PostgreSQL’s MVCC (Multi-Version Concurrency Control) model is both its greatest feature and the root cause of this specific problem. Every UPDATE or DELETE doesn’t overwrite rows in place; it marks old versions as dead and writes new ones. Over time, especially on high-write tables, these dead tuples pile up and inflate your table and index sizes.
The standard fix is VACUUM and AUTOVACUUM. They reclaim dead tuple space and make it reusable — but they don’t shrink the physical file on disk. The only way to fully reclaim that space is VACUUM FULL, which rewrites the entire table. The catch: VACUUM FULL takes an exclusive lock, meaning your entire table is offline while it runs. On a production database with millions of rows, that’s anywhere from minutes to hours of downtime.
That’s where pg_repack comes in. It rebuilds bloated tables and indexes live, with only a brief lock at the very end to swap the old and new versions. Production stays up. Users don’t notice a thing.
Understanding Bloat Before Fixing It
Before installing anything, you should confirm you actually have a bloat problem. Run this query to estimate table bloat across your database:
SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(
pg_relation_size(schemaname || '.' || tablename) -
(reltuples * 8192 / fillfactor::float)::bigint
) AS estimated_bloat
FROM pg_stat_user_tables
JOIN pg_class ON relname = tablename
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;A more precise approach is the pgstattuple extension:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
table_len,
tuple_count,
dead_tuple_count,
dead_tuple_percent,
free_space,
free_percent
FROM pgstattuple('public.orders');If dead_tuple_percent or free_percent is above 20–30%, the table is a candidate for repacking.
Installing pg_repack
pg_repack is a PostgreSQL extension — it runs server-side code during the repack process. You need to install both the server extension and the client CLI tool.
On Ubuntu / Debian
sudo apt update
sudo apt install postgresql-16-repack # Replace 16 with your PG versionOn RHEL / CentOS / Rocky Linux
sudo dnf install pg_repack_16 # From PGDG repoFrom source (when packages aren’t available)
git clone https://github.com/reorg/pg_repack.git
cd pg_repack
make
sudo make installAfter installation, enable the extension on your target database:
-- Connect to your database
CREATE EXTENSION IF NOT EXISTS pg_repack;Verify it loaded correctly:
SELECT * FROM pg_extension WHERE extname = 'pg_repack';Configuring and Running pg_repack
pg_repack works by creating a shadow copy of your table, replaying all changes that happen during the copy using triggers, then swapping the tables with a short ACCESS EXCLUSIVE lock — typically under a second for most tables.
Repack a single bloated table
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--table=public.ordersRepack only the indexes on a table (no table rewrite)
Sometimes only the indexes are bloated — this is faster and even less intrusive:
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--table=public.orders \
--only-indexesRepack all tables in a database
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--allUse the --dry-run flag first to see what pg_repack would do without actually doing it:
pg_repack --dbname=mydb --table=public.orders --dry-runControlling resource usage
On a busy production server, you don’t want pg_repack hammering your I/O. Two flags help here:
pg_repack \
--dbname=mydb \
--table=public.orders \
--wait-timeout=10 \
--no-kill-backend--wait-timeout: How many seconds to wait for conflicting locks before giving up (default: 60s)--no-kill-backend: Don’t kill other connections — just wait or abort
For tables that receive heavy write traffic, consider running pg_repack during your lowest-traffic window (typically 3–5 AM) and wrapping it in a shell script:
#!/bin/bash
# repack_bloated.sh — run via cron during off-peak hours
DB="mydb"
PG_USER="postgres"
TABLES=("public.orders" "public.events" "public.logs")
for TABLE in "${TABLES[@]}"; do
echo "[$(date)] Repacking $TABLE..."
pg_repack \
--dbname=$DB \
--username=$PG_USER \
--table=$TABLE \
--wait-timeout=30 \
--no-kill-backend
echo "[$(date)] Done: $TABLE"
doneAdd it to crontab for weekly maintenance:
crontab -e
# Run every Sunday at 3 AM
0 3 * * 0 /usr/local/bin/repack_bloated.sh >> /var/log/pg_repack.log 2>&1Verification and Monitoring
After running pg_repack, confirm the bloat is actually gone. Compare table size before and after:
SELECT
pg_size_pretty(pg_relation_size('public.orders')) AS table_size,
pg_size_pretty(pg_indexes_size('public.orders')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;Run pgstattuple again to confirm dead tuples are cleared:
SELECT
dead_tuple_count,
dead_tuple_percent,
free_percent
FROM pgstattuple('public.orders');A well-repacked table should show dead_tuple_count = 0 and free_percent near 0 (or near your configured fillfactor).
Monitor bloat growth over time
Bloat will grow back — that’s normal. The goal is to keep it manageable. Set up a monitoring query you can run weekly or pipe into Grafana/Datadog:
SELECT
schemaname || '.' || relname AS table_name,
n_dead_tup,
n_live_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;If dead_pct keeps climbing on a specific table despite regular autovacuum, it usually means autovacuum can’t keep up with write volume. You can tune it per-table:
ALTER TABLE public.orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- Trigger at 1% dead tuples
autovacuum_analyze_scale_factor = 0.005
);What to watch in production logs
pg_repack logs its progress to stdout. The key lines to verify a clean run:
INFO: repacking table "public"."orders"
INFO: repacking index "orders_pkey"
INFO: repacking index "orders_user_id_idx"
INFO: applying last changes on the target table
INFO: cleaning up temprary objectsIf you see ERROR: query was canceled due to conflict with recovery, that means you’re on a standby replica — pg_repack only runs on the primary. If you see ERROR: lock wait timeout exceeded, increase --wait-timeout or wait for less traffic.
A Few Things I Learned the Hard Way
pg_repack needs enough free disk space to hold a full copy of the table while rebuilding. If your 40 GB orders table is being repacked, you need at least 40 GB free. Check before you start:
df -h /var/lib/postgresql/Also, pg_repack temporarily installs triggers on the source table to track changes during the copy phase. Those triggers add a small write overhead. On extremely high-throughput tables (millions of writes per minute), test in staging first to confirm the overhead is acceptable.
Finally — pg_repack can’t repack tables without a primary key. If you have any HEAP tables without PKs, either add one first or use CLUSTER as a fallback (which does lock, but may be acceptable during a maintenance window).
The combination of weekly pg_repack runs, properly tuned autovacuum per high-write table, and a bloat monitoring query is the setup that’s served me well across multiple production PostgreSQL deployments. It’s not glamorous, but it’s the kind of operational habit that keeps queries fast and disk costs predictable.
