PostgreSQL Index Bloat: Measure It, Then REINDEX CONCURRENTLY

What index bloat is and why B-tree indexes accumulate it

Index bloat is space inside an index file that holds no live entries: dead tuples not yet removed, free space on pages that VACUUM has cleaned, and pages left sparsely filled by splits. Bloated indexes take more disk and more shared buffers, and scans read more pages to return the same rows.

Where it comes from

PostgreSQL uses MVCC. An UPDATE writes a new row version, and a DELETE only marks the old one dead. When an update creates a new heap tuple, every index on the table gets a new entry pointing to it, and the old entries stay until VACUUM removes them. VACUUM marks that space as reusable within the index, but it does not shrink the file or return space to the operating system. Free space on a leaf page can only be reused by keys that sort into that page's range.

That last constraint explains the problem workloads:

• Updates to indexed columns. Each one adds index entries and leaves dead ones behind.
• Monotonically increasing keys such as sequences or timestamps, combined with deletion of old rows. New keys always land on the rightmost pages. Older pages that have been mostly emptied keep their few surviving entries and never get refilled.
• Bulk deletes. They leave many pages sparse at once.

Page splits add to this. A split divides a full page into two partly filled pages, and churn can leave both sparse.

HOT updates

If an update changes no indexed column and the new row version fits on the same heap page, PostgreSQL performs a heap-only tuple (HOT) update and writes no new index entries.

Some bloat is normal and harmless. It matters mostly for high-churn tables with large indexes.

Copy                  UPDATE a row
                        ▼
     ┌──────────────────────────────────────┐
     │ Indexed column changed, or new row   │
     │ version won't fit on the heap page?  │
     └────────┬───────────────────────────┬─┘
          yes │                           │ no
              ▼                           ▼
 ┌────────────────────────┐   ┌────────────────────────┐
 │ New heap tuple         │   │ HOT update: new tuple  │
 │ + new entry in EVERY   │   │ on same heap page,     │
 │   index on the table   │   │ NO new index entries   │
 │ Old entries left dead  │   │ (indexes untouched)    │
 └────────────┬───────────┘   └────────────────────────┘
              ▼
 ┌────────────────────────┐
 │ VACUUM removes dead    │
 │ entries; space reusable│
 │ inside the index only; │
 │ file is not shrunk     │
 └────────────┬───────────┘
              ▼
 B-tree leaf pages, ascending keys (e.g. sequence ids)
 ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
 │ 1-100    │ │ 101-200  │ │ 201-300  │ │ 301+     │
 │ #....... │ │ ##...... │ │ #####... │ │ ######## │◄── new keys
 └──────────┘ └──────────┘ └──────────┘ └──────────┘    land here
   Old pages stay sparse: freed space is reusable only by keys
   that sort into that page's range, so it is rarely refilled.
 # live entry   . free space (still part of the index file)

Non-HOT updates add an entry to every index and leave dead ones behind; VACUUM frees that space only for reuse inside the index, so with ever-increasing keys the older leaf pages stay sparse while new keys pile onto the rightmost page.

Measuring bloat: a cheap screen first, then pgstattuple for exact numbers

Start with numbers you already have. pg_relation_size() on each index, joined to pg_stat_user_indexes for relname, indexrelname and idx_scan, shows which indexes are large, which are actually used, and how each compares to its table. Look for indexes that kept growing while row counts stayed flat, or that have grown far larger than an equivalent index on similar data. This query reads only catalog metadata, so it is safe to run at any time.

The widely shared bloat-estimation queries go a step further. They use column widths from pg_stats to predict an index's ideal size and report the gap as bloat. Treat the output as a rough screen. avg_width is an average, so variable-width keys such as text or jsonb can skew the prediction badly. Multi-column keys add alignment padding the formulas often model incorrectly. NULLs and B-tree deduplication of repeated values also shift real sizes away from the estimate. The result can flag a healthy index or miss a bloated one.

To get exact figures, install the pgstattuple extension with CREATE EXTENSION pgstattuple; and call pgstatindex('index_name'). The two columns to check are avg_leaf_density, the percentage of leaf-page space holding live data, and leaf_fragmentation, which measures how far the leaf pages' physical order has drifted from their logical order.

pgstatindex reads every page of the index, so on large indexes it generates heavy I/O. Run it off-peak or on a replica. Use the cheap screen to pick which indexes to check this way.

Copy-- Step 1: cheap screen. Reads catalog metadata only; safe any time.
SELECT s.relname                                        AS table_name,
       s.indexrelname                                   AS index_name,
       s.idx_scan                                       AS scans_since_reset,
       pg_size_pretty(pg_relation_size(s.indexrelid))   AS index_size,
       pg_size_pretty(pg_relation_size(s.relid))        AS table_size,
       round(pg_relation_size(s.indexrelid)::numeric
             / nullif(pg_relation_size(s.relid), 0), 2) AS index_to_table_ratio
FROM pg_stat_user_indexes s
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

-- Step 2: exact numbers for the suspects from step 1.
-- pgstatindex reads every page of the index: run off-peak or on a replica.
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT index_size,
       leaf_pages,
       avg_leaf_density,    -- % of leaf-page space holding live data
       leaf_fragmentation   -- % of leaf pages out of logical order
FROM pgstatindex('public.orders_customer_id_idx');

A metadata-only query ranks indexes by size, scan count and size relative to their table, then pgstatindex() from pgstattuple returns exact leaf density and fragmentation for one suspect index.

Reading the results and deciding whether a rebuild is worth it

A freshly built B-tree packs leaf pages to its fillfactor, which defaults to 90. The baseline for avg_leaf_density is therefore about 90%, not 100%. An index at 70% is carrying modest slack. One at 40% has roughly half its leaf pages wasted. Ratio alone is misleading, though. A 50 MB index that is 40% empty rarely justifies a maintenance window, because reclaiming 20 MB changes nothing measurable.

Bloat costs you in four places:

• Disk and backups. Base backups copy every index page, and dead space is copied too.
• Cache. Sparse pages occupy shared_buffers and evict useful data.
• Index scans. Range scans touch more leaf pages to return the same rows.
• Vacuum. Vacuum scans each index in full, so the work grows with index size.

Before you rebuild, check idx_scan in pg_stat_user_indexes. An index with zero scans should be dropped, not rebuilt. Confirm that the statistics weren't reset recently and that replicas don't use the index, since each server keeps its own counters. Also confirm the index doesn't enforce a unique or primary key constraint.

Illustrative estimate. Assume a 20 GB index with avg_leaf_density of 45% and fillfactor 90. After a rebuild it would be about 20 × 45 / 90 ≈ 10 GB. That frees roughly 10 GB of disk and backup volume, and range scans touch about half as many leaf pages.

Copy-- Requires the contrib extension: CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- If stats were reset recently, idx_scan = 0 proves little. Check replicas too.
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- Shortlist large B-tree indexes first. pgstatindex reads every page.
WITH candidates AS MATERIALIZED (
  SELECT s.schemaname, s.relname, s.indexrelname, s.indexrelid, s.idx_scan,
         (i.indisunique OR i.indisprimary) AS enforces_constraint,
         COALESCE((SELECT o.option_value::int
                   FROM pg_options_to_table(c.reloptions) o
                   WHERE o.option_name = 'fillfactor'), 90) AS fillfactor
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
  JOIN pg_class c ON c.oid = s.indexrelid
  JOIN pg_am am   ON am.oid = c.relam
  WHERE am.amname = 'btree'
    AND pg_relation_size(s.indexrelid) > 1024 * 1024 * 1024  -- skip indexes under 1 GB
)
SELECT cand.schemaname, cand.relname, cand.indexrelname,
       cand.idx_scan,                      -- 0 and no constraint: drop candidate
       cand.enforces_constraint,
       cand.fillfactor,
       round(st.avg_leaf_density::numeric, 1)    AS leaf_density_pct,
       pg_size_pretty(st.index_size)              AS index_size,
       -- Estimated bytes a rebuild back to fillfactor would free
       pg_size_pretty(GREATEST(0, st.index_size
         * (1 - st.avg_leaf_density / cand.fillfactor))::bigint) AS est_reclaimable
FROM candidates cand
CROSS JOIN LATERAL pgstatindex(cand.indexrelid::regclass) st
ORDER BY st.index_size * (1 - st.avg_leaf_density / cand.fillfactor) DESC;

This query lists large B-tree indexes with their leaf density, fillfactor, scan count, constraint status, and estimated reclaimable bytes, which gives you the inputs for the decision table above.

How REINDEX CONCURRENTLY works and how it compares to the alternatives

REINDEX CONCURRENTLY works by building a replacement index next to the old one rather than rewriting the old one in place. It creates a new index definition in the catalog, marked invalid, and builds it in its own transaction. It then waits for every transaction that could have modified the table without knowing about the new index, and runs a second pass to pick up rows written during the build. Once the new index is valid, it swaps the names, marks the old index invalid, waits again for transactions still using it, and drops it. Each wait is needed because a running query may still rely on the old catalog state. This is also why the command is slow on a busy system with long-running transactions.

There are two requirements. You need free disk space roughly equal to the size of the new index, because both indexes exist at the same time. You also cannot run the command inside a transaction block, because it commits between phases.

The concurrent path scans the table twice and waits on other transactions. On a small table where a lock lasting a few seconds is acceptable, plain REINDEX is the better choice.

Running REINDEX CONCURRENTLY safely in production

1. Check free disk space. The rebuild writes a complete new copy of the index next to the old one, and the old copy is dropped only at the end. You need room for at least the index's current size plus some margin. A tablespace that fills mid-build is far worse than the bloat.

2. Inspect pg_stat_activity. Look for long-running queries and idle in transaction sessions. The build waits for every transaction that could still see the old index to finish, so one forgotten session can stall it for hours.

3. Set lock_timeout and statement_timeout deliberately. While the rebuild waits for its lock, it sits in the lock queue, and any later DDL on that table queues behind it. If an ALTER TABLE is already waiting, the rebuild queues behind that. A short lock_timeout makes it give up quickly instead of extending the pile-up. A statement timeout that fires mid-build leaves an invalid index behind (see the next section).

4. Rebuild one index at a time. Avoid table-wide or database-wide runs. Single-index runs keep each step's disk, WAL and failure scope small.

5. Watch pg_stat_progress_create_index. It shows the current phase and how many blocks or tuples have been processed.

Every index page is written to WAL. That traffic adds to replica lag and grows archive storage. It also forces logical replication consumers to read through the extra volume, and their slots hold WAL on the primary until they catch up. There is little built-in throttling, so scheduling is your main control: run rebuilds in low-traffic windows.

Primary key and unique index rebuilds fail if the build finds a uniqueness violation. Partitioned tables and system catalogs have restrictions that differ between releases, so check the documentation for your PostgreSQL version.

Copy-- Session 1: pre-checks, then rebuild a single index (PostgreSQL 12+).
-- Size of the index being rebuilt; confirm free space with `df` on its tablespace.
SELECT pg_size_pretty(pg_relation_size('public.orders_customer_id_idx'));

-- Long-running and idle-in-transaction sessions the build would wait on.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND (state = 'idle in transaction' OR now() - xact_start > interval '5 minutes')
ORDER BY xact_start;

-- Give up quickly on a lock wait instead of blocking other sessions behind us.
SET lock_timeout = '5s';
-- Disable the statement timeout for this session; a timeout mid-build leaves an invalid index.
SET statement_timeout = 0;

-- One index at a time. Must run outside a transaction block.
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- Session 2: monitor progress while the rebuild runs.
SELECT pid,
       relid::regclass        AS table_name,
       index_relid::regclass  AS index_name,
       phase,
       lockers_done, lockers_total,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

A psql session that checks index size and blocking transactions, sets lock_timeout and statement_timeout on purpose, rebuilds one index with REINDEX CONCURRENTLY, and tracks it from a second session through pg_stat_progress_create_index.

Recovering from a failed or interrupted rebuild

If REINDEX CONCURRENTLY fails or is cancelled partway through, PostgreSQL does not roll back the work it has already committed. The command runs as several separate transactions, so an error leaves the new index in the catalog marked invalid, usually named with a _ccnew suffix. A failure late in the swap phase can instead leave an index with a _ccold suffix. The planner will not use an invalid index for reads, but once it has reached the ready state it is still maintained on every insert and update. You pay the write overhead and disk space and get no read benefit.

To find these leftovers, query pg_index for rows where indisvalid is false and join to pg_class to get the index and table names. Remove each one with DROP INDEX CONCURRENTLY so you avoid taking a blocking lock on the table, then retry the rebuild. Do not retry first. A second attempt adds another index alongside the stale one.

Any automation that runs rebuilds should run this check before and after every attempt and clean up what it finds. Otherwise scheduled jobs quietly pile up invalid indexes.

Common causes of failure

• Uniqueness violations found during the build.
• Deadlocks with application transactions.
• statement_timeout or lock_timeout expiring during a long phase.
• Running out of disk space. The old and new indexes exist at the same time, so you need room for both.
• A dropped connection or terminated session, such as a closed SSH session or a killed pooler client.

Fix the underlying cause before you retry.

Preventing bloat and the impact on cost and engineering time

Making the fix last

A rebuild only resets the clock. If the churn pattern that caused the bloat stays the same, the bloat comes back.

• Tune autovacuum per table. The global defaults trigger vacuum after a fraction of the table changes. On a large, high-churn table that fraction is a lot of dead tuples. Lower autovacuum_vacuum_scale_factor (or set a fixed autovacuum_vacuum_threshold) as a storage parameter on that table only, so vacuum runs often enough to make index space reusable.
• Lower the table's fillfactor. A HOT update writes the new row version on the same heap page and adds no index entries. It only works when no indexed column changes and the page has free space. Reserving space per page makes HOT updates possible on update-heavy tables.
• Drop indexes on volatile columns that no query needs. One such index blocks HOT for every update that touches that column.
• Expect page splits after a rebuild. A rebuild packs leaf pages to the index's fillfactor. On a table with random-key inserts, those dense pages split almost at once, and some of the bloat returns within days. Lowering the index fillfactor reserves room for those inserts.
• Check, don't schedule. Run pgstatindex on your largest indexes on a schedule, and rebuild only the ones that cross your threshold.

Cost

Each rebuild needs disk headroom for a second copy of the index. It also needs a low-traffic window and creates on-call risk: a failed run leaves an invalid index that someone has to clean up.

Illustrative estimate (assumed numbers, not a benchmark): Assume 40 indexes are rebuilt monthly at 0.5 engineer-hours each, including monitoring and cleanup. Assume checks show only 6 actually need it.

Hours saved = (indexes rebuilt − indexes needing it) × hours per rebuild = (40 − 6) × 0.5 = 17 hours/month

Plug in your own counts and hours per rebuild.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql index bloat, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.