---
# === IDENTITY ===
id: software/debugging/postgresql-index-bloat/2026
canonical_question: "How do I diagnose and fix PostgreSQL index bloat?"
aliases:
  - "PostgreSQL index bloat"
  - "REINDEX CONCURRENTLY PostgreSQL"
  - "pg_repack index bloat"
  - "PostgreSQL rebuild bloated index"
  - "pgstattuple index bloat"
  - "PostgreSQL index taking too much disk space"
  - "PostgreSQL fillfactor HOT updates"
  - "PostgreSQL btree bloat"
  - "PostgreSQL index maintenance"
  - "PostgreSQL invalid index _ccnew"
entity_type: software_reference
domain: software > debugging > postgresql_index_bloat
region: global
jurisdiction: global
temporal_scope: 2012-2026

# === VERIFICATION ===
last_verified: 2026-04-15
confidence: 0.92
version: 1.0
first_published: 2026-04-15

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 14 — fixed REINDEX CONCURRENTLY corruption bug that silently dropped index entries (2022-02)"
  next_review: 2026-10-12
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "REINDEX CONCURRENTLY requires PostgreSQL 12 or later — PG 11 and earlier must use build-drop-rename via CREATE INDEX CONCURRENTLY + DROP INDEX"
  - "REINDEX CONCURRENTLY cannot rebuild exclusion-constraint indexes, system catalog indexes, or run inside a transaction block for partitioned tables"
  - "pg_repack requires a primary key or unique not-null index on every table it processes — won't work on tables without one"
  - "pg_repack needs 2x disk space (table + all indexes duplicated) and the client version must match the extension version"
  - "VACUUM FULL takes ACCESS EXCLUSIVE lock for the entire operation — never run on production tables during business hours; it blocks reads and writes"
  - "REINDEX CONCURRENTLY holds the xmin horizon for the duration, which stalls autovacuum cleanup of dead tuples across ALL tables in the cluster until it finishes"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Slow query performance without disk-space concerns (index is small but queries are slow)"
    use_instead: "software/debugging/postgresql-slow-queries/2026"
  - condition: "Table bloat rather than index bloat (high n_dead_tup on the table, not on indexes)"
    use_instead: "VACUUM (autovacuum tuning) — table bloat is a different remediation path than index bloat"
  - condition: "Connection pool exhaustion or lock-wait timeouts (not disk space / not slow index scans)"
    use_instead: "software/debugging/postgresql-connection-pool/2026"
  - condition: "Corrupted index (checksum failures, access errors) — not bloat, needs different recovery"
    use_instead: "Rebuild from scratch with REINDEX (non-concurrent) after isolating the table; investigate storage integrity first"

# === AGENT HINTS ===
inputs_needed:
  - key: postgresql_version
    question: "Which PostgreSQL version are you running?"
    type: choice
    options: ["PostgreSQL 11 or older", "PostgreSQL 12-13", "PostgreSQL 14-16", "PostgreSQL 17+", "PostgreSQL 18+"]
  - key: deployment_type
    question: "Self-managed or managed (RDS/Aurora/Cloud SQL)?"
    type: choice
    options: ["Self-managed (full superuser)", "AWS RDS/Aurora", "GCP Cloud SQL", "Azure Database for PostgreSQL", "Supabase/Neon/other"]
  - key: downtime_tolerance
    question: "Can the affected table tolerate an ACCESS EXCLUSIVE lock (blocked writes)?"
    type: choice
    options: ["Yes — maintenance window available", "No — 24/7 production, zero write downtime", "Brief lock ok (< 5 seconds)"]
  - key: workload_pattern
    question: "Is the affected table read-heavy, write-heavy, or append-only?"
    type: choice
    options: ["Read-heavy (rare updates)", "Write-heavy (frequent UPDATE/DELETE)", "Append-only (INSERT only)", "Mixed"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/debugging/postgresql-index-bloat/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-04-15)"

# === RELATED UNITS ===
related_kos:
  depends_on:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "PostgreSQL Slow Queries (prerequisite: know how to read EXPLAIN output)"
  related_to:
    - id: "software/debugging/postgresql-connection-pool/2026"
      label: "PostgreSQL Connection Pool Exhaustion"
    - id: "software/debugging/mysql-deadlocks/2026"
      label: "MySQL Deadlocks"
  solves:
    - id: "software/debugging/database-timeout-errors/2026"
      label: "Disk-full alerts on PostgreSQL storage"
  alternative_to:
    - id: "software/debugging/postgresql-partitioning/2026"
      label: "Table Partitioning (prevention via partition drop instead of DELETE + REINDEX)"
  often_confused_with:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "Slow Queries (similar symptom but different root cause — stale stats or missing index, not bloat)"

# === SOURCES ===
sources:
  - id: src1
    title: "PostgreSQL Documentation — REINDEX"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/sql-reindex.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src2
    title: "PostgreSQL Documentation — pgstattuple"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/pgstattuple.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src3
    title: "PostgreSQL Documentation — Routine Reindexing"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/routine-reindex.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src4
    title: "PostgresAI — How to deal with bloat"
    author: Postgres.ai
    url: https://postgres.ai/docs/postgres-howtos/database-administration/maintenance/how-to-deal-with-bloat
    type: technical_blog
    published: 2025-03-01
    reliability: high
  - id: src5
    title: "AWS Prescriptive Guidance — Removing bloat with pg_repack"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/pg-repack.html
    type: official_docs
    published: 2025-06-01
    reliability: high
  - id: src6
    title: "AWS Prescriptive Guidance — Rebuilding indexes"
    author: Amazon Web Services
    url: https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-maintenance-rds-aurora/reindex.html
    type: official_docs
    published: 2025-06-01
    reliability: high
  - id: src7
    title: "Crunchy Data — Postgres Performance Boost: HOT Updates and Fill Factor"
    author: Crunchy Data
    url: https://www.crunchydata.com/blog/postgres-performance-boost-hot-updates-and-fill-factor
    type: technical_blog
    published: 2024-07-01
    reliability: high
  - id: src8
    title: "Index Bloat in Postgres: Why It Matters, How to Identify, How to Resolve"
    author: Kendra Little
    url: https://kendralittle.com/2025/12/01/index-bloat-postgres-why-it-matters-how-to-identify-and-resolve/
    type: technical_blog
    published: 2025-12-01
    reliability: high
---

# How Do I Diagnose and Fix PostgreSQL Index Bloat?

## TL;DR

- **Bottom line**: PostgreSQL index bloat is diagnosed with `pgstattuple` or bloat-estimator SQL and fixed with `REINDEX INDEX CONCURRENTLY` (PG 12+) for zero-downtime rebuilds, or `pg_repack` when you need to reorganize both table and indexes. Prevent recurrence with fillfactor tuning, HOT updates, and aggressive autovacuum settings.
- **Key tool/command**: `REINDEX INDEX CONCURRENTLY idx_name;` — rebuilds the index without blocking writes. Fall back to `pg_repack --index idx_name` for very large indexes or when `xmin` horizon concerns block reindex.
- **Watch out for**: A failed `REINDEX CONCURRENTLY` leaves behind `INVALID` indexes with suffixes `_ccnew` or `_ccold` that still consume write overhead but are never used for reads. You must `DROP INDEX CONCURRENTLY` them before retrying.
- **Works with**: PostgreSQL 12+. `REINDEX CONCURRENTLY` added in PG 12; corruption bug fixed in PG 14.0 (do not use on PG 12-13 for unique indexes in production). `pg_repack` works on PG 9.4+.

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- **REINDEX CONCURRENTLY requires PostgreSQL 12 or later**. On PG 11 or older, use the manual pattern: `CREATE INDEX CONCURRENTLY new_idx ...; DROP INDEX CONCURRENTLY old_idx; ALTER INDEX new_idx RENAME TO old_idx;`.
- **REINDEX CONCURRENTLY cannot rebuild exclusion-constraint indexes or system catalog indexes**, and cannot run inside a transaction block for partitioned indexes/tables. [src1]
- **pg_repack requires a primary key or unique not-null index** on every table it processes. Tables without one must use `REINDEX` or be restructured first. [src5]
- **pg_repack needs 2x disk space** — the table and all indexes are fully duplicated during the operation. Check `pg_database_size()` before starting on a full disk. [src5]
- **Never run `VACUUM FULL` on production during business hours** — it takes `ACCESS EXCLUSIVE` for the entire operation, blocking both reads and writes. Use `pg_repack` or `REINDEX CONCURRENTLY` instead.
- **`REINDEX CONCURRENTLY` holds the xmin horizon** for its duration — autovacuum cannot clean dead tuples in ANY table cluster-wide until it finishes. Batch one index at a time on high-churn systems. [src4]

## Quick Reference

| # | Symptom / Goal | Command / Solution | Notes |
|---|---|---|---|
| 1 | Check index sizes vs table sizes | `SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC;` | Index larger than table = likely bloat [src3] |
| 2 | Measure bloat precisely (slow but exact) | `CREATE EXTENSION pgstattuple; SELECT * FROM pgstatindex('idx_name');` | Full index scan; avoid on huge indexes [src2] |
| 3 | Measure bloat approximately (fast) | `SELECT * FROM pgstattuple_approx('idx_name');` | Sample-based; use for large indexes [src2] |
| 4 | Rebuild one index online (PG 12+) | `REINDEX INDEX CONCURRENTLY idx_name;` | No write lock; ~2x slower than REINDEX [src1] |
| 5 | Rebuild all indexes on a table online | `REINDEX TABLE CONCURRENTLY my_table;` | Sequential per index; takes hours on large tables [src1] |
| 6 | Rebuild with pg_repack (alternative) | `pg_repack -d mydb --index idx_name` | Works on older PG; no xmin horizon impact [src5] |
| 7 | Find invalid indexes from failed rebuild | `SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;` | Look for `_ccnew` / `_ccold` suffixes [src1] |
| 8 | Drop invalid index | `DROP INDEX CONCURRENTLY idx_name_ccnew;` | Must do before retrying REINDEX [src1] |
| 9 | Unused indexes (candidates to drop) | `SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;` | Stats reset on restart — check uptime [src3] |
| 10 | Set fillfactor for write-heavy table | `ALTER TABLE t SET (FILLFACTOR = 80);` | Enables HOT updates; reduces index bloat 60%+ [src7] |
| 11 | Check HOT update ratio | `SELECT relname, n_tup_hot_upd::float / NULLIF(n_tup_upd,0) AS hot_pct FROM pg_stat_user_tables;` | Goal: > 0.8 on frequently-updated tables [src7] |
| 12 | Aggressive autovacuum on hot table | `ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01);` | Vacuums at 1% dead instead of 20% default [src4] |
| 13 | Rebuild with low-lock pg_repack (table + indexes) | `pg_repack -d mydb --table my_table -k` | Requires PK; 2x disk space [src5] |
| 14 | Pre-check disk space before pg_repack | `SELECT pg_size_pretty(pg_total_relation_size('t'));` | Need 2x this free [src5] |
| 15 | Rebuild with BRIN alternative for time-series | `CREATE INDEX CONCURRENTLY idx_brin ON t USING BRIN (created_at);` | BRIN barely bloats; 1000x smaller than btree [src7] |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (32 lines)

```
START — Suspected index bloat
|-- Step 1: CONFIRM it is index bloat
|   |-- Index size > table size (pg_relation_size) -> likely bloat [src3]
|   |-- pgstattuple: leaf_fragmentation > 20% or avg_leaf_density < 50% -> confirmed [src2]
|   |-- Disk growing but row count flat -> bloat suspected
|   `-- If no -> use postgresql-slow-queries/2026 or VACUUM for table bloat
|
|-- Step 2: CHOOSE remediation
|   |-- Running PostgreSQL 12+?
|   |   |-- Single bloated index < 50GB -> REINDEX INDEX CONCURRENTLY [src1]
|   |   |-- Multiple indexes on one table -> REINDEX TABLE CONCURRENTLY [src1]
|   |   |-- Table + all indexes bloated; table has PK -> pg_repack [src5]
|   |   `-- Cannot tolerate xmin horizon hold (heavy DELETE workload) -> pg_repack [src4, src5]
|   |-- Running PostgreSQL 11 or older?
|   |   `-- CREATE INDEX CONCURRENTLY new; DROP INDEX CONCURRENTLY old; RENAME
|   `-- Maintenance window + small index?
|       `-- REINDEX INDEX idx_name (blocks writes but ~2x faster)
|
|-- Step 3: VERIFY fix
|   |-- Re-run pgstatindex -> leaf_fragmentation should be < 10%
|   |-- Check pg_relation_size -> should drop significantly
|   `-- Check for invalid indexes -> indisvalid must be true for all
|
`-- Step 4: PREVENT recurrence
    |-- Write-heavy table? -> ALTER TABLE SET (FILLFACTOR = 80) + re-REINDEX [src7]
    |-- HOT ratio < 80%? -> Reduce indexes on updated columns [src7]
    |-- Append-only table with range queries? -> Replace btree with BRIN [src7]
    |-- Time-series data? -> Partition by date + drop old partitions [src4]
    `-- Tune autovacuum_vacuum_scale_factor = 0.01 on high-churn tables [src4]
```

## Step-by-Step Guide

### 1. Identify bloated indexes

Start with size comparison — indexes larger than their tables are a strong signal. [src3, src8]

```sql
-- Indexes ordered by size, with table-relative ratio
SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  round(pg_relation_size(indexrelid)::numeric
        / NULLIF(pg_relation_size(relid), 0), 2) AS idx_to_table_ratio,
  idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
-- Indexes with idx_to_table_ratio > 0.5 warrant a closer look.
```

**Verify**: Run the query above. Any index with ratio > 0.5 AND > 1 GB is a strong bloat candidate.

### 2. Measure bloat precisely with pgstattuple

For confirmed bloat analysis, `pgstattuple` is the authoritative source. [src2]

```sql
-- One-time: install the extension (superuser)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Exact measurement (slow on large indexes — full scan)
SELECT * FROM pgstatindex('public.orders_customer_id_idx');
-- Key columns:
--   avg_leaf_density  : lower = more bloat (< 50% is bad, < 30% is critical)
--   leaf_fragmentation: higher = more bloat (> 20% warrants reindex)

-- Approximate measurement (fast, sample-based — PG 9.5+)
SELECT * FROM pgstattuple_approx('public.orders_customer_id_idx');
-- Use on multi-GB indexes to avoid table scans in production.
```

**Verify**: `avg_leaf_density < 50` or `leaf_fragmentation > 20` confirms bloat requiring remediation.

### 3. Drop any invalid indexes from prior failed rebuilds

Before rebuilding, clear debris from prior concurrent attempts. [src1]

```sql
-- Find any invalid indexes (failed REINDEX CONCURRENTLY artifacts)
SELECT
  c.relname AS index_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- Drop them (use CONCURRENTLY to avoid locks)
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx_ccnew;
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx_ccold;
```

**Verify**: Re-run the invalid-index query — should return zero rows.

### 4. Rebuild with REINDEX CONCURRENTLY (primary fix)

This is the zero-downtime remediation for PG 12+. [src1, src6]

```sql
-- Single bloated index (no write lock; reads and writes continue)
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- All indexes on a table (processed sequentially)
REINDEX TABLE CONCURRENTLY public.orders;

-- Parallel workers (PG 14+): control via max_parallel_maintenance_workers
SET max_parallel_maintenance_workers = 4;
REINDEX INDEX CONCURRENTLY public.large_idx;
```

**Verify**: `SELECT pg_size_pretty(pg_relation_size('public.orders_customer_id_idx'));` — size should drop significantly (often 50-90% reduction on heavily bloated indexes).

### 5. Use pg_repack when REINDEX is unsuitable

When the index is very large, the table is also bloated, or you cannot hold the xmin horizon. [src5]

```bash
# Install extension once (requires superuser)
psql -d mydb -c "CREATE EXTENSION pg_repack;"

# Rebuild all indexes on a table (table stays writable)
pg_repack -h db.example.com -d mydb --table public.orders -k

# Rebuild only one index
pg_repack -h db.example.com -d mydb --index public.orders_customer_id_idx

# Dry run to verify before touching production
pg_repack -h db.example.com -d mydb --table public.orders --dry-run
```

**Verify**: After completion, `pg_repack` reports "ok" and removes all intermediate objects. Check `pg_stat_activity` for stale sessions.

### 6. Tune fillfactor to prevent recurrence

For write-heavy tables, lower fillfactor enables HOT updates, which avoid updating indexes entirely. [src7]

```sql
-- Set fillfactor to 80% — leaves room for HOT updates on same page
ALTER TABLE public.orders SET (FILLFACTOR = 80);

-- Rebuild table + indexes to apply fillfactor to existing pages
VACUUM FULL public.orders;  -- OR: pg_repack -t public.orders

-- Verify HOT update ratio is climbing
SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- Goal: hot_pct > 80 on heavily-updated tables.
```

**Verify**: `hot_pct` improves within hours of normal workload. Index bloat accumulation rate drops proportionally.

### 7. Tune autovacuum for high-churn tables

Default autovacuum triggers at 20% dead tuples — too late for high-churn tables. [src4]

```sql
-- Per-table autovacuum settings (more aggressive)
ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.01,        -- vacuum at 1% dead vs 20%
  autovacuum_vacuum_threshold    = 1000,        -- minimum dead tuples
  autovacuum_analyze_scale_factor = 0.005,      -- analyze at 0.5%
  autovacuum_vacuum_cost_limit   = 2000         -- allow more work per cycle
);

-- Kill idle-in-transaction sessions that block autovacuum
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > INTERVAL '10 minutes';
```

**Verify**: `pg_stat_user_tables.last_autovacuum` should update frequently; `n_dead_tup` should stay low.

## Code Examples
<!-- Inline excerpts ≤15 lines. Full scripts extracted to scripts/ -->

### SQL: comprehensive bloat-detection report

```sql
-- Input:  pgstattuple extension installed
-- Output: Ranked list of bloated indexes with precise metrics
-- Full script: scripts/bloat-report.sql

CREATE EXTENSION IF NOT EXISTS pgstattuple;

WITH idx AS (
  SELECT schemaname, indexrelname, indexrelid,
         pg_relation_size(indexrelid) AS idx_bytes
  FROM pg_stat_user_indexes
  WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024  -- >100MB only
)
SELECT schemaname, indexrelname,
       pg_size_pretty(idx_bytes) AS size,
       (pgstatindex(indexrelid::regclass::text)).avg_leaf_density,
       (pgstatindex(indexrelid::regclass::text)).leaf_fragmentation
FROM idx
ORDER BY idx_bytes DESC;
```

### Python: automated reindex worker with safety gates

```python
# Input:  connection string + bloat threshold (default: leaf_density < 50)
# Output: reindexes each qualifying index; reports status
# Full script: scripts/reindex-worker.py

import psycopg2, time
conn = psycopg2.connect(CONN_STR)
conn.autocommit = True  # REINDEX CONCURRENTLY cannot be in a transaction
with conn.cursor() as cur:
    cur.execute("""
      SELECT schemaname||'.'||indexrelname AS idx
      FROM pg_stat_user_indexes i
      WHERE pg_relation_size(indexrelid) > 100*1024*1024
        AND (pgstatindex(indexrelid::regclass::text)).avg_leaf_density < 50
    """)
    for (idx,) in cur.fetchall():
        cur.execute(f"REINDEX INDEX CONCURRENTLY {idx}")
        print(f"reindexed {idx}")
        time.sleep(5)  # brief pause to let autovacuum catch up
```

### Bash: orchestrated pg_repack run with disk-space guard

```bash
#!/bin/bash
# Input:  DB name + table name
# Output: pg_repack runs only if 2x free disk space available
# Full script: scripts/pg-repack-guard.sh

DB="$1"; TABLE="$2"
NEEDED=$(psql -tA -d "$DB" -c "SELECT pg_total_relation_size('$TABLE') * 2")
FREE=$(df -B1 --output=avail /var/lib/postgresql | tail -1)

if [ "$FREE" -lt "$NEEDED" ]; then
  echo "ERROR: need $NEEDED bytes, have $FREE" >&2
  exit 1
fi

pg_repack -d "$DB" --table "$TABLE" -k --jobs=4
```

## Anti-Patterns

### Wrong: VACUUM FULL on production to fix index bloat

```sql
-- BAD - takes ACCESS EXCLUSIVE lock on the entire table [src4]
-- Blocks ALL reads and writes for minutes to hours
VACUUM FULL public.orders;
-- Queries pile up; connection pool fills; cascading outage
```

### Correct: REINDEX CONCURRENTLY + pg_repack (zero downtime)

```sql
-- GOOD - no write lock; production continues normally [src1, src5]
REINDEX TABLE CONCURRENTLY public.orders;
-- Or for table + index bloat together:
-- pg_repack -d mydb --table public.orders -k
```

### Wrong: REINDEX without CONCURRENTLY on a live table

```sql
-- BAD - takes ACCESS EXCLUSIVE on the index [src1]
-- All queries using this index are blocked for the duration
REINDEX INDEX public.orders_customer_id_idx;
```

### Correct: always use CONCURRENTLY on live systems

```sql
-- GOOD - reads + writes continue; only ~2x slower [src1]
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;
-- Monitor with: SELECT * FROM pg_stat_progress_create_index;
```

### Wrong: ignoring invalid indexes from failed rebuilds

```sql
-- BAD - orphan _ccnew / _ccold indexes persist [src1]
-- They consume write overhead but are never used for reads
-- Cluster slowly accumulates dead weight every failed reindex
-- (no explicit code — the anti-pattern is NOT running the cleanup query)
```

### Correct: detect and drop invalid indexes as a routine check

```sql
-- GOOD - clean up failed rebuild artifacts [src1]
SELECT 'DROP INDEX CONCURRENTLY ' || indexrelid::regclass::text || ';' AS cleanup_sql
FROM pg_index WHERE NOT indisvalid;
-- Review, then execute the generated statements.
```

### Wrong: DROP INDEX + CREATE INDEX (non-concurrent) as a "fast rebuild"

```sql
-- BAD - between DROP and CREATE, queries do seq scans [src3]
-- On a large table, this can bring an application to its knees
DROP INDEX public.orders_customer_id_idx;
CREATE INDEX public.orders_customer_id_idx ON public.orders (customer_id);
```

### Correct: REINDEX CONCURRENTLY keeps the index available throughout

```sql
-- GOOD - old index serves queries while new one builds [src1]
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;
-- Atomic swap at the end; no seq-scan gap.
```

## Common Pitfalls

- **Ignoring the xmin horizon impact**: `REINDEX CONCURRENTLY` holds the xmin horizon cluster-wide for its entire runtime. On a 4-hour reindex of a 500GB index, autovacuum cannot clean dead tuples on ANY table for 4 hours, causing cascading bloat. Fix: `pg_repack` for very large indexes; it does not hold xmin. [src4]
- **Running pg_repack on a table without a PK**: `pg_repack` silently fails or errors with "relation does not have a primary key". Fix: add a primary key, use a unique not-null index, or fall back to `REINDEX CONCURRENTLY`. [src5]
- **Disk space exhaustion mid-rebuild**: Both `REINDEX CONCURRENTLY` and `pg_repack` need the old and new index simultaneously (effectively 2x space). Fix: pre-check with `SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) FROM pg_stat_user_indexes WHERE relid = 'mytable'::regclass;` and compare to `df`. [src5]
- **Fillfactor change doesn't apply retroactively**: `ALTER TABLE SET (FILLFACTOR = 80)` only affects new pages. Existing pages remain at 100% full until `VACUUM FULL` or `pg_repack` rewrites them. Fix: change fillfactor THEN rebuild. [src7]
- **Autovacuum blocked by long-running transactions**: An `idle in transaction` session from 3 hours ago blocks autovacuum on every table in the cluster. Bloat accumulates silently. Fix: `SET idle_in_transaction_session_timeout = '10min'` globally. [src4]
- **Missing `CONCURRENTLY` on DROP**: Dropping an invalid index with plain `DROP INDEX` takes `ACCESS EXCLUSIVE` briefly. Use `DROP INDEX CONCURRENTLY` in production. [src1]
- **HOT updates defeated by too many indexes**: HOT requires the UPDATE to not touch ANY indexed column. A table with 10 indexes on frequently-updated columns will have HOT ratio near 0%. Fix: drop low-value indexes (check `idx_scan = 0` in `pg_stat_user_indexes`). [src7]
- **Using btree where BRIN would suffice**: On append-only time-series tables, btree indexes on timestamp columns bloat 1000x more than BRIN. Fix: `CREATE INDEX USING BRIN` for sequential-access columns. [src7]

## Diagnostic Commands

> Full script: [diagnostic-commands.sql](scripts/diagnostic-commands.sql) (38 lines)

```sql
-- === Identify bloat candidates ===
-- Indexes larger than their tables (strong bloat signal)
SELECT schemaname, relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_sz,
       pg_size_pretty(pg_relation_size(relid))      AS tbl_sz
# ... (see full script)
```

## Version History & Compatibility

| Feature | Available Since | Notes |
|---|---|---|
| `REINDEX` (non-concurrent) | PostgreSQL 7.x | Takes ACCESS EXCLUSIVE on index [src1] |
| `pgstattuple` extension | PostgreSQL 8.2 | Exact but slow bloat measurement [src2] |
| `pg_repack` | PostgreSQL 9.4+ | External extension; no xmin horizon impact [src5] |
| `pgstattuple_approx` | PostgreSQL 9.5 | Fast sample-based estimate [src2] |
| `REINDEX CONCURRENTLY` | PostgreSQL 12 | Zero-downtime rebuild [src1] |
| `pg_stat_progress_create_index` | PostgreSQL 12 | Monitor reindex progress [src1] |
| Parallel index builds | PostgreSQL 11 | `max_parallel_maintenance_workers` [src1] |
| REINDEX CONCURRENTLY unique-index bug fix | PostgreSQL 14.0 | Critical — avoid on PG 12-13 for unique indexes [src1] |
| BRIN + HOT-update compatibility | PostgreSQL 16 | BRIN indexes no longer block HOT [src7] |
| `REINDEX ... TABLESPACE` | PostgreSQL 14 | Move index during rebuild [src1] |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Single index bloated, PG 12+, live system | PG 11 or older | `CREATE INDEX CONCURRENTLY` + `DROP INDEX CONCURRENTLY` + rename |
| Table + all indexes bloated, table has PK | Table has no PK or unique index | `REINDEX TABLE CONCURRENTLY` |
| Very large index, cannot hold xmin horizon | Short-lived small index | `REINDEX INDEX CONCURRENTLY` (simpler) |
| Slow queries + small index size | Index size is normal | See `postgresql-slow-queries/2026` (not bloat) |
| Table bloat (not just index bloat) | Only indexes are bloated | `pg_repack --table` or `VACUUM` tuning |
| Append-only time-series data | High-cardinality equality lookups | BRIN index instead of btree |

## Important Caveats

- **REINDEX CONCURRENTLY in PG 12-13 had a unique-index corruption bug** (fixed in 14.0). If you are stuck on 12 or 13, apply the latest minor-version patches before using it on unique indexes, or fall back to `pg_repack`. [src1]
- **`pg_repack` client and extension versions must match exactly**. Version drift causes silent failures. Pin both in deployment automation. [src5]
- **PostgreSQL managed services have limits**: AWS RDS, GCP Cloud SQL, and Azure may not allow `pg_repack` extension installation. `REINDEX CONCURRENTLY` is available everywhere PG 12+ is. [src5, src6]
- **Exclusion-constraint indexes skip concurrent reindex silently**. Check with `\d+ tablename` for `EXCLUDE USING ...` constraints before relying on `REINDEX TABLE CONCURRENTLY`. [src1]
- **Bloat below 20% is usually not worth fixing** — the cost of rebuild often exceeds the recovered disk space. AWS suggests 20% as a practical threshold; some teams use 30-40%. [src6, src8]
- **Partitioned tables**: `REINDEX TABLE CONCURRENTLY parent` processes each partition sequentially, and cannot run inside a transaction. For massive partitioned tables, script per-partition rebuilds with pauses. [src1]

## Related Units

- [PostgreSQL Slow Queries](/software/debugging/postgresql-slow-queries/2026)
- [PostgreSQL Connection Pool Exhaustion](/software/debugging/postgresql-connection-pool/2026)
- [MySQL Deadlocks](/software/debugging/mysql-deadlocks/2026)