pgvector Index Management Embedding Pipeline Optimization

Detecting HNSW Index Bloat with pgstattuple

This page shows how to use the pgstattuple extension to measure physical bloat inside an HNSW vector index and decide, from concrete thresholds, whether a REINDEX INDEX CONCURRENTLY is warranted. It scopes the problem narrowly: given a large HNSW index whose size keeps growing after heavy UPDATE/DELETE churn, how to read dead_tuple_percent, free_percent, and leaf fragmentation without misreading the whole-relation scan that pgstattuple performs.

Up: Monitoring Vector Indexes with pg_stat Views

HNSW indexes accumulate wasted space in a way plain size metrics hide. Every UPDATE to a row’s embedding writes a new heap tuple and a new graph entry; the old graph node is only tombstoned, and its space is reclaimed lazily by VACUUM. Over a churn-heavy month the on-disk index can be far larger than the live data it serves, quietly raising memory pressure and cache misses. pgstattuple reads the actual page contents to tell you how much of that space is dead versus free, which is the evidence you need before scheduling the expensive rebuild described in zero-downtime index operations.

Prerequisites

  • PostgreSQL 15+ and the ability to CREATE EXTENSION (superuser or a role with the privilege).
  • The pgvector extension with at least one HNSW index under real churn; the build parameters are covered in step-by-step HNSW index creation for production workloads.
  • A maintenance window or a read replica: pgstattuple and pgstatindex take an ACCESS SHARE lock and scan the entire relation, which is I/O-heavy on a multi-gigabyte index.
  • Baseline size numbers from your monitoring view so you can compare bloat measurements against a known-good build.

Ordered steps

From measurement to a reindex decision A left-to-right sequence of four boxes. Step one, create extension pgstattuple. Step two, run pgstatindex off-peak or on a replica because it takes an ACCESS SHARE lock and scans the whole relation. Step three, read dead_tuple_percent and free_percent against thresholds. From step three a decision branches down: if free_percent is below twenty percent, keep monitoring; if free_percent is above thirty percent or bloat exceeds two times the fresh-build size, run REINDEX INDEX CONCURRENTLY. Measure before you rebuild STEP 1 CREATE EXTENSION pgstattuple STEP 2 · OFF-PEAK pgstatindex() ACCESS SHARE · full scan STEP 3 Read dead & free % against thresholds free % < 20 Keep monitoring no action needed free % > 30 OR size > 2× fresh build REINDEX INDEX CONCURRENTLY
Measure with pgstatindex off-peak, then branch on free_percent and size growth before committing to a concurrent rebuild.

Step-by-step procedure

1. Install the extension

pgstattuple ships in contrib and installs per-database.

SQL
CREATE EXTENSION IF NOT EXISTS pgstattuple;

2. Measure heap-level bloat on the table

pgstattuple reports dead_tuple_percent and free_percent for the underlying table. Dead tuples here are the churned embedding rows that VACUUM has not yet reclaimed; they are the upstream cause of index bloat.

SQL
SELECT
    table_len,
    tuple_count,
    dead_tuple_count,
    round(dead_tuple_percent::numeric, 2) AS dead_pct,
    round(free_percent::numeric, 2)       AS free_pct
FROM pgstattuple('public.doc_chunks');

A high dead_pct on the table points at autovacuum falling behind, which is tuned in autovacuum tuning for pgvector tables.

3. Measure index-level fragmentation with pgstatindex

For B-tree-structured indexes pgstatindex reports leaf_fragmentation and avg_leaf_density. HNSW is not a B-tree, so use pgstattuple directly on the index relation to get its free_percent; use pgstatindex on any accompanying B-tree indexes (for example a metadata btree on doc_id).

SQL
-- HNSW index: free space is the reclaimable bloat signal
SELECT round(free_percent::numeric, 2) AS free_pct,
       pg_size_pretty(pg_relation_size('doc_chunks_embedding_hnsw')) AS idx_size
FROM pgstattuple('doc_chunks_embedding_hnsw');

-- B-tree companion index: leaf fragmentation is meaningful here
SELECT leaf_fragmentation, avg_leaf_density
FROM pgstatindex('doc_chunks_doc_id_btree');

4. Compare against a fresh-build baseline

Bloat is relative. Record the size of the index right after a clean REINDEX and compare live size to it. A live index more than roughly 2x its fresh-build size, or with free_percent above 30%, has reclaimable space worth a rebuild.

SQL
SELECT
    pg_size_pretty(pg_relation_size('doc_chunks_embedding_hnsw')) AS live_size,
    round(
        pg_relation_size('doc_chunks_embedding_hnsw')::numeric
        / nullif(:fresh_build_bytes, 0),
    2) AS bloat_ratio;

5. Reindex concurrently when thresholds are crossed

If the evidence says rebuild, do it without blocking writes. REINDEX INDEX CONCURRENTLY builds a replacement and swaps it in under a brief lock; the full scheduling pattern is in scheduling reindex concurrently without downtime.

SQL
REINDEX INDEX CONCURRENTLY doc_chunks_embedding_hnsw;

Parameter reference

Thresholds that turn a raw measurement into a rebuild decision. Treat them as starting points and calibrate to your churn.

Threshold Type Default reading Production recommendation Notes
dead_tuple_percent (table) percent reported by pgstattuple Investigate autovacuum above ~10% High dead tuples upstream drive index bloat; fix vacuum first.
free_percent (HNSW index) percent reported by pgstattuple Rebuild above ~30% Reclaimable space inside index pages; the primary HNSW bloat signal.
leaf_fragmentation (B-tree) percent reported by pgstatindex Rebuild above ~30% Only meaningful for B-tree companion indexes, not HNSW itself.
avg_leaf_density (B-tree) percent reported by pgstatindex Healthy 70–90% Low density means sparse leaves; complements fragmentation.
bloat_ratio (vs fresh build) ratio 1.0 at build time Rebuild above ~2.0 Compare live pg_relation_size to a recorded post-REINDEX size.
scan lock lock mode ACCESS SHARE Run off-peak or on a replica pgstattuple reads every page; heavy I/O on large indexes.

Verification

After a REINDEX INDEX CONCURRENTLY, re-run the same measurements and confirm free_percent and size dropped back toward the baseline.

SQL
SELECT round(free_percent::numeric, 2) AS free_pct,
       pg_size_pretty(pg_relation_size('doc_chunks_embedding_hnsw')) AS idx_size
FROM pgstattuple('doc_chunks_embedding_hnsw');

-- confirm no invalid index was left behind by a failed concurrent build
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'doc_chunks_embedding_hnsw'::regclass;

A low free_pct, a reduced size, and indisvalid = true together confirm the rebuild reclaimed the bloat cleanly.

Troubleshooting

  • pgstattuple runs for minutes and drives I/O wait up. That is expected: it scans the entire relation under ACCESS SHARE. Move the call to a read replica or a low-traffic window, and never wire it into a per-scrape dashboard; run it on demand or on a slow cron.
  • pgstatindex errors on the HNSW index. pgstatindex targets B-tree structure. Use pgstattuple() on the HNSW relation for its free_percent, and reserve pgstatindex for B-tree companion indexes.
  • free_percent is high but size never shrinks after VACUUM. Plain VACUUM marks space reusable inside the index but does not return it to the OS. Only REINDEX (or VACUUM FULL, which locks) actually reduces the on-disk file; use the concurrent form to avoid blocking.
  • A concurrent reindex left a leftover invalid index. A failed REINDEX INDEX CONCURRENTLY can leave an indisvalid = false shadow. Detect it with the pg_index query above and drop it with DROP INDEX CONCURRENTLY before retrying.
  • Bloat comes back within days. The rebuild treats the symptom, not the cause. Sustained high dead_tuple_percent means autovacuum is not keeping up on this high-churn table; retune per-table autovacuum before the next rebuild cycle.