Remove PostgreSQL Table Bloat in Django With pg_repack

Problem statement

A Django table that sees heavy updates or deletes, such as sessions, task results, carts, or notifications, has grown to many times the size its row count suggests. Queries scan more pages than necessary, backups take longer, and disk usage keeps rising even after you delete old rows. That wasted space is bloat: dead row versions that vacuum marked reusable but never returned to the operating system. VACUUM FULL would reclaim it but locks the table for the whole rewrite. pg_repack rebuilds tables online.

1. Measure bloat with the pgstattuple extension or a bloat estimation query.
2. Fix the cause first: tune autovacuum for the table and delete old rows in batches.
3. Install the pg_repack extension and client matching your PostgreSQL version.
4. Run pg_repack --table=public.django_session --jobs=2 -d shop during low traffic.
5. For index-only bloat, use REINDEX INDEX CONCURRENTLY instead.

Step-by-step solution

1. Measure

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT pg_size_pretty(table_len) AS size, dead_tuple_percent, free_percent
FROM pgstattuple('public.django_session');

pgstattuple reads the whole table, so run it off-peak on large tables, or use pgstattuple_approx. A table with free_percent above 30 to 50 percent and a size much larger than its live data is a repack candidate. Compare pg_total_relation_size with an estimate of rows times average row width for a quick sanity check.

2. Fix the cause

Bloat returns if autovacuum cannot keep up. For high-churn Django tables:

ALTER TABLE django_session SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000);

Long-running transactions (including idle-in-transaction sessions) stop vacuum from removing dead rows anywhere in the database; fix those too. Delete expired rows regularly (python manage.py clearsessions, task result expiry) in batches.

3. Install pg_repack

The server needs the extension and the client binary must match the server's major version:

sudo apt-get install -y postgresql-17-repack
psql -d shop -c "CREATE EXTENSION pg_repack;"

Amazon RDS, Aurora, Cloud SQL, and Azure Flexible Server support pg_repack as an extension; run the client from a machine with network access, using the provider's admin role. Each table must have a primary key or a non-null unique index.

4. Repack the table

pg_repack -h db.internal -U postgres -d shop --table=public.django_session --jobs=2 --wait-timeout=60

pg_repack creates a new copy of the table, records concurrent changes with a trigger, applies them, rebuilds indexes, and swaps the tables with a short ACCESS EXCLUSIVE lock. It needs free disk space roughly equal to the table plus its indexes. --wait-timeout makes it give up (or cancel conflicting queries with --no-kill-backend unset) if it cannot get the lock quickly; long-running queries on the table delay the swap.

5. Reindex bloated indexes

If tables are fine but indexes are bloated (common after mass deletes):

REINDEX INDEX CONCURRENTLY django_session_expire_date_a5c62663;

On PostgreSQL 12+, this rebuilds without blocking writes. pg_repack --only-indexes is an alternative.

Explanation

PostgreSQL's MVCC keeps old row versions after updates and deletes. Vacuum marks their space reusable for future inserts in the same table but does not shrink the file, except for empty pages at the end. If churn outpaces vacuum, or vacuum is blocked by old transactions, tables grow far beyond their live data. Rewriting the table is the only way to return space; VACUUM FULL does it with a lock that blocks reads and writes, while pg_repack does it online with triggers and a brief final lock.

Edge cases / notes

• Foreign keys and triggers. pg_repack handles them, but test on a copy first, especially with logical replication or custom triggers.
• WAL volume. Rewriting a large table generates a lot of WAL; watch replica lag and WAL disk usage.
• Django migrations. Do not run migrations on the table while pg_repack is running.

Next steps

• Prevent regrowth with Tune PostgreSQL Autovacuum for Django.
• Delete old rows safely with Delete Old Rows in Django in Batches.
• Avoid bloat on event tables with Partition Large PostgreSQL Tables for Django.

FAQ

Why does my table not shrink after deleting rows?

Vacuum only marks space reusable. Returning it to disk requires rewriting the table with pg_repack or VACUUM FULL.

Is pg_repack safe on production?

It is designed for online use, with a brief lock at the end. Test on a copy and run during low traffic.

What does pg_repack need?

The extension on the server, a matching client version, a primary key on the table, and free disk space.

Can I use pg_repack on RDS?

Yes. RDS and Aurora support the pg_repack extension; run the client from a host that can connect.