Implement pgvector index maintenance with VACUUM and reindexing schedules

Introduction

In production, pgvector indexes accumulate dead tuples and bloat as embeddings are updated or deleted, silently degrading nearest-neighbor recall without triggering obvious errors. IVFFlat and HNSW indexes cannot self-compact the way B-tree indexes do, so unaddressed bloat reduces search quality while your application keeps returning results—just progressively worse ones. By the end of this lesson, you will be able to query PostgreSQL system views to measure index health, schedule VACUUM operations based on dead-tuple thresholds, and trigger REINDEX CONCURRENTLY to restore recall when bloat exceeds safe limits.

Key Terminology

• Dead tuples — rows that have been updated or deleted in PostgreSQL but whose storage space has not yet been reclaimed; they accumulate in the heap and inflate the n_dead_tup counter in pg_stat_user_tables, which PgvectorHealthMonitor.check_health reads to compute dead_tuple_ratio.
• Dead-tuple ratio — the fraction of dead rows relative to live rows (n_dead_tup / n_live_tup), exposed as the DEAD_TUPLE_RATIO Prometheus gauge and used as the primary threshold signal for scheduling VACUUM on an embeddings table; a value above roughly 0.20 indicates maintenance is overdue.
• Index bloat — the accumulation of unused or stale space inside an IVFFlat or HNSW index caused by dead tuples that VACUUM cannot fully reclaim from the index structure itself; tracked by the BLOAT_RATIO gauge and resolved only by a full REINDEX CONCURRENTLY.
• REINDEX CONCURRENTLY — a PostgreSQL DDL operation that rebuilds an index from scratch in the background without acquiring a table lock, allowing reads and writes to continue; triggered when bloat_ratio exceeds safe limits and the index must be fully restored rather than incrementally cleaned.
• pg_stat_user_tables — a PostgreSQL system catalog view that exposes per-table tuple statistics including n_live_tup, n_dead_tup, last_vacuum, and last_autovacuum; PgvectorHealthMonitor queries this view to populate the dead_tuple_ratio and seconds_since_vacuum fields of IndexHealthReport.
• IndexHealthReport — a Python dataclass defined in pgvector_health.py that bundles all per-table health measurements—live/dead tuple counts, dead-tuple ratio, index size, table size, and time since last VACUUM—into a single structure a maintenance scheduler can inspect and act on.

Concepts

Why pgvector indexes degrade silently

PostgreSQL's B-tree indexes can reuse pages in-place as rows are updated and deleted, so bloat is self-limiting in most transactional workloads. IVFFlat and HNSW indexes do not share this property. Both index types organize embedding vectors into fixed structures—inverted file lists or hierarchical navigable graphs—that do not automatically reclaim space from deleted or updated rows. Dead tuples accumulate in the underlying heap and inside the index, widening the gap between what the index "thinks" is there and what is actually live.

The failure mode is subtle: nearest-neighbor queries continue returning results, but recall degrades as the index scans dead-entry regions and misses live neighbors. There is no error, no warning, and no obvious metric spike unless you are explicitly measuring dead-tuple ratio and bloat. This is why the lesson centers on proactive measurement rather than reactive incident response.

Dead-tuple ratio as a scheduling signal

Because pgvector indexes cannot self-compact, maintenance must be driven by an observable threshold. The dead-tuple ratio—n_dead_tup / n_live_tup from pg_stat_user_tables—serves as that signal. A ratio near zero means the table is healthy; a ratio above roughly 0.20 on an embeddings table means dead rows are materially affecting index quality and VACUUM should run.

The DEAD_TUPLE_RATIO Prometheus gauge makes this threshold operationally visible: you can alert on it, graph it over time, and correlate spikes with write-heavy workloads or failed autovacuum runs. The companion LAST_VACUUM gauge surfaces a second failure mode—schedule drift, where the ratio stays moderate but VACUUM hasn't run in so long that a burst write event could push it over the threshold before the next scheduled window (see Code Walkthrough).

Two-tier maintenance: VACUUM versus REINDEX CONCURRENTLY

Not all bloat requires the same response. VACUUM reclaims dead heap rows and updates visibility maps, which allows the index to skip dead entries on subsequent scans. It is fast, non-blocking, and appropriate when dead-tuple ratio is elevated but the index structure itself is still sound.

When bloat has progressed to the point that the index contains significant stale structure—not just dead heap pointers but misallocated internal pages—VACUUM alone cannot restore recall. REINDEX CONCURRENTLY rebuilds the entire index from the current live heap, resetting bloat to zero, but at the cost of a longer runtime and additional I/O. The two operations form a hierarchy: schedule VACUUM frequently as a low-cost first response, and reserve REINDEX CONCURRENTLY for cases where the BLOAT_RATIO gauge exceeds a higher threshold.

Reading health from system catalogs

PgvectorHealthMonitor.check_health issues two parameterized queries. The first targets pg_stat_user_tables for tuple counts and vacuum timestamps; the second targets pg_index joined with pg_relation_size for index and table size in bytes. Splitting the queries keeps each one focused and avoids the join complexity that arises when combining tuple statistics with index metadata in a single pass.

The COALESCE(last_vacuum, last_autovacuum) expression in the first query is intentional: in production, most tables are maintained by PostgreSQL's autovacuum daemon rather than manually scheduled jobs, and treating a recent autovacuum as evidence of freshness keeps the seconds_since_vacuum field accurate regardless of which agent ran last. PgvectorHealthMonitor therefore surfaces a unified health picture that works whether your maintenance is automatic, scheduled, or mixed (see Code Walkthrough).

Code Walkthrough

Now that you understand dead-tuple ratio, index bloat, and the VACUUM/REINDEX CONCURRENTLY lifecycle, the implementation wires those concepts into two concrete components: a Prometheus-instrumented health reporter and an asyncpg-backed monitor that queries PostgreSQL system catalogs.

Health metrics and the IndexHealthReport dataclass

The block below defines four Prometheus Gauge metrics—tracking index size, dead-tuple ratio, bloat ratio, and time since last VACUUM—plus an IndexHealthReport dataclass that bundles all per-table measurements into a single structure the maintenance scheduler can act on:

Code snippetpython
1# src/vector_index_ops/pgvector_health.py
2from dataclasses import dataclass
3from typing import Optional
4import asyncpg
5from prometheus_client import Gauge
6
7INDEX_SIZE = Gauge(
8    "pgvector_index_size_bytes",
9    "Size of pgvector indexes in bytes",
10    ["index_name"],
11)
12DEAD_TUPLE_RATIO = Gauge(
13    "pgvector_dead_tuple_ratio",
14    "Ratio of dead tuples to live tuples",
15    ["table_name"],
16)
17BLOAT_RATIO = Gauge(
18    "pgvector_index_bloat_ratio",
19    "Estimated bloat ratio for pgvector indexes",
20    ["index_name"],
21)
22LAST_VACUUM = Gauge(
23    "pgvector_last_vacuum_seconds_ago",
24    "Seconds since last VACUUM on embedding tables",
25    ["table_name"],
26)
27
28@dataclass
29class IndexHealthReport:
30    table_name: str
31    index_name: str
32    live_tuples: int
33    dead_tuples: int
34    dead_tuple_ratio: float
35    index_size_bytes: int
36    table_size_bytes: int
37    last_vacuum_at: Optional[str]
38    seconds_since_vacuum: Optional[float]

DEAD_TUPLE_RATIO is the primary scheduling signal: once it exceeds roughly 0.20 on an embeddings table, VACUUM is overdue. LAST_VACUUM surfaces schedule drift—tables that haven't been vacuumed recently despite high write throughput need autovacuum tuning or explicit scheduling.

PgvectorHealthMonitor

The monitor wraps an asyncpg.Pool and reads from pg_stat_user_tables and pg_index to populate an IndexHealthReport:

Code snippetpython
1# src/vector_index_ops/pgvector_health.py (continued)
2class PgvectorHealthMonitor:
3    def __init__(self, pool: asyncpg.Pool) -> None:
4        self._pool = pool
5
6    async def check_health(
7        self, table_name: str = "embeddings"
8    ) -> IndexHealthReport:
9        stats = await self._pool.fetchrow(
10            """
11            SELECT
12                n_live_tup,
13                n_dead_tup,
14                CASE WHEN n_live_tup > 0
15                    THEN n_dead_tup::float / n_live_tup
16                    ELSE 0
17                END AS dead_ratio,
18                last_vacuum,
19                last_autovacuum,
20                EXTRACT(EPOCH FROM (
21                    NOW() - COALESCE(last_vacuum, last_autovacuum)
22                )) AS seconds_since_vacuum
23            FROM pg_stat_user_tables
24            WHERE relname = $1
25            """,
26            table_name,
27        )
28        index_info = await self._pool.fetchrow(
29            """
30            SELECT
31                indexrelid::regclass AS index_name,
32                pg_relation_size(indexrelid) AS index_size,
33                pg_total_relation_size($1::regclass) AS table_size
34            FROM pg_index
35            WHERE indrelid = $1::regclass
36              AND indisvalid
37            ORDER BY pg_relation_size(indexrelid) DESC
38            LIMIT 1
39            """,
40            table_name,
41        )
42        return IndexHealthReport(
43            table_name=table_name,
44            index_name=str(index_info["index_name"]),
45            live_tuples=stats["n_live_tup"],
46            dead_tuples=stats["n_dead_tup"],
47            dead_tuple_ratio=float(stats["dead_ratio"]),
48            index_size_bytes=index_info["index_size"],
49            table_size_bytes=index_info["table_size"],
50            last_vacuum_at=str(
51                stats["last_vacuum"] or stats["last_autovacuum"]
52            ),
53            seconds_since_vacuum=(
54                float(stats["seconds_since_vacuum"])
55                if stats["seconds_since_vacuum"] is not None
56                else None
57            ),
58        )

check_health issues two parameterized queries—one for tuple statistics, one for index sizing—then combines them into the IndexHealthReport your scheduler will inspect. The COALESCE(last_vacuum, last_autovacuum) expression handles tables maintained by autovacuum rather than manual scheduling, keeping the time-since-vacuum metric accurate regardless of which agent performed the last cleanup.

Confirm that calling await monitor.check_health("embeddings") against a PostgreSQL instance with a pgvector-backed embeddings table returns an IndexHealthReport with a non-negative dead_tuple_ratio and a positive index_size_bytes.

Do's and Don'ts

Having walked through the material above, the following Do's and Don'ts distill it into practice.

Do's

1. ✓Do use dead_tuple_ratio from pg_stat_user_tables as your primary VACUUM trigger — IVFFlat and HNSW indexes cannot self-compact the way B-tree indexes do, so once the ratio crosses roughly 0.20 on an embeddings table, nearest-neighbor recall degrades silently while the application keeps returning results.
2. ✓Do use COALESCE(last_vacuum, last_autovacuum) when computing seconds_since_vacuum in PgvectorHealthMonitor.check_health — tables maintained exclusively by autovacuum leave last_vacuum NULL; omitting the COALESCE causes the LAST_VACUUM Prometheus gauge to report None even for recently cleaned tables, masking real schedule drift.
3. ✓Do expose DEAD_TUPLE_RATIO and LAST_VACUUM as separate, table-labeled Prometheus Gauge metrics — keeping the dead-tuple ratio (the immediate VACUUM trigger) distinct from time-since-vacuum (the autovacuum-tuning signal) lets the maintenance scheduler distinguish between "run VACUUM now" and "the autovacuum schedule needs adjustment."

Don'ts

1. ✗Don't treat a steadily rising dead_tuple_ratio as benign because embeddings queries keep returning results — bloat in IVFFlat and HNSW indexes erodes recall without surfacing errors or slow-query alerts, so the only early signal is the ratio in pg_stat_user_tables, not application-level symptoms.
2. ✗Don't query pg_index alone to assess index health — pg_index provides index_size_bytes and the indisvalid flag needed to skip in-progress rebuilds, but it contains no tuple statistics; without the companion pg_stat_user_tables query, IndexHealthReport.dead_tuple_ratio is always zero and the scheduler has no signal on which to act.
3. ✗Don't run plain REINDEX instead of REINDEX CONCURRENTLY on a production embeddings table — plain REINDEX acquires an exclusive lock that blocks all nearest-neighbor reads for the full rebuild duration; REINDEX CONCURRENTLY rebuilds the pgvector index without interrupting live query traffic.