pgvector adds vector columns and similarity search to Postgres. It is the default choice when you already use Postgres, need transactional consistency and want to filter by tenant or permission in the same query.
Key takeaways
- Store embeddings in a
vector(n)column and match the model's dimensions. - Use cosine, inner product or L2 distance consistently with how the embedding was trained.
- HNSW is usually the best default for low-latency search; IVFFlat needs tuning and enough data.
- Measure quality on a fixed evaluation set before you optimise the prompt.
What matters in practice
- Filtering by tenant or permission is part of the query, not an afterthought.
- Index build time and memory grow with the dataset; plan for maintenance.
How to implement it
- Enable the extension. Create the
vectorextension in the database. - Create the table. Add the vector column and metadata columns for filtering.
- Choose a distance. Match the operator to the embedding model and normalise if needed.
- Create an index. Start with HNSW and tune
mandef_construction. - Query with filters. Filter by tenant and permission, then order by distance.
- Monitor. Track recall, latency and index size; re-embed when the model changes.
A working example
create extension if not exists vector;
create table documents (
id bigserial primary key,
tenant_id uuid not null,
content text not null,
embedding vector(1536) not null
);
create index on documents using hnsw (embedding vector_cosine_ops);Cost, latency and reliability
| Concern | What drives it | What to do |
|---|---|---|
| Slow query | No index or too many rows scanned | Add HNSW and filter before ordering |
| Poor results | Wrong distance or embedding model | Match the operator and re-embed consistently |
| Index build memory | Large dataset | Build during a maintenance window and monitor memory |
| Cross-tenant leak | Missing filter | Include tenant/permission in the query and test |
| Dimension mismatch | Changed embedding model | Re-embed and migrate the column dimension |
Security and privacy
- Keep provider keys on the server and scope them to the minimum permissions.
- Treat retrieved documents and user input as untrusted; separate instructions from data.
- Redact sensitive fields before sending them to a third-party model.
- Log prompts and outputs only as long as you need them, with access controls.
- Provide a human escalation path for high-impact or low-confidence answers.
Common mistakes
- Mixing embeddings from two models in one index.
- Using cosine distance with unnormalised vectors without checking the model.
- Filtering after the vector search in application code.
- Assuming an index is a substitute for a relevance evaluation.
How to evaluate it
- Build a fixed set. Real questions, ambiguous cases, no-answer cases and adversarial prompts.
- Score the output. Correctness, citation quality, refusal behaviour, latency and cost.
- Review failures. Group them by retrieval, prompt, model or product gap.
- Version changes. Record the prompt, model and retrieval settings with each release.
Evaluation rubric
| Dimension | Pass condition | How to test |
|---|---|---|
| Correctness | Matches the source or is clearly marked as uncertain | Human review against a reference answer |
| Grounding | Every factual claim has a source | Citation coverage and spot checks |
| Refusal | Declines when the context is insufficient | No-answer and adversarial cases |
| Safety | Does not follow injected instructions | Prompt-injection test set |
| Latency | Within the product's interaction budget | p95 on a production-like request |
| Cost | Within the per-task budget | Tokens, retrieval calls and retries |
Rollout plan
- Internal only. Test with the team and a fixed evaluation set; log every failure.
- Small pilot. Expose it to a narrow user group with a visible feedback control.
- Human in the loop. Require review for high-impact answers or actions.
- Measure. Compare task completion, correction rate, latency and cost with the baseline.
- Expand or stop. Expand only if the rubric passes; keep a kill switch and a fallback.
Data governance
| Question | What to decide | Why |
|---|---|---|
| What data is sent? | Minimum fields, redaction and retention | Third-party processing and privacy |
| Where is it stored? | Logs, embeddings and evaluation sets | Access and deletion |
| Who can see it? | Roles, support and vendor access | Least privilege |
| How long is it kept? | Prompts, outputs and feedback | Retention policy |
| How is it deleted? | User, tenant and backup paths | Privacy requests |
| What is the fallback? | Search, form or human handoff | The model will fail sometimes |
Frequently asked questions
For many apps, yes. Measure at your expected row count and concurrency. A dedicated vector database may be better at very large scale or with complex filtering.
HNSW is usually a good default for low-latency search. IVFFlat can be efficient with a large, stable dataset but needs tuning.
Include tenant_id in the table and query, filter before ordering, and test with two tenants. Consider row-level security as an additional layer.
Create a new column or table, re-embed the corpus, evaluate, then switch reads and remove the old index. Do not mix dimensions in one column.
Sources and further reading
- pgvector (github.com)
- Supabase pgvector guide (supabase.com)
- LangChain.js documentation (js.langchain.com)
Related reading
Planning an AI feature?
Describe the user, the task and the data. I will tell you whether a prompt, RAG or a deterministic workflow is the right starting point.
How to cite this guide: Nur Hossain Repon, “Vector search with pgvector 2026 guide”, Nhrepon Portfolio, reviewed October 2026, https://nhrepon.com/posts/vector-search-with-pgvector-2026-guide.
Comments
No comments yet.