PostgreSQL Index Bloat: Detection and Remediation

Your PostgreSQL queries were fast last quarter. Now the same queries are crawling, even though the data volume hasn't changed dramatically. You've checked the query plans, added work_mem, and tuned autovacuum — but performance keeps degrading. The culprit hiding in plain sight is almost always index bloat: dead, wasted space inside your indexes that PostgreSQL has to scan through on every read, but nobody is cleaning up. Left unchecked, index bloat silently erodes database performance until it becomes a production incident.

Index bloat accumulates differently from table bloat and is often overlooked because standard monitoring tools focus on heap (table) bloat. B-tree indexes in PostgreSQL do not reclaim space from deleted or updated rows the same way a VACUUM handles the heap. Over time, pages inside an index fill with dead tuples, inflating the index size far beyond what's needed to represent live data. A 500 MB index holding 50 MB of actual live entries is not uncommon in write-heavy workloads.

This post walks through how to detect index bloat precisely using pgstattuple, calculate a reliable bloat ratio, understand the performance consequences, and remediate it safely in production using REINDEX CONCURRENTLY and the CREATE INDEX CONCURRENTLY + DROP approach.

What Is Index Bloat?

When a row is updated or deleted in PostgreSQL, the old row version (dead tuple) remains in the heap until VACUUM reclaims it. For indexes, however, the corresponding index entries pointing to those dead tuples become dead index entries — but the B-tree pages they occupy are not immediately reused. Over many write cycles, a B-tree index accumulates a large proportion of pages that hold no useful live data.

The key distinction from table bloat is that VACUUM marks dead heap tuples as reusable but does not physically compact B-tree index pages in the same pass. Index pages can only be fully reclaimed when an entire page becomes empty, and even then, PostgreSQL only returns truly empty pages to the free space map rather than shrinking the index file on disk. This means that in workloads with high update or delete rates — think order tables, event logs, or queues — B-tree indexes grow monotonically even if the live row count stays constant.

Bloat is wasted space PostgreSQL reads. Every sequential scan of an index has to traverse bloated pages to find live entries, increasing I/O and degrading query latency.

Measuring Index Bloat

The most accurate way to measure index bloat is the pgstattuple extension, which provides page-level visibility into index internals.

First, install the extension (requires superuser):

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then use the pgstatindex() function to inspect a specific index:

SELECT
  index_size,
  leaf_pages,
  empty_pages,
  deleted_pages,
  avg_leaf_density,
  ROUND(
    (CAST(deleted_pages AS numeric) / NULLIF(leaf_pages + deleted_pages + empty_pages, 0)) * 100,
    2
  ) AS bloat_ratio_pct
FROM pgstatindex('idx_orders_created_at');

Key fields to understand:

• leaf_pages: pages holding actual index entries pointing to heap rows.
• deleted_pages: pages entirely composed of dead entries, pending reclaim.
• empty_pages: pages already reclaimed but not yet returned to OS.
• avg_leaf_density: average fill ratio of live data on leaf pages (lower = more bloat within pages).

For a database-wide view, combine pgstatindex() with pg_stat_user_indexes to rank all user indexes by bloat:

SELECT
  schemaname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  psi.deleted_pages,
  psi.leaf_pages,
  ROUND(
    (CAST(psi.deleted_pages AS numeric) /
     NULLIF(psi.leaf_pages + psi.deleted_pages, 0)) * 100, 2
  ) AS bloat_ratio_pct
FROM pg_stat_user_indexes psui
CROSS JOIN LATERAL pgstatindex(psui.indexrelid) psi
ORDER BY bloat_ratio_pct DESC NULLS LAST
LIMIT 20;

You can also get a quick size comparison between pg_relation_size (actual on-disk size) and what the live data should theoretically require by using pgstattuple() on the indexed table and comparing dead tuple fractions:

SELECT
  table_len,
  dead_tuple_len,
  ROUND((dead_tuple_len::numeric / table_len) * 100, 2) AS table_dead_pct
FROM pgstattuple('orders');

High dead_tuple_len on the table combined with high deleted_pages on the index confirms a coordinated bloat problem originating from write churn.

Impact on Performance

Index bloat degrades performance through several mechanisms:

• Increased I/O per query: A bloated index has a larger physical footprint. Index scans that would fit in shared_buffers at a compact size now require multiple buffer evictions, increasing disk reads.
• Slower B-tree traversal: More pages means more levels or wider levels in the B-tree, increasing the number of page reads per lookup even for point queries.
• Planner cost miscalibration: The planner estimates index scan cost using page counts from pg_class.relpages. An inflated page count makes index scans appear more expensive than they actually would be after cleanup, potentially causing the planner to choose sequential scans unnecessarily.
• Cache pressure: Bloated indexes evict useful heap and index pages from shared_buffers, reducing cache hit rates across the database.

Autovacuum deserves specific attention here. While autovacuum does trigger index cleanup passes, it only removes dead index entries during a heap vacuum. It does not compact or rebuild the index structure. In databases with very high update or delete rates, autovacuum may not keep up, and even when it does, the freed pages within the index are not consolidated. This is why high-churn indexes require periodic rebuilds regardless of how well autovacuum is tuned.

Remediation

There are two safe approaches to remediating index bloat in production without locking out application traffic.

Option 1: REINDEX CONCURRENTLY (PostgreSQL 12+)

REINDEX CONCURRENTLY rebuilds the index while allowing concurrent reads and writes. It creates a new index alongside the old one, swaps them, and drops the old index — all without an exclusive lock.

REINDEX INDEX CONCURRENTLY idx_orders_created_at;

To rebuild all indexes on a table:

REINDEX TABLE CONCURRENTLY orders;

Option 2: CREATE INDEX CONCURRENTLY + DROP INDEX

On PostgreSQL versions before 12, or when you need more control over the rebuild process, the manual approach achieves the same result:

-- Step 1: Build the replacement index
CREATE INDEX CONCURRENTLY idx_orders_created_at_new
  ON orders (created_at);

-- Step 2: Verify the new index is valid
SELECT indexname, indisvalid
FROM pg_indexes
JOIN pg_index ON indexrelid = (
  SELECT oid FROM pg_class WHERE relname = 'idx_orders_created_at_new'
)
WHERE indexname = 'idx_orders_created_at_new';

-- Step 3: Swap in a single transaction (renames only, no rebuild)
BEGIN;
ALTER INDEX idx_orders_created_at RENAME TO idx_orders_created_at_old;
ALTER INDEX idx_orders_created_at_new RENAME TO idx_orders_created_at;
COMMIT;

-- Step 4: Drop the old bloated index
DROP INDEX CONCURRENTLY idx_orders_created_at_old;

Prevention

Remediation fixes the present problem; prevention stops it from recurring at the same pace.

Identify and Drop Unused Indexes

Unused indexes still accumulate bloat from writes without providing any query benefit. Use pg_stat_user_indexes to find indexes that have never been scanned since the last statistics reset:

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS scans_since_reset
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Tune Autovacuum for Write-Heavy Tables

Ensure autovacuum is triggering frequently enough on high-churn tables to prevent heap dead tuple accumulation, which drives index dead entry accumulation:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum at 1% dead tuples (default: 20%)
  autovacuum_analyze_scale_factor = 0.005, -- analyze at 0.5% changes
  autovacuum_vacuum_cost_delay = 2         -- reduce throttling (ms)
);

Automated Bloat Monitoring Cron

Set up a nightly cron job that writes bloat metrics to a monitoring table, enabling trend detection and alerting before bloat becomes critical:

-- Create a monitoring table
CREATE TABLE IF NOT EXISTS dba_index_bloat_log (
  logged_at        TIMESTAMPTZ DEFAULT NOW(),
  schemaname       TEXT,
  indexrelname     TEXT,
  index_size_bytes BIGINT,
  bloat_ratio_pct  NUMERIC
);

-- Insert nightly snapshot (run via pg_cron or external cron + psql)
INSERT INTO dba_index_bloat_log (schemaname, indexrelname, index_size_bytes, bloat_ratio_pct)
SELECT
  schemaname,
  indexrelname,
  pg_relation_size(indexrelid),
  ROUND(
    (CAST(psi.deleted_pages AS numeric) /
     NULLIF(psi.leaf_pages + psi.deleted_pages, 0)) * 100, 2
  )
FROM pg_stat_user_indexes psui
CROSS JOIN LATERAL pgstatindex(psui.indexrelid) psi
WHERE (psi.leaf_pages + psi.deleted_pages) > 0;

A shell cron entry to run this nightly at 2 AM:

0 2 * * * psql -U postgres -d mydb -c "\
INSERT INTO dba_index_bloat_log (schemaname, indexrelname, index_size_bytes, bloat_ratio_pct) \
SELECT schemaname, indexrelname, pg_relation_size(indexrelid), \
  ROUND((CAST(psi.deleted_pages AS numeric) / NULLIF(psi.leaf_pages + psi.deleted_pages, 0)) * 100, 2) \
FROM pg_stat_user_indexes psui \
CROSS JOIN LATERAL pgstatindex(psui.indexrelid) psi \
WHERE (psi.leaf_pages + psi.deleted_pages) > 0;" \
>> /var/log/pg_bloat_monitor.log 2>&1

Alert when any index exceeds your threshold (e.g., 40% bloat ratio or index size growth of more than 20% week-over-week). This turns reactive firefighting into proactive maintenance.

Manage PostgreSQL Index Bloat at Scale with JusDB

Tracking index bloat manually across dozens of databases and hundreds of indexes is unsustainable at scale. JusDB provides continuous index health monitoring, automated bloat detection with configurable thresholds, and guided remediation workflows — so your team spends time on application development rather than database firefighting. Connect your PostgreSQL instances to JusDB and get a complete picture of index health, query performance, and autovacuum effectiveness in a single dashboard.

Start monitoring your PostgreSQL indexes with JusDB — free to get started.