Choosing the agent data layer: pgvector vs Chroma vs Pinecone

Start in Postgres

The case for this is not that pgvector wins a benchmark. It is that the alternative introduces a distributed system where you previously had one database, and that cost is paid continuously while the benefit only appears at scale you may never reach.

pgvector is an open-source Postgres extension providing exact and approximate nearest-neighbour search with ACID compliance and point-in-time recovery. It handles the vector shapes real embedding models produce:

And the distance operators cover what any retrieval design needs: <-> for L2, <=> for cosine, <#> for negative inner product, <+> for L1, plus <~> and <%> for Hamming and Jaccard on binary vectors. If you are cosine-similarity shopping for a database, this row is the entire requirement and Postgres already meets it.

The agent access pattern is not search

This is the argument most vector-database comparisons miss, because they are written for search. Consider the shapes:

When a single retrieval carries a network round trip plus connection setup plus serialisation, and you do that thirty times in a loop, the fixed overhead is the cost — not the index. A local Postgres query on the same connection pool as the rest of your data avoids all of it. That is a real advantage of co-location and it is specific to agents.

The corollary: if your agent is doing thirty retrievals per run, the first optimisation is not a faster database. It is asking why thirty retrievals are necessary, which is usually a context-layer problem.

When Postgres stops being the answer

On memory: HNSW is the fast index and it wants to be resident. The practical threshold is not a row count someone can quote you — it is the point where your index plus your working set exceeds the RAM you are willing to provision. Two mitigations come before migration: halfvec halves the storage at usually-imperceptible recall cost, and binary quantisation reduces index size substantially at higher scale.

On the fourth reason: it is legitimate and under-stated. “We do not want to own this” is a perfectly good basis for buying a managed service. It is just a different reason from the one people usually give, and being honest about which one applies leads to a better decision.

HNSW and IVFFlat

That last constraint on IVFFlat catches people in exactly one way: they create the index in a migration that runs before the data loads, get a useless index built on nothing, and never find out because the query still returns results — just poor ones.

-- HNSW: the production default. No training step, safe on an empty table.
-- Defaults are m = 16, ef_construction = 64.
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- IVFFlat: faster to build, cheaper in memory, weaker at query time.
-- MUST be created after the data is loaded — it trains on existing rows.
-- lists = rows/1000 up to 1M rows; sqrt(rows) above that.
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);   -- ≈ 1M rows

The caption is its own small trap. The index operator class has to match the distance operator your query uses. A vector_cosine_ops index will not be used by a query written with <->, and Postgres will silently fall back to a sequential scan — fast enough in development, catastrophic in production. Check with EXPLAIN that your index is actually being hit.

Filtering is where recall quietly dies

This matters far more for agents than for search, because agent queries are almost always scoped: this customer, this account, this document set, this date range. A scoped query is a filtered query, and filtered approximate search is the hard case in every vector engine.

In Postgres you have a lever the dedicated stores mostly do not: ordinary relational partitioning and B-tree indexes on the filter column, working alongside the vector index. For strongly-scoped access — one customer at a time, which describes most agent retrieval — a partitioned table makes the filtered case cheap rather than pathological.

Whatever you choose, test filtered recall explicitly at a realistic selectivity. A recall number measured on unfiltered queries tells you nothing about the query your agent will actually run.

Your recall problem is chunking

This is the most commonly misdiagnosed problem in the category. The symptom — “it retrieves irrelevant passages” — looks like a search problem, so teams evaluate search engines. The cause is usually upstream.

The fourth is subtle and worth dwelling on. A question and the passage that answers it are not linguistically similar, so embedding both in the same space and hoping for proximity underperforms. Storing a generated question alongside each chunk, or embedding a summary rather than the raw text, often produces a bigger improvement than any index change — and costs one pass over your corpus rather than a migration.

The three options, honestly

Chroma is the most pleasant of the three to start with, and the embedded mode is genuinely useful: no server, no network, works in a test. It bundles the things you would otherwise assemble — embedding functions for OpenAI, Cohere, Hugging Face and sentence-transformers, metadata filtering, keyword and regex search without embeddings, dense/sparse/hybrid querying, and multi-modal indexing across images, audio and text. If you need multi-modal retrieval, that is a real capability gap Postgres does not fill.

Pinecone positions itself as the vector database for AI agents and applications — semantic search, knowledge retrieval and long-term memory at scale, searching billions of items in milliseconds. Its operational specifics change often enough that quoting them here would be a disservice; if it is on your shortlist, the questions to take to its current documentation are filtered-search behaviour, per-query latency at your scale, and what happens to your data if you leave.

The cost nobody counts

This is the tax that never appears in a comparison table, and it is paid every day rather than at migration time. Four failure modes turn up reliably:

The last one is a security issue, not a data-quality issue, and it is the strongest single argument for co-location. When the vector lives in the same row as the record, your existing row-level access control covers it for free. When it lives in another system, you have to reimplement authorisation there — and a retrieval layer that returns content the requesting user could not otherwise read is a confused-deputy problem wearing a different hat. That connects directly to the agent threat model.

None of which makes a dedicated store wrong. It makes it a decision with a recurring cost that should be justified by a measured constraint. The order that works: start co-located, instrument retrieval quality and latency, fix your chunking, and migrate when a number tells you to — not when a landing page does.