pgvector, Pinecone, & Chroma

Scenario

You have embedded your document chunks into vectors. Now you need to store them and search by semantic similarity. Before you read on, predict: can a normal SQL database like Postgres handle this, or do you need a completely new system?

Think about what it means to 'search by vector' — then read on to compare your options.

Mental model

A vector database is a storage engine optimized for one specific math operation: finding the closest arrays of numbers very fast.

To run semantic search, you need a database that supports ANN indexes and vector distance operators. You have three main options: add vector features to your existing Postgres database (pgvector), use a specialized managed cloud service (Pinecone, Qdrant, Weaviate), or use a lightweight in-memory library for local development (Chroma, FAISS).

Deep dive

Vector databases exist to solve one problem: storing large arrays of floats and running fast Approximate Nearest Neighbor (ANN) searches over millions of them. Without a vector database, you would have to calculate the distance to every stored vector on every query — which is too slow at scale.

Vector databases are infrastructure optimized for fast similarity search.

**pgvector (Postgres extension)**: If you already use Postgres, install the pgvector extension and your vectors live in the same rows as your other data. You can join, filter by user ID, and use SQL transactions alongside semantic search. This avoids keeping two databases in sync. It is the pragmatic default for most teams.

pgvector puts semantic search next to your existing relational data — no extra system to maintain.

**Pinecone, Weaviate, Qdrant**: These are specialized managed services. They scale to billions of vectors without you managing servers. You call them via REST APIs. They are a good fit for very large unstructured data or when you are building a decoupled microservice architecture. The trade-off is that you must keep metadata synchronized between your main database and the vector service.

Managed vector services scale massively but require syncing data out of your primary database.

**Chroma, FAISS**: Lightweight libraries that run in-memory or locally as Python packages. No server setup needed. They are ideal for prototyping, Jupyter notebooks, and agent short-term memory where running a full database is unnecessary overhead.

Chroma and FAISS are perfect for local development and lightweight prototypes.

**Switching vector databases does not improve answer quality.** The quality of your retrieval depends on your chunking strategy and embedding model — not on which database stores the vectors. A vector database just does fast math on numbers. Changing databases will not make the AI smarter or more accurate.

Retrieval quality lives in the embedding model and chunking. The database choice affects operations and scale, not intelligence.

Code examples

pgvector: vectors living next to relational data

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    user_id INT,
    content TEXT,
    embedding vector(1536)
);

-- Combined: exact metadata match AND semantic search
SELECT content
FROM documents
WHERE user_id = 42
ORDER BY embedding <=> :query_vec
LIMIT 5;

With pgvector, semantic search is just another SQL clause. The `WHERE` clause filters by user, the `ORDER BY` finds the closest vectors. No second database to sync.

Pinecone: specialized managed vector search

from pinecone import Pinecone
pc = Pinecone(api_key='...')
index = pc.Index('docs-index')

response = index.query(
    vector=query_vec,
    top_k=5,
    include_metadata=True,
    filter={'user_id': {'$eq': 42}}
)

Specialized vector databases use JSON-style filter objects. They scale to billions of vectors, but you must securely replicate metadata like `user_id` into their system at ingest time.

Common mistakes

Choosing a specialized vector database before you need it: Many teams adopt a managed vector service when they have only 100,000 documents. If they already use Postgres, pgvector would have saved weeks of data-sync work. Start with pgvector and migrate only when you hit real scaling limits.
Believing the vector database improves AI quality: Switching from Chroma to Pinecone will not make RAG answers more accurate. The database only does fast geometry — the 'intelligence' comes entirely from the embedding model and chunking strategy upstream.

Glossary

vector database: A storage engine built to hold lists of numbers (vectors) and quickly find the ones that are mathematically closest to a query vector.
pgvector: A plugin for Postgres that adds vector storage and fast similarity search directly to your existing relational database.
Pinecone: A managed cloud service that stores and searches vectors at large scale, so you do not have to run your own database servers.

Recall questions

Explain the main advantage of pgvector over a specialized vector database like Pinecone, in your own words.
What determines the quality (relevance) of retrieved results — the vector database you choose, or something else?
When would you choose Chroma or FAISS over pgvector or Pinecone?
What new operational problem does adding a specialized vector database like Pinecone create, if you already use Postgres?

Questions & answers

Your team is building a RAG feature for a multi-tenant SaaS app that already uses Postgres. A developer proposes adding a Pinecone cluster for the vector search. What trade-offs should you raise?

Adding Pinecone creates a data synchronization problem. You now must keep document metadata, permissions, and tenant boundaries synchronized between Postgres and Pinecone. pgvector inside your existing Postgres avoids this entirely and lets you apply `tenant_id` filters with standard SQL.

Approach: Highlight data synchronization complexity. Vector databases are infrastructure, not magic. The pragmatic choice is pgvector when Postgres is already the source of truth.

A user complains that the RAG bot returns irrelevant answers. Your CTO suggests migrating from pgvector to Qdrant to 'improve the AI's understanding.' Is this a valid solution?

No. Vector databases execute fast distance calculations on numbers — they do not understand text or meaning. Migrating databases will not improve relevance. To improve relevance, you must improve the embedding model, the chunking strategy, or add reranking.

Approach: Separate infrastructure from AI capability. The vector store is a geometry engine. Relevance improvements come from the layers above it — embedding quality, chunk quality, and reranking.

Continue learning

Previous: Similarity Metrics

Return to AI Engineering Roadmap