Vector Databases

PostgreSQL + pgvector

pgvector is an open-source Postgres extension that adds a native vector column type and similarity operators directly into SQL. Instead of standing up new infrastructure, embeddings live in the same table as the rest of your relational data.

What you can actually build

• A documents table with a vector(1536) column sitting next to normal columns like customer_id, status, created_at
• Single SQL queries that combine semantic similarity with relational filters — "most similar support tickets, but only open ones from this account"
• RAG pipelines that reuse existing Postgres infra, backups, replication, and row-level security

-- enable extension
CREATE EXTENSION vector;

CREATE TABLE documents (
  id serial PRIMARY KEY,
  content text,
  account_id int,
  status text,
  embedding vector(1536)
);

-- HNSW index for ANN search
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

-- query: nearest neighbors + relational filter
SELECT content
FROM documents
WHERE status = 'open' AND account_id = 42
ORDER BY embedding <=> '[0.012, -0.045, ...]'
LIMIT 5;

Pinecone

Pinecone is a fully managed, purpose-built vector database delivered as an API. You push vectors via SDK calls; Pinecone handles index construction (HNSW-based), sharding, replication, and scaling — there's no underlying database to administer.

What you can actually build

• Multi-tenant RAG systems using namespaces to isolate each customer's vectors
• Metadata filtering alongside similarity search (e.g. {"category": "invoice", "year": 2026})
• Hybrid dense + sparse search for better precision on exact terms (SKUs, names, codes)
• Systems that need to scale to hundreds of millions or billions of vectors with predictable low latency

import { Pinecone } from "@pinecone-database/pinecone";
const pc = new Pinecone({ apiKey: process.env.PINECONE_API_KEY });
const index = pc.index("support-tickets");

await index.namespace("acct-42").upsert([
  { id: "doc1", values: embedding, metadata: { status: "open" } }
]);

const results = await index.namespace("acct-42").query({
  vector: queryEmbedding,
  topK: 5,
  filter: { status: { "$eq": "open" } }
});

Decision framing

ANN Algorithms & Index Tuning

Approximate Nearest Neighbor (ANN) search is what makes vector databases fast. Instead of comparing a query vector against every stored vector (exact search, O(n)), ANN algorithms build index structures that navigate to approximate matches in O(log n) or sub-linear time, trading a small, tunable amount of recall for massive speed gains.

HNSW vs IVFFlat — the two you need to know

HNSW parameter tuning cheat-sheet

Distance metrics — which to choose

Index scaling math

-- pgvector: HNSW index with tuned parameters
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 24, ef_construction = 100);

-- Set query-time search width per session
SET hnsw.ef_search = 80;

-- IVFFlat alternative for lower memory budget
CREATE INDEX ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 200);

-- Tune probes at query time
SET ivfflat.probes = 10;

Choosing between HNSW and IVFFlat

Hybrid Search

Pure dense vector search excels at semantic similarity but misses exact-term matches — a user searching for "GPT-4o-mini" or a specific product SKU might get semantically adjacent results when they need an exact keyword hit. Hybrid search combines dense vector search with sparse keyword search (typically BM25) so you get both semantic understanding and lexical precision.

Dense vs Sparse vs Hybrid

Reciprocal Rank Fusion (RRF)

RRF is the most common way to merge dense and sparse result sets. Each document gets a score based on its rank in each list, then scores are summed. It's robust — it doesn't require calibrating score scales between the two systems, which is notoriously hard.

-- RRF formula: score(d) = sum over each list of 1 / (k + rank(d))
-- k=60 is the standard constant (reduces sensitivity to top-rank variance)

-- Example: merging dense rank 2 + sparse rank 5
-- RRF score = 1/(60+2) + 1/(60+5) = 0.0161 + 0.0154 = 0.0315

Re-ranking (cross-encoder)

After ANN retrieval gives you a candidate set (e.g. top-50), a cross-encoder re-ranker scores each candidate against the full query in context — much more accurate than bi-encoder similarity, but too slow to run on the full corpus. This two-stage pattern is the production standard for high-precision retrieval.

from sentence_transformers import CrossEncoder

reranker = CrossEncoder("cross-encoder/ms-marco-MiniLM-L-6-v2")

# candidates = [(doc_text, score), ...] from ANN retrieval
pairs = [(query, doc) for doc, score in candidates]
rerank_scores = reranker.predict(pairs)

# Sort by re-ranker score and take top-5
ranked = sorted(zip(candidates, rerank_scores),
                key=lambda x: x[1], reverse=True)[:5]

Hybrid search in Pinecone

// Pinecone supports native sparse-dense hybrid search
const results = await index.query({
  vector: denseEmbedding,          // dense component
  sparseVector: {                  // sparse BM25 component
    indices: [102, 983, 1204],
    values:  [0.72, 0.45, 0.31]
  },
  topK: 10,
  alpha: 0.7   // 0 = pure sparse, 1 = pure dense
});

Production Patterns

Getting vector search working in a notebook is straightforward. Getting it to stay accurate, fast, and compliant in a production system over months is where most teams underinvest. These are the patterns that matter once you're past the prototype stage.

1 · Embedding model versioning

-- pgvector: track model version in the same table
ALTER TABLE documents ADD COLUMN embedding_model TEXT;
ALTER TABLE documents ADD COLUMN embedding_version TEXT;

-- Always filter by version at query time
SELECT content FROM documents
WHERE embedding_model = 'text-embedding-3-small'
  AND embedding_version = 'v1'
ORDER BY embedding <=> $1 LIMIT 5;

2 · Multi-tenancy patterns

3 · Index staleness management

One of the most common silent failures: source data is updated or deleted but the vector index isn't. The model then retrieves stale or incorrect context with high confidence.

4 · HIPAA & compliance checklist

5 · Monitoring in production

6 · Cost optimization

Vector Database Comparison

pgvector and Pinecone are the two most common choices, but the broader landscape has several strong options. Understanding the key differentiators lets you make an informed recommendation — and defend it in an interview.

Full comparison matrix

Decision tree

The interview-ready summary

Production-Level Interview Q&A

Click a question to expand the answer.