What is index bloat and why do Postgres B-tree indexes bloat?

Index bloat is the gap between the space an index occupies and the space its live entries actually need. A bloated B-tree has many pages that are mostly empty or filled with entries pointing at row versions no transaction can see anymore.

The root cause is MVCC. An UPDATE in Postgres does not modify a row in place. It writes a new row version, and unless the update qualifies as a HOT (heap-only tuple) update, every index on the table gets a new entry pointing at that version. The old entries stay behind. A DELETE similarly leaves index entries pointing at dead rows. VACUUM eventually removes those dead entries and marks the freed space reusable, and it can recycle pages that become completely empty. It rarely returns space to the operating system, so the index file stays at its high-water mark. B-tree pages also split when full, but half-empty neighbors are never merged back together.

Three workload patterns produce most bloat:

  • Heavy update or delete churn on indexed columns, which keeps adding entries faster than vacuum can reclaim them.
  • Mass deletes, such as purging old rows, which leave large ranges of sparsely populated pages.
  • Random-key inserts, such as random UUIDs, which split pages throughout the tree and leave many of them partly filled.

If you run high-churn tables, bloat has a real cost. Index scans read more pages to find the same rows. Mostly empty pages take up space in shared_buffers and the OS page cache that hot data could use. Disk usage, backup size, and replication traffic all grow without any increase in useful data.

Who should use REINDEX CONCURRENTLY?

REINDEX CONCURRENTLY is available in PostgreSQL 12 and later. It is meant for people who need to rebuild an index on a table that cannot stop taking writes:

  • DBAs and SREs running production Postgres where downtime or a maintenance window is expensive or hard to schedule, such as user-facing services that take writes around the clock.
  • Backend engineers who own their service's database and handle index maintenance themselves without a dedicated DBA team. The command is built into Postgres, so no extension or external tool is required.
  • Teams with high-churn tables, such as queues, session stores, order or event tables with frequent status updates, and tables purged on a schedule. These are the workloads described above that bloat indexes faster than vacuum can keep them compact.
  • Teams that need to fix a corrupted or invalid index on a live system. A rebuild replaces the index structure from the table data, and the concurrent form does it without blocking writes.

It is not the right tool if the table itself is bloated (the rebuild only touches indexes), if you are on a version older than 12, if the index is on a system catalog, or if you already have a window in which writes can stop. In that last case plain REINDEX is cheaper. The section on alternatives at the end covers these cases.

How do you measure index bloat instead of guessing?

Size alone tells you nothing. A 40 GB index on a 200 GB table may be perfectly dense, and a 2 GB index may be 70% empty. To decide whether a rebuild is worth it, you need to know how full the leaf pages actually are.

The pgstattuple extension provides pgstatindex(), which returns exact figures for a B-tree: avg_leaf_density, leaf_fragmentation, leaf_pages, and counts of empty and deleted pages. A freshly built B-tree with the default fillfactor of 90 sits near 90% leaf density. If the measured density is well below that, the gap is reclaimable space. The function gets this precision by reading every page of the index. On a large index that means a full scan's worth of I/O that competes with production traffic and evicts cached pages, so run it on a replica or during quiet periods, and only on indexes you have already shortlisted.

Catalog-based estimate queries are cheap because they infer expected size from pg_class row counts and pg_stats column widths. Treat them as a coarse filter. Stale statistics, NULLs, variable-width keys, and per-entry overhead they don't model can push the estimate far from reality in either direction.

Method Cost Accuracy Use for
Catalog estimate Negligible Approximate, can be badly wrong Shortlisting
pgstatindex() Full index read Exact Confirming before acting

Rank candidates by size, measured density, and idx_scan from pg_stat_user_indexes. A large, sparse, frequently scanned index justifies a rebuild. A bloated index with near-zero scans since the last stats reset is usually a candidate for DROP INDEX CONCURRENTLY instead. Before dropping one, check whether it enforces a constraint or is used on a replica, because each server keeps its own usage counters.

-- Requires the pgstattuple extension (contrib). Run on a replica or in a quiet window.
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Step 1: cheap shortlist. MATERIALIZED makes this filter run before any
-- pgstatindex() call, so only shortlisted indexes are read in full.
WITH shortlist AS MATERIALIZED (
  SELECT s.schemaname, s.relname, s.indexrelname, s.indexrelid, s.idx_scan,
         pg_relation_size(s.indexrelid) AS size_bytes,
         -- PK, unique, or constraint-backed indexes cannot simply be dropped
         (i.indisprimary OR i.indisunique
          OR EXISTS (SELECT 1 FROM pg_constraint c
                     WHERE c.conindid = s.indexrelid)) AS backs_constraint
  FROM pg_stat_user_indexes s
  JOIN pg_index i  ON i.indexrelid = s.indexrelid
  JOIN pg_class ic ON ic.oid = s.indexrelid
  JOIN pg_am am    ON am.oid = ic.relam
  WHERE am.amname = 'btree'                         -- pgstatindex() expects B-tree
    AND i.indisvalid                                -- skip leftovers from failed CONCURRENTLY builds
    AND pg_relation_size(s.indexrelid) > 1073741824 -- 1 GB threshold; adjust as needed
)
-- Step 2: exact measurement. Each pgstatindex() call reads the whole index.
SELECT sl.schemaname, sl.relname AS table_name, sl.indexrelname AS index_name,
       pg_size_pretty(sl.size_bytes) AS index_size,
       st.avg_leaf_density,              -- ~90 is normal for a fresh fillfactor-90 B-tree
       st.leaf_fragmentation,
       st.empty_pages, st.deleted_pages,
       sl.idx_scan,                      -- counter is local to this server
       sl.backs_constraint
FROM shortlist sl
CROSS JOIN LATERAL pgstatindex(sl.indexrelid::regclass) AS st
-- Rarely used indexes first (drop candidates), then the sparsest of the rest.
ORDER BY sl.idx_scan ASC, st.avg_leaf_density ASC, sl.size_bytes DESC;

A query that first narrows the list to large, valid B-tree indexes using cheap catalog data, then runs pgstatindex() only on that list. It reports exact leaf density next to idx_scan and flags constraint-backed indexes, so you can choose between rebuilding an index and dropping it.

How does it work? Inside REINDEX CONCURRENTLY

REINDEX CONCURRENTLY builds a replacement index next to the old one, keeps both maintained during the rebuild, and switches over only once the new one is complete. Each step runs in its own transaction.

  1. Create and build. A new invalid index (suffixed _ccnew) is added to the catalog and built from a snapshot of the table. Writes continue, and new rows are added to both indexes.
  2. Validate. A second pass over the table inserts any rows the build snapshot missed.
  3. Wait. Postgres waits for every transaction whose snapshot predates the new index to finish.
  4. Swap. The new index takes over the old name, and the old index is marked dead.
  5. Drop. The old index is removed after another wait.
time ───────────────────────────────────────────────────────►
old idx  [serving reads + writes ............][dead]──X dropped
new idx         [build][validate]  [serving reads + writes ...]
locks     SUE ──────────────────────────────────────────────── (whole run)
                     ^wait      ^wait   ^swap  ^wait  ^drop

The table holds SHARE UPDATE EXCLUSIVE (SUE) for the entire run. This lock blocks other DDL and VACUUM, but not reads or writes. The swap and drop take brief, stronger locks on the indexes themselves. Each wait point blocks until older transactions end. A single session left idle in transaction for an hour stalls the rebuild for that hour, and Postgres reports no error while it waits.

Plain REINDEX REINDEX CONCURRENTLY
Table writes Blocked Allowed
Reads using the index Blocked Allowed
Table scans Usually one At least two
Extra disk Brief Full second index
Failure leftover None Invalid index
 Table lock: SHARE UPDATE EXCLUSIVE held for the whole run
 (reads and writes continue; other DDL and VACUUM on the table wait)

 ┌────────────┐    ┌────────────┐    ┌────────────┐    ┌──────────────────┐
 │ 1. Create  │    │ 2. Validate│    │ 3. Wait    │    │ long-running txn │
 │ + build    │───►│ 2nd table  │───►│ for older  │◄───│ or idle in txn   │
 │ idx_ccnew  │    │ scan adds  │    │ snapshots  │    │ old snapshot     │
 │ (invalid)  │    │ missed rows│    │ to finish  │    │ stalls, no error │
 └────────────┘    └────────────┘    └─────┬──────┘    └──────────────────┘
                                           │
 ┌────────────┐    ┌────────────┐          │
 │ 5. Drop    │    │ 4. Swap    │          │
 │ old index  │◄───│ _ccnew gets│◄─────────┘
 │ after wait │    │ old name,  │
 │ brief lock │    │ brief lock │
 │ on indexes │    │ on indexes │
 └────────────┘    └────────────┘

 old index: serves reads + writes until step 4, then dead, then dropped
 new index: receives writes from step 1, serves reads after step 4

 Plain REINDEX: one build, no wait points, table writes blocked throughout

REINDEX CONCURRENTLY builds and validates a replacement index under a table-level SHARE UPDATE EXCLUSIVE lock, waits for older snapshots, then swaps and drops with brief index locks, and any long-running transaction stalls it at the wait points.

How do you run postgres reindex concurrently safely in production?

Rebuild one index per statement with REINDEX INDEX CONCURRENTLY. REINDEX TABLE CONCURRENTLY rebuilds every index on the table in one long operation, which multiplies disk use, WAL volume, and the work you lose if it fails.

  1. Check free disk first. Both copies of the index exist until the swap, so you need free space roughly equal to the current index size, plus headroom for WAL. Running out of disk mid-build is worse than not starting.

  2. Plan for WAL. The new index is written to WAL in full. On a large index, that volume can increase replica lag, fill archive storage, and make replication slots retain WAL if a consumer falls behind. Run the rebuild in a quiet window and watch lag while it runs.

  3. Use a session nothing will kill. Connect directly to Postgres, not through a pooler with idle or query timeouts, and run the command from tmux or screen on a host, not from a laptop. A dropped connection leaves an invalid index behind.

  4. Set timeouts deliberately.

    • Set a short lock_timeout. Each phase waits behind older transactions, and a long wait can make other sessions queue behind the rebuild.
    • Disable or raise statement_timeout for the session, because the build itself can legitimately take hours.
  5. Retry on lock timeouts. Before each retry, drop any leftover _ccnew index (see the next section). Then back off and try again.

  6. Work around migration frameworks. Most frameworks wrap each migration in a transaction, and REINDEX CONCURRENTLY refuses to run inside a transaction block. Use the framework's per-migration opt-out, or run the rebuild outside the framework.

  7. Leave system catalogs out. REINDEX CONCURRENTLY does not support them.

#!/usr/bin/env bash
set -euo pipefail
SCHEMA=public
NAME=orders_customer_id_idx
export PGHOST=db-primary.internal PGPORT=5432 PGDATABASE=app PGUSER=maint
PSQL=(psql -X -At -v ON_ERROR_STOP=1)

"${PSQL[@]}" -c "SELECT pg_size_pretty(pg_relation_size('$SCHEMA.$NAME'::regclass))"
echo "Check free space on the data and WAL volumes (df -h on the DB host)."
read -r -p "Proceed? [y/N] " ok; [[ "$ok" == y ]] || exit 1

for attempt in 1 2 3 4 5; do
  # Drop invalid leftovers (NAME_ccnew, NAME_ccnew1, ...) from a failed attempt.
  for leftover in $("${PSQL[@]}" -c "SELECT c.oid::regclass FROM pg_index i
      JOIN pg_class c ON c.oid = i.indexrelid
      WHERE NOT i.indisvalid AND c.relnamespace = '$SCHEMA'::regnamespace
        AND c.relname ~ '^${NAME}_ccnew[0-9]*\$'"); do
    "${PSQL[@]}" -c "DROP INDEX CONCURRENTLY IF EXISTS $leftover"
  done

  # Separate -c flags = separate statements in one session, so no transaction block.
  # Short lock_timeout avoids queueing other sessions; no statement_timeout for the build.
  if "${PSQL[@]}" -c "SET lock_timeout = '5s'" -c "SET statement_timeout = 0" \
       -c "REINDEX INDEX CONCURRENTLY $SCHEMA.$NAME"; then
    echo "Rebuilt $SCHEMA.$NAME on attempt $attempt"; exit 0
  fi
  echo "Attempt $attempt failed (likely lock timeout); backing off" >&2
  sleep $((attempt * 60))
done
exit 1

A bash wrapper that checks index size against free disk, cleans up invalid _ccnew leftovers, and retries REINDEX INDEX CONCURRENTLY on a single index with a short lock_timeout and no statement_timeout, outside any transaction block.

What happens when REINDEX CONCURRENTLY fails, and how do you clean up?

A failed or cancelled run does not roll back cleanly. REINDEX CONCURRENTLY commits in several separate transactions, so an error, a statement_timeout, a deadlock, or a cancelled session can leave the catalog partway through the operation. The usual leftover is an index marked invalid with a _ccnew suffix (sometimes _ccnew1 and so on, if the name was already taken). That index is the half-built replacement. If the failure happened after the swap, you get a _ccold index instead. That is the original index, which could not be dropped. In that case the rebuild itself succeeded.

These leftovers cost you. The planner never uses an invalid index, but Postgres may still maintain it on every insert and update, and it still takes up disk. In effect you pay write amplification and storage for an index that gives you nothing.

To find them, join pg_index to pg_class and filter on indisvalid = false. Then check each name for a _ccnew or _ccold suffix. Remove each one with DROP INDEX CONCURRENTLY. For _ccnew, retry the rebuild afterward. For _ccold, there is nothing to retry. Retry against the specific index, because REINDEX TABLE CONCURRENTLY skips invalid indexes.

Automating detection

Run the invalid-index query as a post-step of every rebuild job, and also on a schedule. Alert on anything it finds. Do not drop indexes automatically without two guards:

  • Check for in-progress builds. An index that is being built concurrently right now also reports indisvalid = false. Check pg_stat_progress_create_index first.
  • Restrict drops by suffix. Only drop indexes whose names carry the _ccnew or _ccold suffix. An invalid index from someone's failed CREATE INDEX CONCURRENTLY needs a human decision.
-- Find invalid indexes left behind by a failed REINDEX CONCURRENTLY.
-- Skips tables with a CREATE INDEX / REINDEX currently running, because
-- an index being built right now also reports indisvalid = false.
SELECT n.nspname                              AS schema_name,
       c.relname                              AS index_name,
       t.relname                              AS table_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS wasted_size,
       CASE
         WHEN c.relname ~ '_ccnew[0-9]*$' THEN 'ccnew: drop, then retry rebuild'
         WHEN c.relname ~ '_ccold[0-9]*$' THEN 'ccold: drop only, rebuild succeeded'
         ELSE 'not from REINDEX: needs a human decision'
       END                                    AS action
FROM pg_index i
JOIN pg_class c     ON c.oid = i.indexrelid
JOIN pg_class t     ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid
  AND NOT EXISTS (
        SELECT 1
        FROM pg_stat_progress_create_index p
        WHERE p.datid = (SELECT oid FROM pg_database
                         WHERE datname = current_database())
          AND p.relid = i.indrelid)
ORDER BY pg_relation_size(c.oid) DESC;

-- Cleanup for a reported _ccnew leftover (placeholder names).
-- Run outside a transaction block: both commands refuse to run inside one.
DROP INDEX CONCURRENTLY public.orders_customer_id_idx_ccnew;

-- Retry the specific index; REINDEX TABLE CONCURRENTLY would skip invalid ones.
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- For a _ccold leftover, the drop is the whole fix; there is nothing to retry.
DROP INDEX CONCURRENTLY public.orders_created_at_idx_ccold;

A detection query that lists invalid indexes, skips tables with an index build in progress, and classifies each result by its _ccnew or _ccold suffix, followed by the DROP INDEX CONCURRENTLY and targeted REINDEX INDEX CONCURRENTLY steps used to clean up and retry.

How do you verify the rebuild and keep bloat from coming back?

Confirm the rebuild worked

Record pg_relation_size for the index before the rebuild and compare it afterward. A smaller index is the expected result, but size alone does not prove the structure is healthy. Run pgstatindex again and check that avg_leaf_density has risen toward the index's fillfactor and that leaf_fragmentation has dropped. While the rebuild runs, watch pg_stat_replication for replay lag and watch disk I/O. The rebuild reads the whole table and writes a full new index, and every page it writes is WAL that replicas have to replay.

Keep bloat from returning

  • Tune autovacuum per table. The default scale factor waits until a fixed fraction of a large table is dead before vacuuming. On high-churn tables, set a lower autovacuum_vacuum_scale_factor as a table storage parameter so dead index entries are cleaned up sooner.
  • Lower fillfactor on update-heavy indexes. Leaving free space on leaf pages absorbs new entries without page splits. The setting only applies to pages built after the change, so it takes effect at the next rebuild.
  • Hunt long-running transactions. Any old snapshot, including one held by an idle-in-transaction session, stops vacuum from removing dead entries. Check backend_xmin in pg_stat_activity and set idle_in_transaction_session_timeout.
  • Track index size over time. Store size snapshots in a metrics table so you can set growth thresholds and plan rebuilds ahead of time.

Illustrative estimate: assume each unplanned bloat incident costs two engineers four hours, and it happens monthly. That is about 96 engineer-hours a year. A scheduled quarterly rebuild that takes one hour to run and review costs about four hours a year.

How can REINDEX CONCURRENTLY save engineering time?

Most of the time spent on index maintenance goes to coordination and manual steps, not to the rebuild itself. REINDEX CONCURRENTLY removes several of those costs:

  • No maintenance window to negotiate. Because reads and writes continue, you do not have to schedule downtime with product teams, announce it to users, or have someone working late to run it. The rebuild can run during normal working hours while the person who started it watches lag and I/O.
  • No manual swap procedure. Before REINDEX CONCURRENTLY existed, the usual approach was CREATE INDEX CONCURRENTLY under a new name, DROP INDEX CONCURRENTLY on the old one, and a rename. Each step had to be scripted and checked, and indexes backing primary keys or unique constraints needed extra handling to reattach the constraint. One command now does the build, swap, and drop, and the rebuilt index keeps its name and its constraint associations.
  • Repeatable automation. The pieces in this article (the shortlist query, the retry wrapper, and the leftover-detection query) can be combined into a scheduled job. After that, routine rebuilds become a review of the job's output instead of a hands-on task.
  • No extra tooling to maintain. The command ships with Postgres 12 and later, so there is no extension to install, upgrade, or keep in sync with a client binary, unlike table-rewriting tools.
  • Less incident work. Rebuilding bloated indexes on a schedule, before they degrade query latency or fill a disk, replaces reactive debugging with planned work. The illustrative estimate in the previous section shows how that tradeoff can add up under its assumed inputs.

Illustrative estimate: assume a manual rebuild in a maintenance window takes 3 hours of one engineer's time across planning, stakeholder coordination, an off-hours execution slot, and verification, and that a scripted REINDEX CONCURRENTLY run takes 30 minutes of attention to start, monitor, and review. With an assumed 12 rebuilds a year, that is about 36 hours versus about 6 hours. These inputs are assumptions. Substitute your own team's figures.

The time savings depend on the safeguards above. A rebuild that stalls on an idle transaction, or leaves _ccnew leftovers nobody notices, costs time instead of saving it. Automate the checks as well as the command.

When should you avoid REINDEX CONCURRENTLY, and what are the alternatives?

Defer a concurrent rebuild when any of these conditions hold:

  • The volume is nearly full. The new index is built alongside the old one, so you temporarily need roughly the index's size again in free space, plus the WAL the build generates. Running out of disk mid-build is a worse outage than the bloat.
  • Long-running transactions are open. The command waits for older transactions at several phases. A forgotten idle in transaction session can stall it for hours. End that session first.
  • Replicas or replication slots are fragile. The full index build is WAL-logged. A lagging replica or an inactive slot will retain that WAL on the primary and widen the lag.
  • A maintenance window already exists. Plain REINDEX does less work and finishes faster. If writes to the table can pause, it is the cheaper option.
Option Blocks writes Fixes table bloat Extra requirements
REINDEX CONCURRENTLY No No Free disk, no long transactions
Plain REINDEX Yes, for its duration No Maintenance window
DROP INDEX (unused) Briefly, or not with CONCURRENTLY No Confirm zero scans in usage statistics
pg_repack Brief exclusive locks Yes Extension, client binary, primary key or unique index for table repacks

pg_repack is the right tool when the table itself is bloated. It also has the highest operational cost: you install and upgrade an extension, keep a matching client binary, and plan for its short exclusive locks at the start and end of the rebuild.

Rebuilding does not fix the cause of the bloat. If autovacuum is tuned too conservatively or long transactions keep blocking cleanup, the bloat will come back. Fix those first, and use scheduled rebuilds only for whatever bloat remains.

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 postgres reindex concurrently, 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.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • 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.