Building a pg_stat_user_indexes Dashboard for Vector Search

This page shows how to assemble a reusable monitoring layer over pg_stat_user_indexes and pg_statio_user_indexes that surfaces scan counts, tuple throughput, on-disk size, buffer cache hit ratio, and unused-index detection for HNSW and IVFFlat indexes. It scopes the problem narrowly: given a running pgvector database, how to expose a least-privilege monitoring VIEW and a read-only role that a dashboard can poll every scrape interval without touching table data or holding heavy locks.

Up: Monitoring Vector Indexes with pg_stat Views

Vector indexes are expensive to build and easy to forget. An HNSW index on a 1536-dimension column can occupy several gigabytes and take minutes to construct, so a stale or never-scanned index is a real cost that only shows up if you measure it. PostgreSQL already tracks per-index access statistics in the cumulative statistics system; the work here is joining the right catalog views, filtering to your vector tables, and packaging the result so a Grafana or psql-based dashboard reads one clean surface. The same counters feed the broader monitoring vector indexes with pg_stat views practice this procedure belongs to.

Prerequisites #

• PostgreSQL 15+ with track_counts = on (the default) so pg_stat_user_indexes accumulates scan data.
• The pgvector extension installed and at least one HNSW or IVFFlat index in place; algorithm choice is covered in HNSW vs IVFFlat algorithm selection.
• A schema to own the monitoring objects (this page uses monitoring) and the pg_monitor predefined role available for the dashboard login.
• psql or any client that can run catalog queries. No superuser is required for the queries themselves, only for the initial GRANT.
• An understanding that these counters are cumulative since the last pg_stat_reset; interpreting them as rates is discussed in Troubleshooting.

Step-by-step procedure #

1. Inspect the raw per-index counters #

Before wrapping anything in a view, confirm the statistics are populating. pg_stat_user_indexes gives you idx_scan, idx_tup_read, and idx_tup_fetch per index; join pg_class to add size.

SELECT
    s.indexrelname                          AS index_name,
    s.relname                               AS table_name,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.schemaname = 'public'
ORDER BY pg_relation_size(s.indexrelid) DESC;

For a vector index, idx_tup_read climbing far faster than idx_tup_fetch is normal: approximate search reads many candidate tuples in the graph or lists and returns only the top k.

2. Add the buffer cache hit ratio #

pg_statio_user_indexes exposes idx_blks_hit and idx_blks_read. The hit ratio tells you whether the index’s hot pages live in shared_buffers or are being pulled from disk — the single most important signal for HNSW latency, because graph traversal is random-access and a cold index thrashes the buffer pool.

SELECT
    io.indexrelname AS index_name,
    io.idx_blks_hit,
    io.idx_blks_read,
    round(
        100.0 * io.idx_blks_hit
        / nullif(io.idx_blks_hit + io.idx_blks_read, 0),
    2) AS cache_hit_pct
FROM pg_statio_user_indexes io
WHERE io.schemaname = 'public'
ORDER BY cache_hit_pct NULLS FIRST;

A cache_hit_pct below roughly 95% on a frequently queried vector index means the working set does not fit in RAM; size the footprint with pgvector storage overhead analysis before adding memory.

3. Create the least-privilege monitoring view #

Bundle scan counters, size, cache ratio, and an unused flag into one VIEW in a dedicated schema. Building it as a view means the dashboard never issues ad-hoc catalog joins and you can evolve the SQL centrally. Only indexes on vector/halfvec columns are surfaced by joining pg_attribute and the pgvector type name.

CREATE SCHEMA IF NOT EXISTS monitoring;

CREATE OR REPLACE VIEW monitoring.vector_index_stats AS
SELECT
    s.schemaname,
    s.relname                          AS table_name,
    s.indexrelname                     AS index_name,
    am.amname                          AS index_method,   -- hnsw / ivfflat
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch,
    pg_relation_size(s.indexrelid)     AS index_bytes,
    round(
        100.0 * io.idx_blks_hit
        / nullif(io.idx_blks_hit + io.idx_blks_read, 0),
    2)                                 AS cache_hit_pct,
    (s.idx_scan = 0)                   AS is_unused
FROM pg_stat_user_indexes s
JOIN pg_statio_user_indexes io ON io.indexrelid = s.indexrelid
JOIN pg_class c   ON c.oid = s.indexrelid
JOIN pg_am    am  ON am.oid = c.relam
WHERE am.amname IN ('hnsw', 'ivfflat');

4. Grant read-only access to a dashboard role #

Give the scraping login only pg_monitor membership and SELECT on the view — never table access. pg_monitor grants visibility into statistics without any data-plane privilege.

CREATE ROLE vector_dashboard LOGIN PASSWORD 'set-in-secret-manager';
GRANT pg_monitor TO vector_dashboard;
GRANT USAGE ON SCHEMA monitoring TO vector_dashboard;
GRANT SELECT ON monitoring.vector_index_stats TO vector_dashboard;

The broader boundary model for restricting who can read vector data is laid out in securing pgvector tables with row-level security.

5. Poll the view from the dashboard #

Point the scraper at the view and interpret each column per index method. HNSW indexes should show steady idx_scan growth and high cache_hit_pct; IVFFlat indexes with a low idx_scan-to-idx_tup_read ratio often signal a lists/probes mismatch, tuned in tuning IVFFlat lists for high-throughput similarity search.

SELECT index_name, index_method, idx_scan, is_unused,
       pg_size_pretty(index_bytes) AS size, cache_hit_pct
FROM monitoring.vector_index_stats
ORDER BY is_unused DESC, index_bytes DESC;

Parameter reference #

Each row is a pg_stat/pg_statio column the dashboard surfaces and how to read it for vector indexes.

Verification #

Query the view and confirm every expected vector index appears with sane counters. A stats_reset timestamp anchors how far back the numbers reach.

SELECT index_name, idx_scan, cache_hit_pct, is_unused
FROM monitoring.vector_index_stats;

-- when were these counters last zeroed?
SELECT stats_reset FROM pg_stat_database
WHERE datname = current_database();

Confirm the least-privilege role cannot read table data:

SET ROLE vector_dashboard;
SELECT * FROM monitoring.vector_index_stats LIMIT 1;   -- succeeds
SELECT * FROM public.doc_chunks LIMIT 1;               -- permission denied
RESET ROLE;

If the view returns rows and the direct table select is denied, the dashboard has exactly the surface it needs and nothing more.

Troubleshooting #

• All counters suddenly read near zero. Someone (or a monitoring job) called pg_stat_reset() or the server restarted with a crash that discarded stats. Check pg_stat_database.stats_reset; treat is_unused as meaningless until a full traffic cycle has re-accumulated after the reset timestamp.
• A busy index still shows idx_scan = 0. You are querying a replica whose statistics are local to that node, or the queries are hitting a different index via an implicit plan. Confirm on the primary with pg_stat_user_indexes, and check EXPLAIN to see which index the planner actually chose.
• cache_hit_pct looks impossibly high right after a restart. The counters are cumulative but small; a handful of warm queries can read 100%. Let the index serve real traffic before trusting the ratio, and compute rates by differencing two snapshots rather than reading the absolute values.
• You cannot tell throughput from the raw numbers. idx_scan is a monotonic counter, not a rate. Sample the view on an interval and subtract consecutive readings (Δidx_scan / Δt) in the dashboard; a single reading only tells you lifetime totals since the last reset.
• Replica and primary disagree. Index usage statistics are not replicated — each node counts its own scans. A read-replica serving search traffic will show high idx_scan while the primary shows little; monitor every node that answers vector queries, not just the writer.

Related #

• Detecting HNSW index bloat with pgstattuple — go deeper than size once an index looks stale
• Autovacuum tuning for pgvector tables — why dead tuples inflate the sizes this dashboard tracks
• Tuning IVFFlat lists for high-throughput similarity search — acting on a low scan-to-read ratio
• Up: Monitoring Vector Indexes with pg_stat Views