Why do PostgreSQL B-tree indexes bloat, and why doesn't VACUUM shrink them?

A B-tree index is a set of fixed-size pages (8 kB by default) holding sorted keys. When an insert targets a full leaf page, PostgreSQL splits it into two pages, each roughly half full. If later inserts land in those key ranges, the space fills back up. If they don't, the half-empty pages stay that way. Random keys such as UUIDs spread inserts across the whole index, so splits happen everywhere and many pages end up partially filled.

Updates and deletes add more waste. In PostgreSQL, an UPDATE writes a new row version. Unless it qualifies as a HOT update, which requires that no indexed column changed and that the new version fits on the same heap page, the update also adds a new entry to every index on the table. The old entries become dead once no transaction can see the old row version.

VACUUM removes those dead index entries and marks the freed space reusable. It does not compact live entries into fewer pages. Space freed inside a page can only take keys that sort into that page's range. A page that becomes completely empty can be recycled for new splits, but it remains part of the file. VACUUM generally does not return B-tree pages to the operating system, so the index file stays the same size even when most of it is empty.

This affects teams running high-churn tables: job queues, session or event tables purged by time range, and status columns that are updated constantly. Bloated indexes use more disk and memory, and scans take longer because they read more pages for the same number of keys.

How do you measure index bloat with pgstattuple?

pgstattuple is a contrib extension that ships with standard PostgreSQL builds but must be enabled per database with CREATE EXTENSION pgstattuple. Using it requires a superuser or a role that has been granted access to its functions. For B-tree indexes, call pgstatindex('index_name'). Two of its output columns matter most:

  • avg_leaf_density is the average percentage of each leaf page that holds live data. A freshly built B-tree with the default fillfactor of 90 reads close to 90, so compare against that baseline rather than 100. A value well below it, such as the 40s or 50s, means most leaf pages are half empty.
  • leaf_fragmentation is the percentage of leaf pages whose physical successor is not their logical successor. High values hurt range scans, because reading keys in order turns into scattered I/O.

deleted_pages and empty_pages show space that VACUUM has reclaimed inside the index but that has not been returned to the filesystem.

pgstatindex is exact because it reads every page of the index. On a large index this means real I/O and buffer cache churn. Run it on a replica if you can, or off-peak, and one index at a time.

For a quick survey, a cheaper option is to compare pg_relation_size and pg_class.relpages with the size you would expect from reltuples and the key width. Then run pgstatindex only on the candidates that look suspicious.

Approach Reads index pages Accuracy Cost on large indexes Best use
pgstatindex All of them Exact High I/O, scales with size Confirming a specific index
Catalog-based estimate None Approximate, depends on statistics Negligible Screening every index
-- One-time setup, per database (needs suitable privileges).
CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Step 1: cheap screen. Reads only the catalog, no index pages.
-- A bigint key in a B-tree needs roughly 20 bytes per entry, so a
-- much higher bytes_per_tuple on such an index points to bloat.
-- reltuples is an estimate, so this is only as fresh as the last
-- VACUUM/ANALYZE.
SELECT c.oid::regclass                         AS index_name,
       pg_size_pretty(pg_relation_size(c.oid)) AS on_disk,
       c.relpages,
       c.reltuples::bigint                     AS est_tuples,
       round(pg_relation_size(c.oid)::numeric
             / c.reltuples::numeric, 1)        AS bytes_per_tuple
FROM pg_class c
JOIN pg_am am ON am.oid = c.relam
WHERE c.relkind = 'i'
  AND am.amname = 'btree'
  AND c.reltuples > 0          -- skip never-analyzed (-1) and empty
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;

-- Step 2: exact check on one suspicious candidate.
-- This reads every page of the index, so run it off-peak or on a
-- replica, one index at a time.
SET statement_timeout = '10min';  -- guard against a runaway scan
SELECT pg_size_pretty(index_size) AS index_size,
       leaf_pages,
       empty_pages,
       deleted_pages,
       avg_leaf_density,    -- compare to ~90 (default fillfactor)
       leaf_fragmentation   -- high values mean scattered range scans
FROM pgstatindex('public.orders_customer_id_idx');
RESET statement_timeout;

The example screens B-tree indexes for bloat using only catalog statistics, then runs the exact but I/O-heavy pgstatindex on a single candidate to read its leaf density and fragmentation.

When is rebuilding an index worth it, and when should you avoid it?

Rebuild when the numbers show a problem, not on a calendar. Three signals justify it:

  • Size versus expected size. The index is much larger than a freshly built copy would be. Estimate the fresh size from row count and key width, or compare against the same index on a recently restored copy.
  • Low average leaf density. A new B-tree packs leaf pages to its fillfactor (90 by default). An avg_leaf_density far below that means most leaf pages are mostly empty space.
  • Observed effects. Index scans read more buffers than before for the same rows, or the index no longer fits in memory and cache hit ratios fall.

Skip or postpone the rebuild in these cases:

  • The disk is nearly full. The new index is built alongside the old one.
  • The primary is busy and replicas are sensitive to lag. The build writes the whole index to WAL.
  • Long-running transactions cannot be avoided. The operation waits for them to finish.
  • The bloat will return. If the workload rebloats the index within days, the rebuild only buys a short reprieve.

Illustrative estimate. Assume a 40 GB index with an average leaf density of 45%. At fillfactor 90, a rebuild produces roughly 40 × 45 / 90 ≈ 20 GB. You need about 20 GB of free space for the new index, plus a similar volume of WAL that must be retained until it is archived and replayed. Assume the underlying table is 100 GB and the disk reads 200 MB/s. Each table scan then takes about 8–9 minutes, and a concurrent build scans the table twice before sorting.

Checking density first takes minutes. Rebuilding every index on a schedule spends hours of I/O and WAL on indexes that gain nothing.

Who should use REINDEX CONCURRENTLY?

REINDEX CONCURRENTLY is available in PostgreSQL 12 and later. It is aimed at the engineers who own a production database that cannot stop accepting writes: DBAs, platform and SRE teams, and application developers on small teams who run their own Postgres.

It is a good fit when all of the following hold:

  • The index is measurably bloated by the signals in the previous section, not just old.
  • The table takes writes continuously, so the SHARE lock that plain REINDEX holds would stall the application for the length of the build.
  • There is no convenient maintenance window, or scheduling one costs more coordination than the rebuild is worth.
  • The index backs a primary key or unique constraint. REINDEX CONCURRENTLY moves constraints and dependencies to the new index for you, which a hand-built CREATE INDEX CONCURRENTLY replacement does not.
  • You have disk headroom for a second copy of the index and the WAL it generates.

It is usually the wrong tool in these cases:

  • Small tables, where plain REINDEX finishes quickly enough that a brief write block does not matter.
  • Systems that already have a maintenance window, where the faster single-pass REINDEX is simpler.
  • Workloads with unavoidable long-running transactions, such as long reporting queries or batch jobs, which stall every wait phase.
  • Index changes rather than rebuilds. If you want a different definition, columns or tablespace, build a new index with CREATE INDEX CONCURRENTLY instead.

You also need sufficient privileges on the table, typically by owning it, and the ability to run a statement outside a transaction block.

How does REINDEX CONCURRENTLY work?

In short, it builds a new copy of the index next to the old one while the table stays open for reads and writes, catches up on rows that changed during the build, then swaps the new index in under the old name and drops the old one. It achieves this by splitting the work into several short transactions and waiting between them, instead of holding one strong lock for the whole rebuild.

The phases

Plain REINDEX takes a SHARE lock on the table, which blocks writes, and an ACCESS EXCLUSIVE lock on the index, which blocks any query that would use it. REINDEX CONCURRENTLY instead holds SHARE UPDATE EXCLUSIVE on the table and its indexes. That lock allows SELECT, INSERT, UPDATE and DELETE. It conflicts only with other schema changes, VACUUM and ANALYZE.

To rebuild under that weaker lock, PostgreSQL splits the work across several transactions:

  1. Create a shadow index. A new catalog entry with a _ccnew suffix is added and marked not ready.
  2. First build pass. The table is scanned and the new index is built from a snapshot. The index is then marked ready, so concurrent writes start maintaining it.
  3. Wait and validate. PostgreSQL waits for transactions that might not see the new index. It then scans the table again to add rows inserted or updated during the first pass. After another wait, the index is marked valid.
  4. Swap. Names, constraints and dependencies move to the new index. The old one is renamed with a _ccold suffix and marked invalid.
  5. Retire and drop. PostgreSQL waits for queries that may still reference the old index, then drops it.

What it costs

Each wait blocks until older transactions finish. A long-running transaction or an idle-in-transaction session can therefore stall the rebuild indefinitely. The extra table scan, the separate transactions, and the double index maintenance during the build make it slower than a plain rebuild, and it uses more I/O, CPU and WAL. It also cannot run inside a transaction block. You pay that extra work so that writes and reads continue throughout the rebuild.

 Lock: SHARE UPDATE EXCLUSIVE on table + indexes (DML continues)
+----------------------------------------------------------------+
| 1. Create shadow index idx_ccnew (catalog entry, not ready)    |
+----------------------------------------------------------------+
                                |
                                v
+----------------------------------------------------------------+
| 2. Build pass: scan table (#1) from a snapshot                 |
|    -> mark ready; writes now maintain old AND new index        |
+----------------------------------------------------------------+
                                |
                                v  wait: older transactions finish
+----------------------------------------------------------------+
| 3. Validate: scan table (#2), add rows changed in pass 1       |
|    -> wait for older transactions -> mark idx_ccnew valid      |
+----------------------------------------------------------------+
                                |
                                v
+----------------------------------------------------------------+
| 4. Swap: names, constraints, dependencies -> new index         |
|    old index renamed idx_ccold, marked invalid                 |
+----------------------------------------------------------------+
                                |
                                v  wait: queries using idx_ccold end
+----------------------------------------------------------------+
| 5. Drop idx_ccold                                              |
+----------------------------------------------------------------+
 Cost: 2 table scans, double index upkeep, many txns -> more I/O,
 CPU and WAL. Long or idle-in-transaction sessions stall each wait.

REINDEX CONCURRENTLY builds, validates and swaps a shadow index across several transactions under a SHARE UPDATE EXCLUSIVE lock, so reads and writes continue at the price of extra scans, waits and WAL.

Step-by-step: running postgres reindex concurrently safely

Treat the rebuild as a planned operation, not a one-line fix.

Before you start

  1. Check disk headroom. The new index is built alongside the old one, so you need free space for roughly a second copy. Compare pg_relation_size() of the index against free space on the volume. Leave a margin for the extra WAL the build generates.
  2. Clear long-running and idle-in-transaction sessions. Several phases wait for every transaction that could still use the old index. One forgotten session in pg_stat_activity with state = 'idle in transaction' can stall the rebuild indefinitely.
  3. Set timeouts deliberately. Set a short lock_timeout so the command fails fast instead of queueing behind a lock while application queries pile up behind it. Disable statement_timeout for this session, or set it generously. If it fires mid-build, you are left with an invalid index to clean up.
  4. Run outside a transaction block. Many migration tools wrap each migration in BEGIN/COMMIT, and the command will fail there with an error. Use your tool's per-migration option to disable the wrapping transaction, or run the rebuild from a separate session.
  5. Rebuild one index at a time, during low traffic. Rebuilding a whole table or schema at once multiplies disk, I/O and WAL pressure. It also makes failures harder to untangle.

While it runs

Query pg_stat_progress_create_index from another session:

  • phase, blocks_done and blocks_total show how far the build has progressed.
  • lockers_total, lockers_done and current_locker_pid show which transaction it is waiting on.

To see what is blocking the rebuild, join pg_stat_activity to pg_locks, or call pg_blocking_pids() on the rebuild's PID. Then decide whether to wait for that session or terminate it.

-- Session 1: preflight checks, then the rebuild (plain psql, autocommit, no BEGIN)
SELECT pg_size_pretty(pg_relation_size('public.orders_customer_id_idx')) AS index_size;

-- Sessions that would stall the rebuild's wait phases
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;

SET lock_timeout = '5s';       -- fail fast instead of queueing ahead of app queries
SET statement_timeout = 0;     -- don't abort mid-build and leave an invalid index
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

-- Session 2: watch progress and what the build is waiting on
SELECT pid, phase, blocks_done, blocks_total,
       lockers_done, lockers_total, current_locker_pid
FROM pg_stat_progress_create_index;

-- Session 2: who is blocking the rebuild, and what they hold
SELECT a.pid, a.state, now() - a.xact_start AS xact_age,
       l.locktype, l.mode, l.granted, left(a.query, 60) AS query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.pid = ANY (pg_blocking_pids(
        (SELECT pid FROM pg_stat_progress_create_index LIMIT 1)));

-- If the build failed, find leftover invalid indexes (suffix _ccnew) to drop
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

A psql runbook that checks index size and long-running transactions, sets lock_timeout and statement_timeout, runs REINDEX INDEX CONCURRENTLY outside a transaction block, and monitors progress and blockers from a second session.

How do you recover from an interrupted REINDEX CONCURRENTLY?

A failed or cancelled run does not roll back cleanly. Because REINDEX CONCURRENTLY commits several separate transactions, an interruption leaves behind whatever it had already created. The leftover is an index marked invalid. The planner will not use it, but if it was already marked ready for inserts, every INSERT and UPDATE on the table still maintains it. You pay the write and storage cost and get no read benefit. If the leftover is a unique index, it can also reject writes that conflict with it.

Finding the leftovers

Query pg_index for rows where indisvalid is false, and join to pg_class to get the index and table names. The suffix tells you which phase failed:

  • _ccnew (possibly with a trailing number, such as _ccnew1): this is the replacement index that was still being built. The original index is intact and still serving queries. Drop the _ccnew index and retry.
  • _ccold: the swap already happened, so the new index has taken the original name and is in use. Only the old copy could not be dropped. Drop the _ccold index. You do not need to retry.

Cleaning up and retrying

Use DROP INDEX CONCURRENTLY so the cleanup does not take a lock that blocks writes. It cannot run inside a transaction block, so run each drop as its own statement. Afterwards, run the indisvalid query again and confirm it returns no rows for that table. Also check that the original index name exists and is valid.

Before you retry, find out why the run failed. Common causes are a lock timeout, a long-running transaction that was cancelled, or a uniqueness violation. If you retry without fixing the cause, it will usually fail the same way and leave another _ccnew index behind.

-- 1. List invalid indexes left behind by an interrupted REINDEX CONCURRENTLY.
--    indisready = true means writes are still maintaining the index.
SELECT n.nspname      AS schema_name,
       c.relname      AS index_name,
       t.relname      AS table_name,
       i.indisready   AS maintained_on_write
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
ORDER BY 1, 3, 2;

-- 2. Drop the leftover without blocking writes. Run each statement on its own,
--    outside BEGIN/COMMIT, because DROP INDEX CONCURRENTLY cannot run in a transaction block.
--    _ccnew: build failed and the original is intact, so drop this and retry later.
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx_ccnew;
--    _ccold: swap succeeded, so drop the old copy. No retry needed.
-- DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx_ccold;

-- 3. Verify: the original name exists and is valid (errors if the name is missing).
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'public.orders_customer_id_idx'::regclass;

-- 4. Before retrying, look for long-running transactions that would stall the rebuild.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- 5. Retry once the cause is fixed. Run it outside a transaction block.
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;

This example finds invalid indexes left by an interrupted REINDEX CONCURRENTLY, drops the _ccnew or _ccold leftover with DROP INDEX CONCURRENTLY, checks that the original index is valid, looks for long-running transactions, and then retries the rebuild.

How can REINDEX CONCURRENTLY save engineering time?

The time savings come mostly from coordination and scripting work you no longer have to do, not from the rebuild itself, which is slower than a plain REINDEX.

  • No maintenance window to negotiate. A blocking rebuild on a busy table means agreeing on a window with application owners, announcing it, and staffing it. A concurrent rebuild can run during ordinary low-traffic hours with one engineer watching progress.
  • No hand-written swap scripts. Replacing an index with CREATE INDEX CONCURRENTLY means building, verifying, dropping and renaming, plus extra steps for indexes that back primary keys or unique constraints. REINDEX CONCURRENTLY performs the swap, including constraints and dependencies, in one command, so there is less code to write, review and get wrong.
  • A repeatable runbook. The preflight checks, timeouts, progress query and cleanup query in this article are the same for every index. Once written, they can be kept in a script or an internal runbook and reused, rather than reworked for each incident.
  • Predictable failure handling. An interrupted run leaves a recognisable _ccnew or _ccold index rather than an ambiguous state. Recovery is a known two-step process: drop the leftover, fix the cause, retry.
  • Fewer wasted rebuilds. Pairing it with pgstatindex checks means engineers spend time only on indexes that are actually bloated, instead of rebuilding everything on a schedule.

Illustrative estimate. Assume a team rebuilds one bloated index per quarter. Assume a blocking rebuild needs about two hours of engineering time to schedule a window, notify stakeholders and run the operation out of hours, and that a scripted CREATE INDEX CONCURRENTLY swap for a constraint-backed index needs about three hours to write and review the first time. Assume a REINDEX CONCURRENTLY run using an existing runbook needs about 30 minutes of attention. Under those assumed inputs, each rebuild saves roughly 1.5 to 2.5 hours of engineering time, and the runbook cost is paid only once. Your numbers will depend on your team's processes and should be measured rather than taken from this example.

What are the alternatives to REINDEX CONCURRENTLY, and how do you prevent bloat returning?

REINDEX CONCURRENTLY is the default choice for online maintenance, but two other approaches are worth knowing, and sometimes the right answer is to leave the index alone.

Option Write blocking Speed Operational cost
CREATE INDEX CONCURRENTLY, then drop and rename Brief locks only Slower, two table scans You script and verify each step
Plain REINDEX in a maintenance window Blocks writes, and queries that use the index Fastest, one pass Needs a scheduled window
Do nothing None n/a Ongoing extra disk use and cache pressure

Building a replacement index with CREATE INDEX CONCURRENTLY gives you more control. You can change the definition, the fillfactor or the tablespace, and you can test the new index before dropping the old one. The cost is extra development work. Indexes that back primary keys or unique constraints can't simply be dropped. You have to swap them through the constraint, which means more steps to get wrong.

Plain REINDEX is simpler and faster. It suits small tables, or systems that already have a regular maintenance window.

Doing nothing is reasonable when bloat is stable. Freed pages get reused, so an index that stays at a steady size isn't getting worse.

Rebuilding only treats the symptom. Bloat comes back if its causes stay in place, so prevention matters more. Two settings address the usual causes:

  • Lower fillfactor. On indexes with heavy random inserts or updates, a lower fillfactor leaves free space in each page. That reduces page splits.
  • Per-table autovacuum thresholds. Lower autovacuum_vacuum_scale_factor on large, high-churn tables. Dead entries are then cleaned up before they pile up.

Combine prevention with scheduled pgstattuple checks, and rebuild only when a measured threshold is crossed. This replaces routine maintenance with a few rebuilds that each have a measured reason.

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.
  • The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
  • You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
  • 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.