A familiar pattern: a team decides to add RAG to its product, evaluates three vector databases, picks one, and six months later is running a second database with its own backups, security reviews, access controls and sync pipeline, all to store a few million embeddings next to data that already lives in Postgres.
Sometimes that's the right call. Often it isn't.
What pgvector gives you
pgvector is an open-source Postgres extension for vector similarity search, supporting exact search as well as approximate indexes such as HNSW and IVFFlat1. The strongest argument for it isn't performance. It's that vectors live beside your relational data, under the same transactions, backups, access controls and operational tooling.
That matters most for filtering. Real RAG queries are rarely "the nearest chunks"; they are "the nearest chunks this user may see, from this tenant, in this product line, updated this year". In Postgres those filters are ordinary SQL against data you already govern.
The filtering problem, and the 0.8 fix
Approximate indexes return a limited candidate set, and filters apply afterwards. With a selective filter, that can leave very few results: the Nile write-up of the release cites an example where a condition matching 10% of rows yields only about four results with default HNSW settings4.
pgvector 0.8.0, released in late 2024, added iterative index scans: if the first pass returns too few matches, the scan continues until it finds enough or hits a configured limit, with options for strict or relaxed ordering23. It also improved cost estimation so the planner makes better index choices when filtering2. The release notes add a useful reminder: where an ordinary index such as a B-tree can meet performance needs, it gives exact results2.
Filtered retrieval is where many RAG systems quietly fail: the right document exists but the user is told nothing relevant was found. Test with your real filters, not unfiltered benchmarks.
How I decide
| Favours pgvector | Favours a dedicated vector database |
|---|---|
| Vectors relate closely to existing relational data | Vector search is the main workload, not a feature |
| Rich filters, permissions and multi-tenancy in SQL | Very large collections or very high query rates |
| Moderate scale that fits your Postgres capacity | Search load would compete with transactional work |
| Team already runs Postgres well | Need for features Postgres lacks in your setup |
| Strong consistency between data and embeddings | Independent scaling of search from the database |
A middle path is common: start on pgvector, measure, and move only the search workload if it outgrows Postgres. If your operational database is MongoDB, the equivalent question applies there, as in hybrid search for RAG on MongoDB.
Practical rules for pgvector
- Benchmark with your filters and your data, measuring recall as well as latency.
- Enforce permissions in the query, ideally with row-level security, so retrieval can't return documents a user shouldn't see. The same principle applies to tenants, as in multi-tenant SaaS patterns.
- Separate search load with read replicas if it begins to affect transactional performance.
- Combine with keyword search; Postgres full-text search plus vectors is a reasonable hybrid for many products.
- Evaluate retrieval quality with a labelled set, as in the RAG to production checklist, before tuning indexes.
How I can help
I design retrieval architectures and data platforms for AI products, choosing the simplest stack that will scale. See my AI architecture work, or analyse your challenge and bring the result to a call.
- 1pgvectorpgvector, GitHub
- 2pgvector 0.8.0 releasedPostgreSQL · Nov 2024
- 3Iterative index scans (pgvector 0.8.0)pgEdge documentation
- 4