Vector Databases — pgvector, Chroma, Pinecone and FAISS
Store and query embeddings at scale: indexes (HNSW, IVF), metadata filters, hybrid search, and how to choose a vector store.
Store Vectors Beside the Data Needed to Use Them
A vector database stores numeric embeddings and finds nearby vectors for a query. A useful retrieval record also carries a stable chunk ID, source text or a secure pointer, document version, title, access scope, and other metadata. Without that information, a nearest-neighbour result is just a row of numbers with no safe way to cite, filter, refresh, or delete its source.
A vector store is one part of a retrieval system, not a complete RAG solution. You still need ingestion, permissions, chunking, model versioning, query construction, evaluation, monitoring, and deletion workflows. For a small corpus, exact NumPy search may be enough; adopt a database when scale, filtering, operations, or shared access justify it.
| Option | Good fit | Trade-off to consider |
|---|---|---|
| PostgreSQL + pgvector | Existing PostgreSQL stack and relational metadata | Index and query tuning; extension availability on your service |
| Chroma | Local development and straightforward vector collections | Operational and scaling characteristics for your deployment |
| Qdrant | Purpose-built vector search with payload filtering | Another service and its backup, access and monitoring needs |
| Pinecone | Managed vector database workflows | Provider-specific operations, cost and data-location review |
| FAISS | Local or embedded nearest-neighbour search library | You provide persistence, metadata, API and access-control layers |
Create a pgvector Table with Metadata
pgvector adds vector types and similarity operators to PostgreSQL. It lets an application keep vectors, chunk text, and relational metadata together, and combine semantic search with SQL filters. Install or enable the extension in a database environment that supports it; the SQL below assumes the extension is already available.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_chunks (
id text PRIMARY KEY,
document_id text NOT NULL,
content text NOT NULL,
embedding vector(3) NOT NULL,
track text NOT NULL,
visibility text NOT NULL,
model_key text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX document_chunks_track_idx ON document_chunks (track);vector(3) is only a tiny illustrative dimension so the schema example is readable. Set the dimension to the value returned by the embedding model you configure, and keep it consistent within an index. Keep secrets and database connection strings outside source code. Apply ordinary row-level or application-level access controls to the text and metadata; vector similarity is not authorization.
model_key can identify the embedding configuration used for a row. It helps prevent accidental queries across incompatible vector spaces and supports controlled re-indexing.Use SQL for Similarity and Metadata Filters
pgvector provides distance operators. The cosine distance operator <=> orders closest rows first; one minus cosine distance can be presented as a similarity-like value when that matches the operator semantics. Confirm operator details for the installed extension version and use the same vector model and dimension for query and stored embeddings.
-- :query_embedding is bound by the database driver, not concatenated into SQL.
SELECT id, document_id, content,
1 - (embedding <=> :query_embedding) AS cosine_similarity
FROM document_chunks
WHERE track = :track
AND visibility = 'published'
AND model_key = :model_key
ORDER BY embedding <=> :query_embedding
LIMIT :top_k;Bind parameters using your database driver instead of interpolating user or model text into SQL. Restrict results with trusted filters before building model context. For multi-tenant data, include tenant and authorization constraints in every retrieval path, and test that a user cannot access another tenant's chunks.
INSERT INTO document_chunks
(id, document_id, content, embedding, track, visibility, model_key)
VALUES
(:id, :document_id, :content, :embedding, :track, :visibility, :model_key)
ON CONFLICT (id) DO UPDATE SET
content = EXCLUDED.content,
embedding = EXCLUDED.embedding,
track = EXCLUDED.track,
visibility = EXCLUDED.visibility,
model_key = EXCLUDED.model_key,
updated_at = now();Use transactions to keep text, vector, and metadata updates consistent. A document edit may affect many chunks; mark old chunks stale or replace them as one coordinated ingestion operation so users do not search a mixture of versions.
Approximate Nearest Neighbour: HNSW and IVFFlat
Exact search compares the query with every eligible vector. An approximate nearest-neighbour (ANN) index trades some recall for faster search as collections grow. HNSW organizes a navigable graph; IVFFlat groups vectors into lists and searches selected lists. The right choice depends on collection size, update patterns, memory, latency goals, filter selectivity, and the database version and configuration available to you.
-- Example index forms; check pgvector documentation for current options and tuning.
CREATE INDEX document_chunks_hnsw_idx
ON document_chunks USING hnsw (embedding vector_cosine_ops);
-- Alternative family: IVFFlat. Build and tune for your corpus and query pattern.
CREATE INDEX document_chunks_ivf_idx
ON document_chunks USING ivfflat (embedding vector_cosine_ops);These are alternative index examples; do not create both automatically without measuring. Some index families have build-time requirements or parameters that depend on the data. Benchmark with representative queries, filters, and concurrent load. Compare recall against exact search on a manageable sample and monitor index size and maintenance.
| Approach | Strength | Cost / concern |
|---|---|---|
| Exact scan | Strong baseline and exact ranking | Work grows with the number of vectors |
| HNSW | Graph-based search can offer strong recall/latency trade-offs | Memory and index-build/maintenance costs |
| IVFFlat | Partitioned search can be compact and configurable | Needs representative data and query-time tuning |
Metadata Is Part of Search Quality and Security
Filters can constrain results by track, language, publication state, tenant, document type, or date. Apply them using trusted application context, not a filter expression generated unchecked by a model. Highly selective filters can change index behaviour and result quality; test them with the actual distribution. If the database cannot apply a security predicate efficiently, choose an architecture that does not expose unauthorized text to the model.
SELECT id, content
FROM document_chunks
WHERE tenant_id = :authenticated_tenant_id
AND visibility = 'published'
AND track = :allowed_track
ORDER BY embedding <=> :query_embedding
LIMIT :top_k;authenticated_tenant_id must come from verified server-side identity. Test data isolation, deletion, and permission revocation in the retrieval layer, not only in the user interface. Keep document IDs stable so deletion requests can remove both source text and corresponding vectors from every index or cache.
Combine Semantic and Lexical Retrieval
Vector similarity is good at semantic paraphrase; lexical search is useful for exact identifiers, product names, error codes and uncommon terms. Hybrid retrieval combines both candidate sets, then merges or reranks them. A weighted score is one simple approach, but raw lexical and vector scores may have different scales. Reciprocal rank fusion combines rankings without requiring their scores to be directly comparable.
def reciprocal_rank_fusion(result_lists, constant: int = 60):
scores = {}
for results in result_lists:
for rank, item in enumerate(results, start=1):
key = item["id"]
scores[key] = scores.get(key, 0.0) + 1 / (constant + rank)
return sorted(scores.items(), key=lambda pair: pair[1], reverse=True)
# result_lists can contain a vector ranking and a lexical/BM25 ranking.This small function demonstrates ranking fusion, not a full search engine. Deduplicate by stable chunk ID, preserve source metadata, and evaluate retrieval using queries that include exact identifiers as well as paraphrases. A reranker can reorder a small combined candidate list, but adds latency and cost and still needs evaluation.
- Retrieve candidates using both vector similarity and lexical search.
- Apply permission and metadata filters before results are merged.
- Merge candidate rankings with a tested fusion or reranking strategy.
- Return a small, diverse set with source IDs and citation metadata.
- Evaluate recall, ranking quality, latency and cost on representative queries.
Know What Each Tool Owns
Chroma and Qdrant provide purpose-built vector storage and filtering capabilities; managed services such as Pinecone provide a hosted operational model; FAISS is a library for similarity search rather than a complete multi-user database service. pgvector is attractive when PostgreSQL is already part of the application and relational joins matter. Compare persistence, backups, tenant isolation, metadata filters, hybrid search, index controls, regional availability, and operational cost using your requirements.
Regardless of store, manage an ingestion lifecycle: idempotent upserts, model/schema versioning, retries, stale chunk cleanup, backup and restore, and a deletion path for source changes. Monitor query latency, empty-result rate, index errors, filter selectivity, and retrieval quality. Review provider documentation for current product features rather than relying on a feature list that may have changed.
from typing import Protocol
class VectorStore(Protocol):
def upsert(self, *, id: str, vector: list[float], metadata: dict) -> None: ...
def search(self, *, vector: list[float], limit: int, filters: dict) -> list[dict]: ...
def delete(self, *, ids: list[str]) -> None: ...
# Keep the rest of the application independent of a single vendor SDK.An application-owned interface makes it possible to test retrieval and swap a backend without rewriting every caller. Do not hide important differences behind a lowest-common-denominator API: expose capabilities and document behavior where filters, hybrid search, or index operations vary.
Common Vector-Database Mistakes
Choose a store before measuring the workload
Why: A benchmark may not reflect your filters, update pattern, scale or team operations.
Fix: Start with requirements and compare candidates using representative queries and deployment constraints.
Build an ANN index and assume it is exact
Why: Approximate search can omit relevant neighbours and index defaults vary.
Fix: Compare recall and latency against exact search and tune with an evaluation set.
Trust a client-provided tenant filter
Why: Users can alter requests and retrieve another tenant’s documents.
Fix: Derive tenant identity from authenticated server context and enforce it in every query.
Forget source updates and deletions
Why: Stale vectors can continue exposing content that changed or should be removed.
Fix: Track chunk-to-source relationships and make reindexing and deletion part of ingestion operations.
Build a Filtered pgvector Search Prototype
Practice
Compare exact and approximate retrieval with access controls
- Create a local test schema with a vector dimension taken from your chosen embedding model; do not use the three-dimensional illustrative schema as production configuration.
- Insert a small synthetic dataset with stable IDs, source IDs, model keys, tracks and tenant metadata.
- Write a parameterized cosine-distance query and verify that the tenant filter comes from authenticated server context.
- Compare exact results with an ANN index on a representative query set and record recall, latency and index-size trade-offs.
- Add a lexical candidate list and merge rankings with reciprocal rank fusion; include exact error-code and semantic-paraphrase queries.
- Test updating and deleting a source document so no stale chunk remains searchable.
Hint: Begin small and measurable. Index parameters and extension capabilities depend on the installed database version and data distribution.
🎯 Key Takeaways
- ✓A vector database stores embeddings with metadata needed for filtering, citations, updates and deletion.
- ✓pgvector combines vector search with PostgreSQL SQL; bind parameters and enforce tenant access in every query.
- ✓HNSW and IVFFlat are ANN index families that trade exactness, memory, build work and latency differently.
- ✓Chroma, Qdrant, managed vector services and FAISS have different operational responsibilities; compare against requirements.
- ✓Hybrid retrieval combines semantic and lexical strengths, with rank fusion or reranking evaluated on real queries.
- ✓Version embedding models, monitor retrieval quality, and make source updates and deletions part of the ingestion lifecycle.
Next
What comes next
Your retrieval pipeline can now return filtered candidates. Next, build evaluation sets and measure whether the whole LLM feature works reliably.
Lesson 08 · Evaluating LLM Apps — Eval Sets, LLM-as-Judge and RAG Metrics →HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
