Detect and Shrink a Bloated Postgres Table

Estimate dead tuple bloat with pgstattuple, reclaim space without exclusive locks using pg_repack, and verify the size actually dropped.

~50 mindatabasepostgresoperations

Task

Measure table bloat precisely, reclaim the wasted disk online, and prove the table size and query performance recovered.

When to use this

A table keeps growing on disk although row counts are flat. Autovacuum runs constantly, queries read dead pages, and the database is running out of disk.

Prerequisites

PostgreSQL with the pgstattuple extension available
pg_repack installed on the database server with superuser access
Free disk at least equal to the table size for the rebuild copy

Steps

1. Estimate bloat with the extension.

CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple('orders');

Expected output: The report shows dead_tuple_percent, for example 62 percent on a table whose row count has been flat for months.

2. Check what autovacuum has been doing.

SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'orders';

Expected output: A large n_dead_tup with a stale or absent last_autovacuum explains why space was never reclaimed automatically.

3. Repack the table online instead of VACUUM FULL.

pg_repack -d shop -t orders -j 2

Expected output: pg_repack rebuilds the table alongside the live one and swaps them, holding only brief locks, while reads and writes continue.

4. Verify the size and tuple statistics after repack.

SELECT pg_size_pretty(pg_total_relation_size('orders')); SELECT * FROM pgstattuple('orders');

Expected output: The table shrinks from, say, 480 GB to 150 GB, and dead_tuple_percent drops below one percent.

5. Tune autovacuum so bloat does not rebuild.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay = 2
);

Expected output: With a two percent scale factor the table is vacuumed far more often, keeping dead tuples from accumulating into bloat again.

Troubleshooting

Problem

pg_repack fails to acquire the initial lock

Solution

Long-running transactions block the brief lock it needs at start; terminate or let them finish, and schedule the repack outside peak hours.

Problem

Not enough disk for the rebuild copy

Solution

Repack needs room for a full copy; free WAL and old backups first, or repack the hottest partitions one at a time instead of the whole table.

Problem

Bloat returns within weeks

Solution

The workload churns rows faster than default autovacuum settings cope with; lower the scale factor and cost delay per table as shown, and verify with pg_stat_user_tables.

Limitations

pgstattuple exact mode scans the whole table and is expensive; use approximate mode for large tables during business hours.
Repack needs transient double storage and superuser, which some managed platforms restrict or schedule for you.

Flat rows, growing disk

Heavy UPDATE and DELETE workloads leave dead tuples that autovacuum marks free but never returns to the operating system. The table balloons, every sequential scan reads gigabytes of corpses, and the disk forecast turns red. pgstattuple quantifies the waste precisely, pg_repack reclaims it online with only brief locks, and a per-table autovacuum tuning keeps the corpse population low enough that it never accumulates again.

# Quantify
SELECT * FROM pgstattuple('orders');

# Reclaim online (reads and writes continue)
pg_repack -d shop -t orders -j 2

# Verify
SELECT pg_size_pretty(pg_total_relation_size('orders'));
#   pg_total_relation_size
#   ------------------------
#   4121 MB   (was 18.9 GB)

VACUUM FULL is the wrong tool at scale

Plain VACUUM frees space for reuse inside the table but never shrinks the file; VACUUM FULL rewrites it under an exclusive lock that blocks everything for the duration. pg_repack gets the shrink of VACUUM FULL with the availability of normal operation: it builds a compact copy, catches up on concurrent writes, and swaps in a brief window.

Fix the churn, not just the corpse

Repack is a reset, not a cure. If the workload rewrites every row every day, bloat returns unless autovacuum keeps pace. Lower the per-table scale factor so vacuum triggers on small dead-tuple counts, and cut the cost delay so each vacuum actually finishes before the next burst of churn.