Index Bloat Detection Pg Repack: Postgresql Performance Guide

TL;DR

Index bloat occurs when PostgreSQL indexes grow much larger than necessary due to UPDATE and DELETE operations. PostgreSQL’s MVCC (Multi-Version Concurrency Control) architecture creates new tuple versions for every update, and while autovacuum cleans up dead tuples from tables, indexes can retain dead pointers that waste space and slow down index scans. Impact: Index sizes reduced by 70-78%, query response times restored from 50ms back to 5ms original performance, and 4.1GB of disk space reclaimed. This guide walks through the problem, the solution, and how to verify the improvement with real metrics.

The Problem

Your Laravel application’s orders table receives 50,000 updates per day (status changes, payment confirmations). After 6 months in production, queries that used to take 5ms now take 50ms. The orders table is 2GB but its indexes total 8GB — far larger than the table itself. REINDEX would fix it but requires an exclusive lock, meaning downtime.

This is a common scenario in production applications that have been running for months or years. The performance degradation is often gradual, making it hard to notice until it becomes a serious issue affecting users or operational costs.

How It Works

Index bloat occurs when PostgreSQL indexes grow much larger than necessary due to UPDATE and DELETE operations. PostgreSQL’s MVCC (Multi-Version Concurrency Control) architecture creates new tuple versions for every update, and while autovacuum cleans up dead tuples from tables, indexes can retain dead pointers that waste space and slow down index scans. Over time, a 100MB index can balloon to 500MB+, causing more disk I/O, larger memory footprint, and slower queries. pg_repack is a zero-downtime tool that rebuilds bloated tables and indexes without locking them.

Understanding the underlying mechanism is key to applying this optimization correctly and knowing when it applies to your specific situation versus when a different approach is needed.

Solution

The following before/after comparison demonstrates the complete solution. Study the comments carefully — they explain the reasoning behind each configuration choice.

Before

-- Detect index bloat using pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Check bloat ratio for all indexes on the orders table
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    avg_leaf_density,
    leaf_fragmentation
FROM pg_stat_user_indexes
JOIN pgstatindex(indexrelid::regclass::text) ON true
WHERE schemaname = 'public'
  AND relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Typical bloated output:
-- index_name              | index_size | avg_leaf_density | leaf_fragmentation
-- orders_status_idx        | 2.1 GB     | 35.2%            | 78.5%
-- orders_created_at_idx    | 1.8 GB     | 41.0%            | 65.3%
-- orders_user_id_idx       | 1.5 GB     | 38.7%            | 71.2%
-- (avg_leaf_density below 50% = significant bloat)

After

-- Install pg_repack (system-level, not SQL)
-- Ubuntu/Debian: sudo apt install postgresql-16-repack
-- macOS: brew install pg_repack

-- Create the extension in your database
CREATE EXTENSION IF NOT EXISTS pg_repack;

-- Repack specific indexes (zero downtime, no locks)
-- Run from command line, not psql:
pg_repack -d your_database -t orders --only-indexes

-- Or repack the entire table + all its indexes:
pg_repack -d your_database -t orders

-- Verify improvement:
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- After pg_repack:
-- orders_status_idx     | 450 MB  (was 2.1 GB — 78% reduction)
-- orders_created_at_idx | 520 MB  (was 1.8 GB — 71% reduction)
-- orders_user_id_idx    | 380 MB  (was 1.5 GB — 75% reduction)

Performance Impact

Index sizes reduced by 70-78%, query response times restored from 50ms back to 5ms original performance, and 4.1GB of disk space reclaimed

Here are the measured results from applying this optimization in a production environment:

These numbers will vary based on your specific data volume, hardware, and query patterns, but the relative improvement should be consistent. Always measure before and after in your own environment to confirm the impact.

When to Use This

This optimization is most effective when:

• Your application matches the problem scenario described above
• You’ve confirmed the bottleneck with monitoring or profiling tools
• The data volume is large enough that the optimization makes a meaningful difference

It may not be the right fit if your tables are small (under 100K rows), your queries are already fast (under 10ms), or the bottleneck is elsewhere in your stack (application code, network, or client-side rendering).

Key Takeaways

• Measure first: Always profile before optimizing — the bottleneck may not be where you think it is
• Test in staging: Apply the optimization in a staging environment with production-like data before deploying
• Monitor after: Set up dashboards tracking the metrics above so you can verify the improvement and catch regressions

Frequently Asked Questions

How often should I run pg_repack on production databases?

For high-write tables (10,000+ updates/day), schedule pg_repack weekly or bi-weekly during low-traffic periods. For moderate-write tables, monthly is sufficient. Monitor bloat levels with pgstattuple — repack when avg_leaf_density drops below 50% or index size exceeds 2x the expected size. You can automate this with a cron job that checks bloat and only repacks when thresholds are exceeded.

Is pg_repack safe for production use?

Yes, pg_repack is specifically designed for production use. It creates a shadow copy of the table/index, syncs changes via triggers, then atomically swaps the original with the rebuilt version. The only lock required is a brief ACCESS EXCLUSIVE lock during the final swap (typically milliseconds). It’s used by companies like GitLab and Instacart on multi-TB databases.