Monitoring Vector Indexes with pg_stat Views

A vector index fails quietly. Nobody gets an error when an HNSW index stops being used — the query still returns rows, just by falling back to a sequential scan that reads every vector in the table, and the only symptom is p99 latency creeping from 8 ms to 800 ms over a week. PostgreSQL already exposes exactly the signals needed to catch this before users do, in the cumulative statistics views: pg_stat_user_indexes tells you whether the index is scanned at all, pg_statio_user_indexes tells you whether it is resident in memory, pg_stat_user_tables tells you whether autovacuum is keeping the heap clean, pg_stat_progress_create_index tracks in-flight builds, and the pgstattuple extension measures physical bloat when you are willing to pay for a full-relation scan. This page maps each surface to the vector-specific failure it detects, gives the diagnostic SQL, and shows how to wire the signals into alerts. It sits under Monitoring, Observability & Operations and assumes a table items(id bigint, embedding vector(1536)) with an HNSW index named items_embedding_hnsw.

Up: Monitoring, Observability & Operations

Architectural Divergence & Trade-offs #

The statistics surfaces are not interchangeable. They differ in what they measure, what they cost to read, and how their numbers behave over time, and picking the wrong one either misses the problem or drags a lock across a production table.

Cumulative counter views — pg_stat_user_indexes, pg_statio_user_indexes, and pg_stat_user_tables — are effectively free. They are in-memory counters maintained by the statistics collector (the shared-memory stats subsystem since PostgreSQL 15), so a SELECT against them is a catalog lookup with no relation access. They are the right default for continuous polling. Their catch is that every number is a monotonic total since the last statistics reset, not a rate. A idx_scan of 4,201,338 tells you nothing on its own; the delta between two samples is the signal. Any dashboard built on these views must difference consecutive scrapes.

The progress view pg_stat_progress_create_index is also cheap but ephemeral: it has exactly one row per in-flight CREATE INDEX or REINDEX on the backend running it, and vanishes the instant the build finishes. It answers one question — how far along is this build — and is useless for steady-state health.

Physical inspection via pgstattuple is the expensive tier. pgstattuple('items_embedding_hnsw') reads every page of the relation to compute the live-tuple percentage, dead-tuple bytes, and free space. On a multi-gigabyte HNSW index that is minutes of I/O and buffer-cache pollution, so it must be sampled deliberately, off-peak, and never on a polling loop. Note that the companion function pgstatindex() is B-tree-only; it returns an error on HNSW and IVFFlat indexes, so physical bloat on a vector index is measured with pgstattuple, not pgstatindex.

Parameter Space & Diagnostic Workflow #

Every counter view is a total, so the operational parameters are the thresholds on rates and ratios you derive from them, not the raw values. The table below is the set worth alerting on for a vector workload.

The workflow follows the diagram top to bottom, cheapest signal first. Start by confirming the index is used at all, because a flat idx_scan invalidates every downstream measurement — you cannot have a cache-residency problem on an index nobody reads.

SELECT relname,
       indexrelname,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  indexrelname = 'items_embedding_hnsw';

Sample this twice a minute apart. If idx_scan does not move while similarity queries are running, the planner is not choosing the index — that is the single most important signal on this page, covered in depth below. PostgreSQL 16 adds a last_idx_scan timestamp to this view that removes the need for differencing to answer “was it ever used recently”; on 15 you difference two samples. A wide and growing gap between idx_tup_read and idx_tup_fetch on an index that is scanned points at pointer bloat: the index is returning entries that resolve to dead heap tuples the visibility check then discards, which is the accumulation autovacuum is supposed to reclaim.

The key signal: a flat idx_scan means the index is not being used #

When idx_scan stays flat while the application is issuing ORDER BY embedding <=> $1 LIMIT k queries, PostgreSQL has decided a sequential scan is cheaper than the ANN index and is reading and sorting the entire table on every query. There are two dominant root causes, and they are distinguishable.

The first is an operator-class or metric mismatch. An HNSW index is built for exactly one distance operator — vector_cosine_ops indexes the <=> operator, vector_l2_ops indexes <->, and vector_ip_ops indexes <#>. A query that orders by <-> cannot use a cosine index at all; the planner silently ignores it and scans. This is the same class of defect catalogued in index validation and error categorization, and it is why the operator in the query and the operator class in CREATE INDEX must be chosen together — the trade-offs are laid out in choosing between HNSW and IVFFlat. Confirm the pairing:

SELECT i.relname                              AS index_name,
       am.amname                              AS index_method,
       pg_get_indexdef(i.oid)                 AS definition
FROM   pg_class i
JOIN   pg_am    am ON am.oid = i.relam
WHERE  i.relname = 'items_embedding_hnsw';

The definition string names the operator class (for example USING hnsw (embedding vector_cosine_ops)). If it reads vector_cosine_ops but your queries order by <->, the index will never be scanned no matter how the planner is tuned.

The second cause is a planner cost miss: the operator class matches, but a WHERE clause, a LIMIT the planner thinks is large, stale table statistics, or a mis-set enable_seqscan/cost GUC leads it to price the scan below the index. Diagnose by forcing the comparison and reading the plan:

SET LOCAL enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM   items
ORDER  BY embedding <=> '[0.01, 0.02, ...]'::vector
LIMIT  10;

If the plan flips to Index Scan using items_embedding_hnsw and gets dramatically faster with enable_seqscan = off, the index is healthy and the planner was simply mispricing it — usually fixed by running ANALYZE items so row estimates are current, or by ensuring the query has no filter that defeats the ANN path.

Cache residency: HNSW must stay resident #

HNSW is a graph. Answering a query walks from an entry point across many nodes, and each hop dereferences a vector that lives on some index page. If those pages are not in shared_buffers, every hop is a random disk read, and a single similarity query can trigger dozens of them. The index therefore has to stay resident in memory to deliver its latency promise. pg_statio_user_indexes measures exactly this through the buffer-cache hit ratio:

SELECT indexrelname,
       idx_blks_hit,
       idx_blks_read,
       round(
         idx_blks_hit::numeric
         / nullif(idx_blks_hit + idx_blks_read, 0),
         4) AS cache_hit_ratio
FROM   pg_statio_user_indexes
WHERE  indexrelname = 'items_embedding_hnsw';

For a latency-sensitive HNSW index you want a hit ratio above 0.99. A ratio that sits at 0.90 or lower means roughly one page in ten is coming from disk, and because those reads are on the critical path of the graph walk they show up directly as tail latency. The fixes are to grow shared_buffers until the index working set fits, pin the workload to a replica whose cache is dedicated to search, or warm the cache after a restart. To reason about whether the index even can fit, size it first — the on-disk footprint of an HNSW graph at a given m and dimension is worked out in pgvector storage overhead analysis. If the index is larger than the memory you can give it, no ratio tuning will help; that is a capacity decision, not a monitoring one.

Step-by-Step Implementation #

The following builds a repeatable monitoring harness: an extension, a snapshot table to hold rate baselines, and the queries that turn counters into alertable numbers.

Step 1 — Install pgstattuple for the expensive tier. It is a contrib extension and only needed for physical inspection.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Step 2 — Snapshot the cheap counters on a schedule. Because the views are cumulative, persist samples so you can difference them. A small table plus a cron-driven insert is enough.

CREATE TABLE IF NOT EXISTS index_stat_history (
    captured_at   timestamptz NOT NULL DEFAULT now(),
    indexrelname  text        NOT NULL,
    idx_scan      bigint      NOT NULL,
    idx_tup_read  bigint      NOT NULL,
    idx_tup_fetch bigint      NOT NULL,
    idx_blks_hit  bigint      NOT NULL,
    idx_blks_read bigint      NOT NULL
);

INSERT INTO index_stat_history
     (indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
      idx_blks_hit, idx_blks_read)
SELECT s.indexrelname, s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
       io.idx_blks_hit, io.idx_blks_read
FROM   pg_stat_user_indexes  s
JOIN   pg_statio_user_indexes io USING (indexrelid)
WHERE  s.indexrelname = 'items_embedding_hnsw';

Step 3 — Derive the scan rate from consecutive snapshots. The delta over the elapsed interval is the per-second scan rate; a zero rate during business hours is the alert.

SELECT indexrelname,
       idx_scan - lag(idx_scan) OVER w                       AS scans_delta,
       EXTRACT(epoch FROM captured_at
                        - lag(captured_at) OVER w)            AS interval_s
FROM   index_stat_history
WHERE  indexrelname = 'items_embedding_hnsw'
WINDOW w AS (PARTITION BY indexrelname ORDER BY captured_at)
ORDER  BY captured_at DESC
LIMIT  5;

Step 4 — Watch dead tuples and autovacuum on the base table. The heap behind a vector index bloats like any other, and a starved autovacuum both slows scans and inflates the index-to-heap pointer gap.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric
             / nullif(n_live_tup + n_dead_tup, 0), 3) AS dead_frac,
       last_autovacuum,
       autovacuum_count
FROM   pg_stat_user_tables
WHERE  relname = 'items';

Step 5 — Track an in-flight build when one is running. During a CREATE INDEX or REINDEX, the progress view reports the phase and block counts. This is the same view used to supervise online rebuilds in zero-downtime index operations.

SELECT p.phase,
       p.blocks_done,
       p.blocks_total,
       round(100.0 * p.blocks_done
             / nullif(p.blocks_total, 0), 1) AS pct_done,
       p.tuples_done,
       p.tuples_total
FROM   pg_stat_progress_create_index p
JOIN   pg_class c ON c.oid = p.relid
WHERE  c.relname = 'items';

Step 6 — Sample physical bloat off-peak only. Gate this behind a maintenance window; it reads the whole index.

SELECT * FROM pgstattuple('items_embedding_hnsw');
-- dead_tuple_percent and free_percent are the numbers that decide a REINDEX

Validation & Recall Testing #

Counters can be misread; the authoritative check that the index is doing its job is the query plan. EXPLAIN (ANALYZE, BUFFERS) shows both which access path was chosen and how many buffers it touched, which cross-validates the pg_statio hit ratio from inside a real query.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM   items
ORDER  BY embedding <=> '[0.01, 0.02, ...]'::vector
LIMIT  10;

A healthy result reads Index Scan using items_embedding_hnsw on items with a low Buffers: shared hit=... and a near-zero read=.... A Seq Scan on items followed by a Sort is the fallback the flat-idx_scan alert is meant to catch — the query is now exact, not approximate, and its cost grows with the table. The BUFFERS line is the plan-level echo of the cache hit ratio: a large read= value here is the same disk paging that shows up as a low idx_blks_hit ratio in pg_statio_user_indexes.

Confirm the ANN tuning knobs are actually taking effect by varying them within a session and watching latency and buffer counts move. For HNSW the runtime knob is hnsw.ef_search; for IVFFlat it is ivfflat.probes.

SET hnsw.ef_search = 40;   -- default; lower recall, fewer hops
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM items
ORDER BY embedding <=> '[...]'::vector LIMIT 10;

SET hnsw.ef_search = 200;  -- higher recall, more hops and buffers
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM items
ORDER BY embedding <=> '[...]'::vector LIMIT 10;

If the plan does not respond to ef_search at all — identical timing and buffer counts — the query is not using the HNSW index in the first place, which loops you back to the operator-class check. Rising shared hit with higher ef_search is the expected signature of the graph walk visiting more nodes for better recall. Measuring the recall those settings actually deliver against a ground-truth set is a separate discipline, automated in recall regression testing in CI.

Failure Modes & Gotchas #

• Reading counters as rates. idx_scan is a lifetime total. A dashboard that plots the raw value shows a smooth ever-rising line and hides the moment the rate dropped to zero. Always difference consecutive samples; the derivative is the health signal, not the level.
• A statistics reset silently zeroes your baseline. pg_stat_reset(), a major-version upgrade, or a crash recovery resets all cumulative counters to zero. A differencing query that spans the reset produces a large negative delta — filter those out, and treat stats_reset in pg_stat_database as the epoch for any rate.
• Sampling error from coarse intervals. Polling every five minutes can miss a scan-rate collapse that self-heals, or average away a bloat spike. Match sample frequency to how fast the signal you care about moves; scan rate wants sub-minute granularity, physical bloat is fine hourly at most.
• pgstattuple locking and cost. It takes an ACCESS SHARE lock and reads every page. On a large HNSW index that is a long, cache-polluting scan that competes with live queries for buffers. Never put it on a polling loop; run it in a maintenance window and store the result.
• Autovacuum starvation inflating scan cost. When autovacuum cannot keep up, n_dead_tup climbs, the heap grows, and every index scan does more visibility-check work fetching tuples that turn out to be dead — visible as idx_tup_read pulling away from idx_tup_fetch and as rising query latency with unchanged idx_scan. The fix is on the table’s autovacuum settings, not the index.
• pgstatindex on a vector index errors out. It only understands B-tree. Calling it on HNSW or IVFFlat raises an error; use pgstattuple for physical numbers on those relations.

Monitoring & Alerting Hooks #

Export the derived rates and ratios — not the raw counters — as gauges, and let the metrics backend do the differencing where it can. The three alerts that catch the failures on this page are: scan rate collapsed, cache hit ratio dropped, dead-tuple fraction climbing.

-- Scan-rate collapse: emit as a gauge, alert when it hits 0 in business hours
SELECT indexrelname,
       idx_scan   AS idx_scan_total,          -- counter; rate() in Prometheus
       idx_tup_read,
       idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  indexrelname = 'items_embedding_hnsw';

With a Postgres exporter these become counters, and the alert is expressed as a rate over a window — for example rate(pg_stat_user_indexes_idx_scan{indexrelname="items_embedding_hnsw"}[10m]) == 0 during hours when query traffic is non-zero. The exporter-side plumbing that publishes these is covered in Prometheus metrics for pgvector. Emit the cache hit ratio as a pre-computed gauge so the alert is a simple threshold:

SELECT round(
         idx_blks_hit::numeric
         / nullif(idx_blks_hit + idx_blks_read, 0), 4)
         AS cache_hit_ratio
FROM   pg_statio_user_indexes
WHERE  indexrelname = 'items_embedding_hnsw';
-- alert: cache_hit_ratio < 0.95 for 15m

And page on dead-tuple fraction so autovacuum starvation is caught before it degrades scans:

SELECT round(n_dead_tup::numeric
             / nullif(n_live_tup + n_dead_tup, 0), 3) AS dead_frac,
       EXTRACT(epoch FROM now() - last_autovacuum)    AS autovac_age_s
FROM   pg_stat_user_tables
WHERE  relname = 'items';
-- alert: dead_frac > 0.20 AND autovac_age_s > 3600

Finally, wire the progress view into your rebuild tooling so an online REINDEX surfaces its pct_done; a build whose blocks_done stops advancing is blocked on a lock and needs the preflight described in zero-downtime index operations.

FAQ #

Related #

• Prometheus metrics for pgvector — publishing these counter deltas and ratios as scrapeable metrics
• Recall regression testing in CI — turning the plan-level validation here into an automated gate
• Zero-downtime index operations — supervising rebuilds through pg_stat_progress_create_index
• Index validation and error categorization — the operator-class defects behind a flat idx_scan
• pgvector storage overhead analysis — sizing the index so a high cache hit ratio is even attainable
• Up: Monitoring, Observability & Operations