Vector Databases Compared: pgvector, Pinecone, Chroma, and When You Need None

Every AI application needs to store embeddings somewhere, and a whole industry sprang up to sell you a place to put them. Most teams do not need a new database. They need an index next to data they already govern.

This is the decision guide I use in reviews: start from the database you run, measure recall and latency on your filters, and only then pay for a dedicated store.

flowchart TD
  start[Need vector search?] --> where{Where is the source?}
  where -->|Postgres already| scale{Vectors and QPS}
  scale -->|under ~10M, modest QPS| pgv[pgvector]
  scale -->|huge QPS or hybrid| hosted[Pinecone / Qdrant / Weaviate]
  where -->|warehouse tables| wh[Warehouse-native vectors]
  where -->|laptop prototype| chroma[Chroma / LanceDB]

What a vector database actually does

Embeddings are arrays of floats (typically 768–3072 dimensions) where semantic similarity becomes geometric closeness. A vector store does two things: stores those arrays next to metadata, and answers “give me the k nearest vectors to this one” quickly.

Exact nearest neighbor over millions of high-dimension vectors is slow. Production stores use an approximate index, usually HNSW (graph) or IVF (inverted lists). You trade a little recall for a lot of latency.

That is it. It is an index type, not a new kind of truth. The documents, tickets, or warehouse rows remain the source. The RAG pipeline post is the ETL around this index.

The index is the product

HNSW builds a multi-layer graph. Inserts are more expensive than a B-tree. Query latency stays stable if the graph fits in memory. IVF partitions the space and searches a few lists — cheaper RAM, more tuning.

What you actually configure:

Knob	What it changes
Metric	Cosine vs L2 vs inner product — must match how the model was trained
ef_search / probes	Recall vs latency at query time
m / lists	Build time and memory at index time
Filters	Pre-filter (index-aware) vs post-filter (retrieve then drop)

Post-filter is the silent bug: you ask for k=5 and source = finance, the engine fetches 5 nearest overall, then drops four of them. You get one chunk. Pre-filter or over-fetch (k much larger than you need) if the store cannot apply the predicate inside the index.

Dimension is a contract. vector(1536) plus a 768-dimension embed is an error or a silent pad, depending on the engine. Store embed_model on the row and refuse mixed writes.

pgvector (Postgres extension)

The default for most teams that already run Postgres.

create extension vector;

create table doc_chunks (
    id bigserial primary key,
    doc_id text not null,
    content text not null,
    source text not null,
    acl text not null,
    embed_model text not null,
    embedding vector(1536) not null
);

create index doc_chunks_hnsw
    on doc_chunks
    using hnsw (embedding vector_cosine_ops)
    with (m = 16, ef_construction = 64);

create index doc_chunks_source on doc_chunks (source, acl);

-- k-NN, filtered, joinable — it is still SQL
select content, source, embedding <=> $query_embedding as distance
from doc_chunks
where source = 'finance-wiki'
  and acl = $user_acl
  and embed_model = 'text-embedding-3-small'
order by embedding <=> $query_embedding
limit 5;

Why this wins early:

• Backups, roles, migrations, and replicas are the ones you already run
• Filters and joins are SQL, not a second query language
• You can put the chunk table in the same transaction as a metadata write
• Comfortably fine into the low millions of vectors on a sized instance

Where it hurts:

• Very high QPS plus huge HNSW graphs means RAM and autovacuum become the project
• Hybrid search (BM25 + vector) is DIY: tsvector plus a score mix, or a dedicated engine
• You own compaction, bloat, and REINDEX after large deletes

If Postgres is already the system of record, start here. Moving off later is an export of doc_id, text, metadata, and vectors — not a rewrite of the product.

Chroma and LanceDB (embedded)

The DuckDB of vector stores: in-process, local disk, no cluster. Use them for prototypes, eval notebooks, and single-node tools.

They are the wrong place to put a multi-writer production index. Concurrent upserts, shared ACLs, and “the laptop went to sleep” are not ops stories you want on a customer path.

A useful pattern: develop the chunker and evals against Chroma, then load the same parquet of chunks into pgvector or a warehouse table. The local DuckDB workflow is the same idea — cheap iteration, then a real store.

Pinecone, Weaviate, Qdrant (dedicated)

These earn their keep when one of these is true:

• Hundreds of millions of vectors and a latency SLO
• Hybrid search (keyword + vector) as a first-class query
• Multi-tenant namespaces with filter-heavy retrieve
• You do not want to own HNSW RAM on Postgres

The trade-off is another system: another IAM story, another backup, another way to drift from the source of truth. Every dedicated store needs the same incremental + tombstone jobs as any other index. The vendor does not ingest your wiki for you.

If you adopt one, treat it like a serving replica:

1. Source of truth stays in object storage or the warehouse
2. The vector store is rebuildable from that snapshot
3. A checksum job compares doc_id counts and hashes on a schedule

Your warehouse already does this

Snowflake (VECTOR plus Cortex embed/search), BigQuery (VECTOR_SEARCH), and Databricks Vector Search on Delta all ship native support. If the documents already live as governed tables, embedding in place avoids a sync pipeline.

That option is underrated. The cost is warehouse/token billing and less control over HNSW knobs. The gain is row access policies on the same table the analyst already queries. See the Cortex AI guide for the SQL shape.

Do not copy the warehouse to Pinecone “because AI” if the only consumer is a Snowflake app. You just bought a consistency bug.

Hybrid search and metadata

Pure vector search misses SKUs, error codes, and ticket IDs. Hybrid means you retrieve with both a lexical score and a vector score, then merge.

A boring version that works on Postgres:

with vec as (
    select id, 1 - (embedding <=> $q_emb) as vscore
    from doc_chunks
    where acl = $acl
    order by embedding <=> $q_emb
    limit 50
),
kw as (
    select id, ts_rank(tsv, plainto_tsquery('english', $q)) as kscore
    from doc_chunks
    where acl = $acl
      and tsv @@ plainto_tsquery('english', $q)
    limit 50
)
select c.content, c.source,
       coalesce(vec.vscore, 0) * 0.6 + coalesce(kw.kscore, 0) * 0.4 as score
from doc_chunks c
left join vec using (id)
left join kw using (id)
where vec.id is not null or kw.id is not null
order by score desc
limit 5;

Tune the 0.6 / 0.4 on an eval set, not in a meeting. Dedicated stores package this. You still need the eval set.

Metadata filters are not optional. Source, date, tenant, and language belong on the row. If the store cannot pre-filter, you will over-fetch and still leak latency.

flowchart LR
  src[Documents / tickets] --> embed[Embed]
  embed --> store[Store + metadata + ACL]
  store --> retrieve[k-NN retrieve]
  retrieve --> rerank[Rerank or hybrid merge]
  rerank --> out[Answer]

Embed, store, retrieve, then rerank. The store is an index. The documents stay the source of truth.

Ops that decide whether the store survives

Whatever brand you pick, you already know these jobs:

• Freshness — incremental upserts from a content hash
• Deletes — tombstones when the source drops a doc
• Rebuild — new embedding model → new collection → flip pointer
• Backup — a store you cannot restore is a cache you pretend is a database
• Auth — the query path must apply ACL; the prompt must not be the ACL

A full rebuild after a model change is a backfill. Budget time and embed cost the same way you budget a warehouse migration.

-- Drift check: serving index vs source snapshot. Run on a schedule.
select
    'index' as side,
    count(*) as chunks,
    count(distinct doc_id) as docs
from doc_chunks
where embed_model = 'text-embedding-3-small'
union all
select
    'source',
    count(*),
    count(distinct doc_id)
from source_doc_chunks
where embed_model = 'text-embedding-3-small';

Evals: recall, latency, filters

Do not pick a vendor from a blog table. Run three numbers on a snapshot of your chunks:

Eval	How
Recall@10 vs exact	Brute-force a 5–10k sample; compare to HNSW
p95 latency	Same k, with and without the filters you will ship
Filter correctness	Gold questions that must not return another tenant

def recall_vs_exact(approx_ids: list[str], exact_ids: list[str]) -> float:
    if not exact_ids:
        return 0.0
    return len(set(approx_ids) & set(exact_ids)) / len(exact_ids)

If recall@10 vs exact drops under ~0.9 on your data, raise ef_search (or the equivalent) before you blame the embedding model. If p95 doubles when you add tenant_id, the store is post-filtering — fix that before you scale QPS.

Also measure rebuild time and embed cost for a model swap. That number matters more than a vendor’s “billions of vectors” slide.

Decision rule

Situation	Choice
Prototype or local eval	Chroma / LanceDB
You run Postgres, under ~10M vectors	pgvector
Data already in a warehouse, SQL consumers	Warehouse-native
Huge scale, hybrid search, dedicated AI serving	Pinecone / Qdrant / Weaviate
You only need keyword search	You need none of these

“None” is a real answer. If every query is an ID lookup or a LIKE on a SKU, a vector index adds cost and a second failure mode. Use embeddings when the question is semantic (“which SOP covers late accruals?”), not when it is where ticket_id = 4412.

Pitfalls

Buying a vector database to avoid data modeling. Metadata, ACLs, and chunk ids still need a schema. The store will not invent one.

One collection for every tenant without a filter. You will leak. Namespace per tenant or a mandatory acl predicate — pick one and test it.

Ignoring vacuum and RAM on pgvector. HNSW lives in memory. Size the box for the graph, not for the heap alone.

Treating vendor hybrid as magic. If your gold questions are error codes, lexical weight should win. Measure.

Sync drift. App writes Postgres, a job writes Pinecone, nobody compares counts. Checksums weekly.

FAQ

Do I need a dedicated vector database for RAG? You need an index. If Postgres is already in the stack and you are under ~10M vectors, that index is pgvector. A new brand is optional.

What happens if I change the embedding model? New collection, full re-embed, then flip the pointer. Mixed dimensions in one table are a silent outage. Store embed_model and refuse mixed writes.

Why did k=5 plus a tenant filter return one chunk? Post-filter. The engine fetched 5 nearest overall, then dropped four. Pre-filter or over-fetch.

Can I skip rerank? For a prototype, yes. For error codes and SKUs, hybrid plus a lexical weight usually beats raw k-NN. Measure on an eval set.

When is the answer “none”? When every query is an ID lookup or a LIKE on a SKU. Embeddings are for semantic questions, not where ticket_id = 4412.

Is Chroma production? For a laptop eval, yes. For multi-writer, ACL-shared, customer-path retrieve, no.

What this means for data engineers

The vector database is the easy part. Keeping the index fresh, filtered, and rebuildable is the same work you already do for search and serving tables. Start with pgvector or the warehouse. Add a dedicated store when latency, scale, or hybrid search force it — with evals in hand, not a logo.