PutThrough Product engineering studio
Book a free call

Building

Vector databases explained: how vector search works, pgvector vs Pinecone, and when you need one

What a vector database is, how vector search works, pgvector vs Pinecone with tested code and current prices, and the filtering mistake that silently drops results.

In short

A vector database stores embeddings, lists of numbers that represent what a piece of text means, and finds the ones nearest a query, so a search for “How do I get my money back?” finds the paragraph about refunds. Most use an approximate index (usually HNSW) to stay fast, trading a little recall for speed. If you already run Postgres, the pgvector extension is usually enough: vectors sit next to the rows they describe, filters are plain SQL and one transaction keeps them in sync. Pinecone is the managed alternative, with a namespace per customer, a free tier and paid plans from $20 a month. Whichever you use, watch for filters applied after the index, which silently return fewer results than you asked for.

Key takeaways

  • If you already run Postgres or Supabase, start with pgvector; reach for a dedicated vector database when the vector workload needs its own scaling or you have no Postgres.
  • Use one embedding model per column and store its name on every row; vectors from different models don’t compare, even at the same dimension.
  • Match the query operator to the index’s operator class (<=> with vector_cosine_ops), or Postgres silently scans the whole table.
  • Filters on an approximate index can return fewer rows than you asked for; turn on pgvector’s iterative scans (0.8.0+) or index the filter column.
  • Treat the tenant filter as a security boundary: enforce it with RLS in Postgres, or one namespace per tenant in Pinecone.
  • Add keyword search and merge with reciprocal rank fusion when exact terms such as order numbers and error codes matter.

What is a vector database?

A vector database stores embeddings, lists of numbers that represent the meaning of a piece of text, an image or a sound, and finds the ones closest to a query. Two texts that mean similar things get vectors that sit close together, even when they share no words. That is how a support agent can answer “How do I get my money back?” from a help-centre paragraph headed “Refunds”.

An embedding model does the conversion. OpenAI’s text-embedding-3-small, for example, turns any text up to 8,192 tokens into 1,536 numbers. The database’s job is the other half: keep millions of those vectors, and return the nearest few to a new one quickly, usually with filters such as “only this customer’s documents”.

What it’s used for, in the order we see it in practice:

  • Retrieval-augmented generation (RAG): an AI agent looks up the passages relevant to a question and answers from them. Every support agent and voice agent we build that answers from company documents works this way.
  • Semantic search: search that matches meaning, not just keywords.
  • Recommendations and deduplication: “more like this”, or “is this ticket a duplicate of one we already have?”

“Vector database” covers two kinds of product. Some are built for nothing else: Pinecone, Qdrant, Weaviate, Milvus and Chroma. Others add vector search to a database you may already run, such as pgvector for Postgres, or to storage, such as Amazon S3 Vectors.

How does vector search work?

Embeddings

Every vector in one column must come from the same embedding model. Vectors from different models live in different spaces, so comparing them returns confident nonsense with no error. Changing models means re-embedding everything. The common models:

Embedding models and what they cost
ModelDimensionsPrice per 1M tokens
OpenAI text-embedding-3-small1,536 (can be shortened)$0.02
OpenAI text-embedding-3-large3,072 (can be shortened)$0.13
Voyage voyage-4-lite / voyage-4 / voyage-4-large1,024 by default; 256, 512 or 2,048$0.02 / $0.06 / $0.12
Pinecone-hosted llama-text-embed-v21,024 by default; 384 to 2,048$0.16

Prices are from OpenAI’s pricing page, Voyage’s pricing and Pinecone’s pricing. Anthropic doesn’t offer its own embedding model and points to Voyage AI, so a Claude-based agent still needs one of these.

Distance

“Close” needs a measure. There are three in common use:

  • Cosine distance: the angle between two vectors, ignoring their length. The usual choice for text.
  • Inner product (dot product): the same ranking as cosine when vectors have length 1, and cheaper to compute. OpenAI and Voyage embeddings already have length 1, so for them, cosine and inner product give the same order.
  • Euclidean (L2) distance: the straight-line distance.

In pgvector each has an operator, and they come up again below:

pgvector’s distance operators
OperatorDistanceNote
<=>Cosine distance0 is identical; similarity is 1 - distance
<#>Inner product, negatedNegative because Postgres indexes only sort ascending
<->Euclidean (L2) distance
<+>L1 (taxicab) distance
<~>, <%>Hamming and Jaccard distanceFor binary vectors

Exact and approximate search

The simple way to find the nearest vectors is to compare the query with every row and sort. That is exact search, and it is correct by definition, but its cost grows with every row you add.

An approximate nearest neighbour (ANN) index skips most of that work, at the cost of sometimes missing a true neighbour. The share of true neighbours it finds is called recall. Two index types matter in practice:

  • HNSW (hierarchical navigable small world) builds a layered graph in which each vector links to its near neighbours. A search starts at the top layer and walks downhill towards the query. It comes from a 2016 paper by Malkov and Yashunin and is now the default almost everywhere.
  • IVFFlat clusters the vectors into lists and searches only the lists nearest the query.

pgvector’s README sums up the trade: HNSW “has better query performance than IVFFlat (in terms of speed-recall tradeoff), but has slower build times and uses more memory”. HNSW can also be created on an empty table, while IVFFlat should be created after the data is loaded, because it learns its clusters from the rows it sees. We use HNSW unless memory is tight.

Do you need a separate vector database?

Often not. If you already run Postgres, Supabase included, pgvector keeps the vectors in the same database as the rows they describe. That gets you a lot for free:

  • A chunk and its document live in one transaction, so deleting a document deletes its vectors too, with nothing left to drift out of sync.
  • Filters are ordinary SQL, joined to your real tables: tenant, permissions, dates, product line.
  • Row-level security, backups and point-in-time recovery cover your vectors the same way they cover everything else.
  • There is one bill and one system to watch.

A dedicated vector database earns its place when the vector workload is big enough to need its own hardware and scaling, when you don’t run Postgres at all, or when you want serverless pricing with nothing to tune.

What we’d use, by situation
SituationWhat we’d use
An app already on Postgres or Supabase, up to a few million chunkspgvector
A RAG agent over a company’s documentspgvector, or Pinecone if there’s no Postgres in the stack
Many customers, each with their own documents, searched heavilyPinecone with a namespace per customer, or pgvector with partitions
Hundreds of millions of vectors or more, searched constantlyA dedicated engine: Pinecone, Qdrant, Milvus or Weaviate
Huge archives searched rarely, where storage cost dominatesAmazon S3 Vectors

pgvector tutorial: vector search in Postgres

pgvector is an open-source Postgres extension. The current release is 0.8.7, from 1 October 2026. We ran every SQL statement in this article against Postgres 18.6 with pgvector 0.8.7, using PGlite, the embedded build of Postgres.

A table of chunks, each with its tenant, where it came from, its text and its vector:

create extension if not exists vector;

create table documents (
  id bigint generated always as identity primary key,
  tenant_id uuid not null,
  source text not null,          -- the file or page the chunk came from
  content text not null,         -- the text you'll give the model
  embedding vector(1536) not null,
  embedding_model text not null default 'text-embedding-3-small'
);

create index on documents (tenant_id);
create index on documents using hnsw (embedding vector_cosine_ops);

On Supabase, create the extension with create extension vector with schema extensions;. And since 30 October, a new Supabase table isn’t visible to the Data API until you grant it, so add the grants and an RLS policy as described in our guide to the 42501 error.

Keep the text and its source next to the vector. You need the text to give to the model, the source to cite and to delete, and the model name for the day you switch models.

The nearest five chunks for one tenant:

select source, content, embedding <=> $1 as distance
from documents
where tenant_id = $2
order by embedding <=> $1
limit 5;

The operator in the query must match the index’s operator class. The index above was built with vector_cosine_ops, so only <=> can use it. We checked: with the same index, a query ordered by <-> or <#> silently falls back to a sequential scan of the whole table. There is no error, just a query that gets slower as the table grows. Use vector_ip_ops with <#>, and vector_l2_ops with <->.

Loading and searching from TypeScript

The code below splits a document into chunks, embeds them with OpenAI and stores them, replacing any older version of the same document. We ran it with pg 8.23 and openai 6.49 against the same Postgres, with a local stand-in for the embeddings API.

import OpenAI from 'openai';
import pg from 'pg';

const openai = new OpenAI(); // reads OPENAI_API_KEY
const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const MODEL = 'text-embedding-3-small'; // 1,536 dimensions

/** Split a document on paragraph breaks into chunks of about `max` characters. */
export function chunk(text: string, max = 1000): string[] {
  const chunks: string[] = [];
  let current = '';
  for (const para of text.split(/\n\s*\n/)) {
    if (current && current.length + para.length > max) {
      chunks.push(current.trim());
      current = '';
    }
    current += para + '\n\n';
  }
  if (current.trim()) chunks.push(current.trim());
  return chunks;
}

async function embed(input: string[]): Promise<number[][]> {
  const res = await openai.embeddings.create({ model: MODEL, input });
  return res.data.map((d) => d.embedding);
}

/** Store a document's chunks, replacing any older version of it. */
export async function ingest(tenantId: string, source: string, text: string) {
  const chunks = chunk(text);
  const vectors = await embed(chunks);
  const client = await db.connect();
  try {
    await client.query('begin');
    await client.query('delete from documents where tenant_id = $1 and source = $2', [tenantId, source]);
    for (const [i, content] of chunks.entries()) {
      await client.query(
        `insert into documents (tenant_id, source, content, embedding, embedding_model)
         values ($1, $2, $3, $4, $5)`,
        [tenantId, source, content, JSON.stringify(vectors[i]), MODEL],
      );
    }
    await client.query('commit');
  } catch (e) {
    await client.query('rollback');
    throw e;
  } finally {
    client.release();
  }
}

/** The k chunks closest in meaning to the question, from one tenant's documents. */
export async function search(tenantId: string, question: string, k = 5) {
  const [vector] = await embed([question]);
  const { rows } = await db.query(
    `select source, content, embedding <=> $1 as distance
     from documents
     where tenant_id = $2
     order by embedding <=> $1
     limit $3`,
    [JSON.stringify(vector), tenantId, k],
  );
  return rows as { source: string; content: string; distance: number }[];
}

JSON.stringify of a number array is valid pgvector input ([0.1,0.2,…]), so no extra library is needed. The chunker is the simplest one that works. For documents with clear headings, split on the headings instead, so each chunk is one topic.

More than 2,000 dimensions

pgvector stores vectors of up to 16,000 dimensions, but an HNSW or IVFFlat index on the vector type takes at most 2,000. With text-embedding-3-large’s 3,072 dimensions, creating the index fails with column cannot have more than 2000 dimensions for hnsw index. There are two fixes, and both worked in our test:

  • Ask the model for fewer dimensions. OpenAI calls the dimensions parameter “the suggested approach” for shortening embeddings: dimensions: 1536.
  • Index a half-precision copy, which allows up to 4,000 dimensions, and use the same expression when you query:
create index on documents
  using hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

-- the query must use the same expression to use the index
select id from documents
order by embedding::halfvec(3072) <=> $1::halfvec(3072)
limit 5;

Why does vector search with a filter return fewer results?

This is the mistake we see most in vector search code, and it fails quietly. With an approximate index, Postgres finds candidate rows in the index first and applies your where clause afterwards. In the README’s words, “if a condition matches 10% of rows, with HNSW and the default `hnsw.ef_search` of 40, only 4 rows will match on average”. You ask for five chunks, get two, and the agent answers from less context than you think.

We reproduced it with 3,000 random 1,536-dimension vectors spread across 50 tenants, so each tenant owned 2% of the rows, and forced the planner to use the HNSW index:

Rows returned for “the 5 nearest chunks for one tenant”
SettingRows returned
Default (hnsw.ef_search = 40)2
hnsw.ef_search = 1002
hnsw.ef_search = 2003
hnsw.iterative_scan = strict_order5
hnsw.iterative_scan = relaxed_order, re-sorted5

Left to itself, the planner chose differently. With a B-tree index on tenant_id, it fetched that tenant’s 60 rows and sorted them by exact distance, which is the right answer when each tenant is small. The trap opens when a tenant grows large enough that Postgres prefers the vector index. The fixes, from simplest:

  1. Index the filter column. A B-tree on tenant_id lets Postgres search one tenant’s rows exactly when that’s cheaper.
  2. Turn on iterative index scans (pgvector 0.8.0 and later). The index keeps scanning until enough rows pass the filter, up to hnsw.max_scan_tuples (20,000 by default):
set hnsw.iterative_scan = strict_order;

-- or relaxed_order, which is faster but can return rows slightly out of
-- order; a materialized CTE puts them back in order
set hnsw.iterative_scan = relaxed_order;

with nearest as materialized (
  select source, content, embedding <=> $1 as distance
  from documents
  where tenant_id = $2
  order by distance
  limit 5
)
select * from nearest order by distance;
  1. Use a partial index for a filter with a few distinct values (create index … where category = 'faq'), and partition the table when there are many, as the pgvector README suggests.

Iterative scans are still approximate. In our run they returned five rows, but not exactly the five that an exact search found. For a RAG agent, that is usually fine. For anything where completeness matters, search exactly.

The tenant filter is a security boundary

In a product where several customers upload documents, where tenant_id = $2 is the only thing keeping one customer’s files out of another customer’s answers. Forget it in one query and the agent will happily quote a stranger’s contract. On Supabase, enforce it with row-level security as well as in code. Write your match function as a plain language sql function: those run as the calling user by default (we checked: prosecdef is false), so RLS still applies. Don’t mark it security definer, because that runs it as its owner and skips RLS.

What is Pinecone?

Pinecone is a managed, serverless vector database. You create an index, send vectors, and query them over an API, with no servers or index settings to manage. It suits teams without Postgres in their stack, or teams that want vector search entirely off their main database.

Two things make it a good fit for products with many customers:

  • Namespaces. Pinecone’s guidance is to “use one namespace per tenant”. A query is scoped to one namespace, so one customer’s search can’t see another’s vectors, and it costs less, as the pricing below shows.
  • Metadata filters with $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $exists, $and, $or and $not, on up to 40 KB of metadata per record.

The same job as the pgvector example, with the TypeScript SDK. We type-checked it against @pinecone-database/pinecone 9.1.0:

import { Pinecone } from '@pinecone-database/pinecone';

// tenantId, source, chunks and vectors as in the pgvector example above;
// questionVector is the embedded question.
const pc = new Pinecone(); // reads PINECONE_API_KEY

// Once: a serverless index for 1,536-dimension vectors compared by cosine.
const indexModel = await pc.indexes.create({
  name: 'docs',
  schema: { fields: { _values: { type: 'dense_vector', dimension: 1536, metric: 'cosine' } } },
  deployment: { deploymentType: 'managed', cloud: 'aws', region: 'us-east-1' },
  waitUntilReady: true,
});

// One namespace per tenant: a query here can only see this tenant's vectors.
const index = pc.index<{ source: string; content: string }>({
  host: indexModel.host,
  namespace: tenantId,
});

await index.upsert({
  records: chunks.map((content, i) => ({
    id: `${source}#${i}`,
    values: vectors[i],
    metadata: { source, content },
  })),
});

const { matches } = await index.query({
  vector: questionVector,
  topK: 5,
  includeMetadata: true,
  filter: { source: { $eq: 'refund-policy.md' } },
});

Two version notes, because most tutorials online are out of date. SDK 9 needs Node.js 22 or later. It also deprecates the flat `pc.createIndex` in favour of pc.indexes.create, shown above. Pinecone can also embed text for you with a hosted model; the older way to do that, createIndexForModel with upsertRecords and searchRecords, is now labelled legacy.

The limits worth knowing, from Pinecone’s operation limits: up to 20,000 dimensions, upserts of up to 1,000 records or 2 MB per request, and topK up to 10,000.

How much does Pinecone cost?

From Pinecone’s pricing page, as of 3 November 2026:

Pinecone plans
PlanPriceWhat’s included
StarterFreeUp to 2 GB storage, 2M write units and 1M read units a month, 5 indexes, AWS us-east-1 only
Builder$20 a month, flat10 GB storage, 5M write units and 2M read units a month, all regions; usage over that is blocked, not billed
Standard$50 a month minimum usagePay as you go: storage $0.33 per GB-month, write units $4–$4.50 per million, read units $16–$18 per million
Enterprise$500 a month minimum usageHigher limits; read and write units cost more per million

How queries are metered is the part that shapes your design. A query costs 1 read unit for every 1 GB of the namespace it searches, with a minimum of 0.25. The number of results doesn’t change the cost, but the size of the namespace does. That is the cost argument for a namespace per tenant: a query in a customer’s own 1 GB namespace costs 1 read unit, and the same query filtered inside a shared 100 GB namespace costs 100.

A rough example. 100,000 chunks of 1,536 dimensions is about 0.6 GB of vectors (1,536 × 4 bytes each), plus the text you store with them; call it 0.7 GB. On Standard, storing that costs about $0.25 a month, and each query uses about 0.7 read units, so 100,000 queries a month comes to roughly $1.10–$1.30. Both are far below the $50 minimum. At that scale you pay for the plan, not for usage, and Builder at $20 would likely cover it.

pgvector vs Pinecone, and the other vector databases

pgvector vs Pinecone
pgvectorPinecone
What it isA Postgres extensionA managed, serverless vector database
Where vectors liveIn your Postgres tables, next to your dataIn Pinecone, separate from your data
FilteringAny SQL, joined to your tablesMetadata filters on each record
Multi-tenancyA tenant_id column with RLS, or partitionsOne namespace per tenant
Keeping data in syncOne transaction covers the row and its vectorYour code writes to two systems
TuningYou choose the index and settings (ef_search, iterative scans)Managed for you
CostPart of your database billFree tier, then $20 flat or a $50 monthly minimum
LicenceOpen source (PostgreSQL licence)Proprietary service

Searches for an open source vector database usually end at one of these:

Open-source vector databases
DatabaseLicenceNotes
pgvectorPostgreSQL licenceVector search inside the Postgres you already run, Supabase included
QdrantApache 2.0Written in Rust; self-hosted, or managed as Qdrant Cloud
MilvusApache 2.0Cloud-native and built to scale out; managed as Zilliz Cloud
WeaviateBSD-3-Clause, with some parts under the Weaviate LicenseCan embed your text at import; supports hybrid search
ChromaApache 2.0Simple to start with; popular for prototypes

On AWS, besides pgvector in Postgres, there is Amazon S3 Vectors, generally available since December 2025, stores and queries vectors directly in S3: up to two billion vectors per index, with vectors of up to 4,096 dimensions. It is built for large, cheaply stored collections rather than for the fastest queries.

How a vector database fits into RAG

The vector database is one step in a retrieval-augmented generation pipeline:

  1. Chunk each document into passages of one topic each.
  2. Embed each chunk and store it with its text, source and tenant.
  3. When a question arrives, embed the question with the same model.
  4. Retrieve the nearest chunks, with hybrid search if exact terms matter.
  5. Generate: give the model the chunks and the question, and tell it to answer only from the chunks and to say which source it used.

Most of the quality comes from steps 1 and 4, not from the choice of database. Before launching an agent, we write down 30 to 50 real questions with the document that should answer each. Then we check that the right chunk comes back in the top five. If it doesn’t, the fix is usually better chunking or hybrid search, not a different vector database. The AI agents guide covers the steps around retrieval.

Mistakes we see in vector search code

  • Mixing embedding models. The documents were embedded with one model and the question with another, or a model upgrade re-embedded half the table. Shortening embeddings makes this worse: two models set to 1,536 dimensions produce vectors of the same size, and they still don’t compare. Store embedding_model on every row.
  • An operator that doesn’t match the index. A query with <-> against a vector_cosine_ops index scans the whole table, and nothing tells you.
  • Filtering after the index. The query returns fewer rows than asked for. Use iterative scans, or an index on the filter column.
  • No tenant filter, or a match function marked security definer that skips RLS. Each turns a search bug into a data leak.
  • Building IVFFlat on an empty table. Its clusters come from the rows present when it’s built. Create it after loading data, or use HNSW.
  • Storing vectors without their text and source. The agent can’t quote what it found, and you can’t delete one document’s chunks when the document changes.

Sources

  1. OpenAI docs — Vector embeddings
  2. OpenAI API pricing
  3. Voyage AI pricing
  4. Pinecone pricing
  5. Claude docs — Embeddings
  6. Malkov and Yashunin — Efficient and robust approximate nearest neighbor search using HNSW graphs (arXiv:1603.09320)
  7. pgvector on GitHub
  8. pgvector changelog
  9. Cormack, Clarke and Büttcher — Reciprocal Rank Fusion outperforms Condorcet and individual rank learning methods (SIGIR 2009)
  10. Supabase docs — Hybrid search
  11. Pinecone
  12. Pinecone docs — Implement multitenancy
  13. Pinecone docs — Indexing overview
  14. Pinecone docs — Node.js SDK
  15. Pinecone docs — Operation limits
  16. Pinecone docs — Understanding cost
  17. Qdrant on GitHub
  18. Milvus on GitHub
  19. Weaviate on GitHub
  20. Chroma on GitHub
  21. AWS — Amazon S3 Vectors now generally available
  22. Amazon S3 docs — S3 Vectors limitations
  23. Supabase docs — Vector indexes
  24. Pinecone docs — Filter by metadata
  25. Pinecone TypeScript SDK on GitHub
  26. Voyage AI docs — Text embeddings

Written by

PutThrough

A product engineering studio that builds AI agents, automations, web and mobile apps, and audits apps built with AI coding tools.

Questions

Questions this raises

Something we haven’t covered?

Ask on a call
01 What is a vector database?

A vector database stores embeddings, which are lists of numbers that represent the meaning of text, images or audio, and finds the ones closest to a query vector. It is what lets an AI agent find the passages relevant to a question even when they use different words. Examples include Pinecone, Qdrant, Weaviate, Milvus and Chroma, and Postgres with the pgvector extension.

02 Is pgvector a vector database?

pgvector is a Postgres extension that adds a vector type, distance operators and HNSW and IVFFlat indexes, so it turns Postgres into a vector database. Vectors live in ordinary tables next to your other data, which means filters are plain SQL and one transaction covers a row and its vector.

03 pgvector vs Pinecone: which should I use?

If you already run Postgres or Supabase, start with pgvector: it costs nothing extra, keeps vectors in sync with your data and lets row-level security protect them. Choose Pinecone if you don’t run Postgres, want vector search entirely managed and off your main database, or have a large multi-tenant workload that suits one namespace per customer.

04 Is Pinecone free?

Pinecone has a free Starter plan with up to 2 GB of storage, 2 million write units and 1 million read units a month, and up to 5 indexes, in AWS us-east-1 only. Paid plans are Builder at $20 a month flat, Standard with a $50 monthly minimum and pay-as-you-go usage, and Enterprise with a $500 minimum (as of November 2026).

05 What is the best open source vector database?

For most applications that already use Postgres, pgvector, because it adds vector search to the database you already run. For a separate engine, Qdrant and Milvus (both Apache 2.0) are built for large collections, Weaviate adds built-in embedding and hybrid search, and Chroma is the simplest to start with.

06 Why does my pgvector query return fewer rows than the limit?

With an approximate index, pgvector finds candidates in the index first and applies your WHERE clause afterwards, so a selective filter can leave fewer rows than you asked for. Since pgvector 0.8.0 you can set hnsw.iterative_scan to strict_order or relaxed_order so the index keeps scanning until enough rows pass the filter. A B-tree index on the filter column also helps.

07 Do I need a vector database for RAG?

You need somewhere to store embeddings and search them by similarity, but it doesn’t have to be a separate product. For most RAG agents over a company’s documents, Postgres with pgvector is enough. Retrieval quality depends more on chunking, using one embedding model consistently and adding keyword search than on which vector database you pick.

Have something to build?

A free 30-minute call. We’ll tell you honestly what fits in a week, and send a fixed quote afterward.

Prefer to write? Send your requirements

What do you need?

What it should do, who will use it, the must-haves, and any links. A few sentences is plenty.

Budget, if you have one in mind
When would you like to start?