Vector Databases Explained: When You Need One and How to Choose

Vector Databases Explained: When You Need One and How to Choose

By TCAL — Tech, Coding & AI Learning

Disclosure: This article may contain affiliate links. We only recommend products we believe in. See how we make money.

A vector database stores embeddings — high-dimensional arrays — and finds the nearest ones to a query. SQL answers “where status = shipped.” A vector index answers “which paragraphs mean something like this question?”

That is the whole product. Everything else (filters, hybrid search, namespaces) is packaging around nearest-neighbor search.

This page is the when-and-how-to-choose guide. The LLM call that uses the retrieved text is in the Claude API tutorial. If the retriever is a tool inside a loop, read building AI agents. For where to host the app that sits in front of the index, see web hosting for developers.

Desk note — who this is for / what it’s bad at: Teams who need semantic retrieve-then-prompt over their own text. Bad as a day-one Pinecone invoice, and a poor substitute for keyword search on order ids.

What to check before you subscribe (or migrate)

Listing identity: pgvector is an extension on the Postgres you already run. Chroma is a local or small-server prototype. Qdrant and Weaviate are dedicated ANN (or hybrid) servers you can self-host or rent. Pinecone is a vendor SLA, not a retrieval strategy. None of them embeds the text for you — that is a model choice — and none of them is the Messages call that uses the chunks.

Before you create a Pinecone project or dump Postgres:

  • Start in the database you already operate. If Postgres is in the stack, enable vector and an HNSW index. A new SaaS is not a RAG architecture.
  • Skip Pinecone until cardinality, multi-region, or “we will not own ANN” is the actual constraint. Vendor landing pages sell “AI memory.” You are buying an index and an invoice.
  • Skip a dedicated cluster for a few thousand chunks. numpy, a JSON file, or Chroma is enough to learn whether retrieval helps. The agent page is where search_docs becomes a tool, not a reason to migrate.
  • Do not buy a flagship card to embed a wiki. Embedding models are small. A 4090 search is unused VRAM if you only needed a small embedder or a hosted one. Local chat weights are the local LLM and Cursor tunnel problem.
  • Do not treat a host upgrade as a vector upgrade. Where the app runs is the hosting and CI question. pgvector needs an extension, not a new CDN. Rebuilds belong in the publish pipeline, the same way an AI review job belongs on the PR — not in a hope.

Practical cadence: embed a few hundred chunks in-process → measure whether answers cite the right paragraph → then pgvector if Postgres is already there. More articles live on the blog index. Amazon search links on this page use tcalnet-20; see how we make money.

How the pipeline actually runs

1. Chunk the source

Embedders have token limits. A 40-page PDF as one vector averages the whole document into mush. Split on headings or a sliding window (for example 500–800 tokens with a small overlap) and keep the raw chunk text next to the vector. You will send that text to the LLM later, not the numbers.

2. Embed

An embedding model maps text to a fixed-length vector. Nearby meanings land nearby in that space. Use one model for both indexing and queries. Mixing a 384-dimension local model with a 1536-dimension hosted model is a silent bug: the index will accept the insert and then retrieve nonsense.

You can call a hosted embedder or run one on your own box. Embedding models are far smaller than chat models; the local LLM setup guide covers the runtime, and the GPU guide is only relevant if you also want to generate with a large local chat model.

3. Index

Brute-force cosine over a few thousand vectors is fine in memory. Past that, stores use approximate indexes — HNSW and IVF show up in almost every vendor doc — so a query does not scan every row. Approximate means you can miss a neighbor. That is the trade you make for speed.

4. Query, then prompt

Embed the question with the same model. Take the top-k chunks. Send those chunks plus the question to the chat model. This is RAG (retrieval-augmented generation): the model is grounded in your text instead of inventing an API from training data. The hallucination guide is the sibling for what happens when you skip retrieval.

When you need one — and when you do not

Yes: RAG over your docs. Internal runbooks, a product handbook, a repo’s markdown. The LLM should quote your text, not invent a function.

Yes: “more like this.” Related tickets, similar images, near-duplicate support emails. That is similarity, not equality.

No: exact match. Order ids, emails, SKUs, and error codes belong in a B-tree or a keyword index. Elasticsearch / Postgres full-text search is the right tool.

No: a small corpus. Under a few thousand chunks, numpy cosine or a JSON file of embeddings is enough. A managed vector product adds an ops surface you do not need yet.

No: structured filters only. “All orders in March over $500” is SQL. Vectors do not replace WHERE.

The State of AI note is right that RAG is how most teams ground models in 2026. It is wrong to read that as “buy Pinecone on day one.”

Postgres first: pgvector

If you already have PostgreSQL, start with the pgvector extension. The extension name is vector. Official usage:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id          bigserial PRIMARY KEY,
  path        text NOT NULL,
  chunk       text NOT NULL,
  embedding   vector(1536) NOT NULL
);

-- Cosine distance; match this to how you embed.
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- Nearest chunks to a query embedding you computed in the app.
SELECT path, chunk, 1 - (embedding <=> $1) AS cosine_similarity
FROM docs
ORDER BY embedding <=> $1
LIMIT 8;

<=> is cosine distance. Similarity is 1 - distance, as the pgvector README states. HNSW is the usual default index; IVFFlat needs a train step (lists) and is fussier to operate.

This is enough for most developer RAG apps. You keep ACID transactions, the same backups as the rest of the product, and the option to add WHERE path LIKE 'docs/api/%' next to the vector order. Many Postgres-friendly hosts already let you enable extensions.

A Python sketch that stays honest about the boundary — your embedder, then SQL:

def retrieve(question: str, k: int = 8) -> list[str]:
    query_vec = embed(question)  # same model used at insert
    rows = db.execute(
        """
        SELECT chunk
        FROM docs
        ORDER BY embedding <=> %s
        LIMIT %s
        """,
        (query_vec, k),
    )
    return [row["chunk"] for row in rows]

Pass retrieve(question) into the user message. Do not concatenate the entire docs table.

How the common products actually differ

The table is positioning, not a price sheet. Vendor list prices move; the ops shape does not.

StoreYou operateWhat it is actually for
pgvectorYour PostgresVectors next to the relational data you already have
ChromaA local process or a small serverPrototypes, notebooks, “does RAG even help?”
QdrantContainer or their cloudFiltered ANN when you have outgrown a single Postgres index
WeaviateContainer or their cloudHybrid (vector + keyword) without gluing two systems
PineconeNothingA managed index when you want zero disk and a vendor SLA

Desk recommendation, in that order:

  1. Prototype in Chroma or even a pickle of vectors.
  2. If Postgres is already in the stack, move to pgvector.
  3. Buy a dedicated store when you need multi-region, huge cardinality, or a team that does not want to own ANN indexes.

Self-hosted Weaviate or Qdrant is a good fit next to self-hosted coding tools if the corpus cannot leave the network. It is a bad fit if nobody on the team has run a stateful service before.

Failure modes this desk sees in public write-ups

  • Wrong embedder at query time. Rebuild the index after a model change. There is no migration path that makes old 384-d rows comparable to new 1536-d rows.
  • Chunks too big. Retrieval returns “the whole chapter.” The LLM then ignores the middle.
  • Chunks too small. You retrieve a heading with no body. Overlap exists to prevent that.
  • No keyword fallback. Error codes and function names are exact tokens. Hybrid search (BM25 + vectors) exists because semantic search misses ECONNRESET.
  • Stale index. The wiki moved; the vectors did not. Treat re-embed as part of the publish pipeline, the same way CI treats a build.
  • Dumping retrieval into an agent without an allowlist. search_docs is a tool. It is not a license for the model to invent a delete_index tool. See agent tool design.

A short decision

Need semantic retrieve-then-prompt over your own text? You need embeddings and an ANN index. You do not automatically need a new SaaS. Start in Postgres. Graduate when the index — not the marketing page — tells you it is time.