Vector Search on a Budget

The Problem#

Dedicated vector databases are useful, but many products can start with Postgres and pgvector. The goal is to prove retrieval quality before adding another system to operate.

Why It Matters#

Semantic search projects often fail because teams optimize infrastructure before they understand retrieval quality. Starting small lets you validate chunking, embedding model, metadata filters, and user feedback first.

Postgres with pgvector is commonly used for early RAG and semantic search because it keeps embeddings near relational metadata and permissions.

Project Example#

A documentation assistant with 100,000 chunks can store embeddings next to document metadata in Postgres. That keeps authorization filters, document versioning, and content updates in one place while the team validates real usage.

Implementation Example#

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunks (
  id bigserial PRIMARY KEY,
  document_id text NOT NULL,
  tenant_id text NOT NULL,
  content text NOT NULL,
  embedding vector(1536),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX doc_chunks_embedding_hnsw
ON doc_chunks USING hnsw (embedding vector_cosine_ops);

Query with both authorization metadata and vector similarity:

SELECT document_id, content
FROM doc_chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 8;

Implementation Checklist#

• Store chunk text, embedding, document id, tenant, and version together.
• Add an HNSW index once exact search becomes too slow.
• Use metadata filters before returning results.
• Evaluate recall with real user questions.
• Move to a dedicated vector store only when scale or filtering requires it.
• Keep a re-embedding pipeline ready for model changes.
• Combine BM25 or full-text search with vector retrieval for exact terms.
• Log retrieved chunk ids for debugging.

Production Notes#

Move to a dedicated vector database when index size, write throughput, multi-tenant filtering, replication, or operational isolation outgrow Postgres. Do it because measurements demand it, not because the feature contains vectors.

Common Mistakes#

• Choosing infrastructure before measuring retrieval quality.
• Skipping keyword search for exact terms.
• Ignoring re-embedding cost.
• Forgetting per-tenant authorization filters.
• Sending retrieved unauthorized chunks to the model and hoping the prompt prevents leaks.

Summary#

Budget vector search is about reducing operational risk. Start with the simplest store that meets recall, latency, and security requirements.