0Pricing
SQL Academy · Lesson

Index Maintenance and Bloat

Diagnose index bloat, rebuild with REINDEX CONCURRENTLY, and drop unused indexes safely.

Why Indexes Bloat

PostgreSQL uses MVCC: UPDATE writes a new row version, leaving the old visible to existing transactions. Index entries for both versions exist. Over time:

  • Heavy-update tables accumulate dead index entries
  • Indexes grow larger than necessary
  • Lookups slow as the B-tree gets deeper

Diagnosing Bloat

Bloat-checking queries are non-trivial. Common tools:

  • pgstattuple extension
  • pg_repack reports
  • Bloat queries from check_postgres / monitoring tools
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('orders_user_id_idx');

All lessons in this course

  1. B-tree vs Hash vs GiST vs GIN Indexes
  2. Composite Indexes and Column Order
  3. Partial and Expression Indexes
  4. Index Maintenance and Bloat
← Back to SQL Academy