PostgreSQL pgvector
pgvector runs underneath most production Postgres RAG stacks in 2026. Default HNSW parameters silently drop recall to 67% on high-dimensional embeddings. This hub explains what to watch, how to measure recall honestly, and how to tune the index without dropping production queries.
Silent recall drop: HNSW returns wrong top-K results without any error or plan anomaly.
Immutable parameters: m and ef_construction cannot be ALTERed — a wrong choice means a full rebuild.
Build-time spikes: parallel HNSW builds eat maintenance_work_mem; watch pg_stat_progress_create_index.
Index bloat after heavy updates: pgvector indexes do not compact on update, only on REINDEX.
Dimension drift: a silent embedding-model swap can mix vector dimensions in the same column.
Returns the index name and WITH-options row (m, ef_construction). If the output is empty the table has no HNSW index; if reloptions is NULL the index was built with pgvector defaults.
SELECT i.indexname, c.reloptions
FROM pg_indexes i
JOIN pg_class c ON c.relname = i.indexname
JOIN pg_am am ON am.oid = c.relam
WHERE i.schemaname = 'public'
AND i.tablename = 'doc_embeddings'
AND am.amname = 'hnsw';HNSW builds are the main reason pgvector index operations feel slow. Parallel build lands in pgvector 0.7+; older versions show a single-phase progress bar and run an order of magnitude slower.
SELECT phase, tuples_done, tuples_total,
round(100.0 * tuples_done / NULLIF(tuples_total,0), 1) AS pct
FROM pg_stat_progress_create_index;Full working query lives in the blog post; run it quarterly or after major data changes. Below 0.85 recall means your RAG returns wrong context on roughly one in every seven queries.
-- Disable the HNSW path for the exact CTE with
-- SET LOCAL enable_indexscan = off; to force a sequential scan
-- and measure the ground truth before comparing to approximate.First-class pgvector inventory: vector columns, indexes, WITH options, live build progress in one dashboard.
HNSW Tuning Lab: one-click benchmark that sweeps 48 configurations and returns a zero-downtime migration SQL.
Recall drop alerts on scheduled re-benchmarks (Scale tier).
Works against self-hosted Postgres, Supabase, Neon, AWS RDS / Aurora, GCP AlloyDB / Cloud SQL, Azure Database.
The silent-killer bug, how it manifests, and the 30-minute zero-downtime fix.
Full benchmark data and recommended configs by use case.
Debugging story — tracing a bad chatbot back to HNSW defaults.
Hands-on setup, integration, and troubleshooting guides from the MonPG product docs.
Required for everything in MonPG. How it works, how to verify it's loaded, what we read from it.
The nine PostgreSQL extensions MonPG reads from, what each unlocks in the product, and which provider ships which.
Install pgstattuple to light up table/index bloat percentages on the Tables and Indexes pages.
Install hypopg to let Index Advisor simulate indexes without creating them — true "would this help?" answers.
Install pg_buffercache to inspect shared_buffers — which relations are hot, how much is dirty, and when to size up.
Enable pgaudit to send compliance-grade audit logs to MonPG Log Insights.
Enable auto_explain to automatically capture EXPLAIN output for every slow query. Essential for Query Advisor.
Install pg_wait_sampling for sampled wait-event histories on the Queries and Backends pages.
Benchmark 48 HNSW configurations against your real pgvector data in 10 minutes. Get the optimal m, ef_construction, and ef_search plus zero-downtime migration SQL.
Paste EXPLAIN ANALYZE output and get an incident-style read of the plan: planner estimate drift, loop explosions, disk spills, buffer pressure, and the evidence SQL to prove the fix.
Model a proposed PostgreSQL index as a production rollout: DDL shape, lock level, WAL pressure, write amplification, replica lag risk, validation SQL, and rollback criteria.
MonPG gives teams query history, alerts, index guidance, vacuum visibility, replication signals, and cloud PostgreSQL monitoring in one place.
Start free