pgvector vs Pinecone: choosing a vector store

The actual difference

Pinecone is a managed vector database. You send vectors and metadata, it handles indexing, sharding, and scaling. It is purpose-built, and at very large scale that specialisation shows.

pgvector is a Postgres extension adding a vector column type and index support. Your embeddings live in a normal table, next to the rest of your data, queryable with normal SQL.

That last point is the whole argument. It is not about speed; it is about whether your vectors are a separate system or part of your database.

Why co-location matters more than benchmarks

Real queries are rarely pure similarity search. You want the nearest chunks belonging to this customer, from sources that are active, excluding anything archived — and then you want to join the results to source records to build citations.

With pgvector that is one query with a WHERE clause and a join, executed transactionally against consistent data. With a separate vector database it becomes: query the index with a metadata filter, get IDs back, then query Postgres for the rest, and handle the case where the two disagree because a delete has not propagated yet.

That inconsistency window is a real source of production bugs. A customer deletes a document, the row goes, the vector lingers, and the assistant cites something that no longer exists.

select
  c.id,
  c.content,
  c.metadata,
  1 - (c.embedding <=> $query_embedding) as similarity
from chunks c
where c.workspace_id = $workspace_id
  and c.embedding is not null
order by c.embedding <=> $query_embedding
limit $k;

Tenant isolation is stronger in the database

For a multi-tenant product this is the strongest argument. In Postgres, the workspace filter is part of the query, and it can be backed by row-level security so the database refuses to return another tenant's rows even if application code forgets the filter.

With an external vector store, isolation is a metadata filter you must remember to apply on every call. It works. It is also one forgotten parameter away from serving one customer's documents to another — a failure with no good disclosure email.

AskBrew scopes retrieval inside a SQL function that takes the workspace ID as a required argument, so there is no code path that searches without one.

Index choice in pgvector

pgvector offers IVFFlat and HNSW. IVFFlat clusters vectors and searches the nearest clusters — smaller and faster to build, but it needs representative data before the clustering is meaningful, which makes it awkward for a system where tenants arrive continuously.

HNSW builds a navigable graph. Slower to build and larger on disk, better recall, and no dependency on having data present when the index is created. For a multi-tenant product where new workspaces appear constantly, that property decides it. We use HNSW with cosine distance.

create index chunks_embedding_idx on chunks
  using hnsw (embedding vector_cosine_ops);

When Pinecone is the right answer

We would not pretend the choice is universal. Pinecone earns its place in several situations.

• —Hundreds of millions of vectors, where a dedicated system's sharding is doing real work your Postgres instance is not built for
• —Vector search is the product, not a feature of an application that also has relational data
• —You are not already running Postgres, and adding it purely for this is the larger operational burden
• —Your team would rather not own index tuning, vacuum behaviour, or the memory profile of a large HNSW index